SQL & PL/SQL :: Varchar2 Data Change Mmyyyy To Yyyymm
Jan 8, 2013
CREATE TABLE CHECK(
ADM_DATE VARCHAR2(10)
)
INSERT ALL
INTO CHECK VALUES ('122012')
INTO CHECK VALUES ('112012')
INTO CHECK VALUES ('102012')
INTO CHECK VALUES ('092012')
INTO CHECK VALUES ('082012')
SELECT * FROM DUAL;
ADM_DATE has the data as in format 'MMYYYY' but I've to make it as 'YYYYMM' while the datatype of ADM_DATE is VARCHAR2.
I have to change the datatype of a column from CLOB to varchar2, without changing the order of the columns. The table has no data. I could find any other way other than dropping the CLOB columns and then adding new columns with varchar2 datatype. But this changes the order of the columns in the table.
Insert into test values ('2/03/2011') Insert into test values ('02/03/2011') Insert into test values ('12/33/2011') Insert into test values ('xxx') Insert into test values ('33/33/2011') Insert into test values ('03/03/11')
i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column
like '%abc%' like 'abc%' like '&abc'
Will be the corresponding index will be for those cases?
title varchar2(100) y publisher varchar2(20) y categoryname varchar2(20) y rating varchar2(2) y
my query is, select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3
returns result ,
1 1 MY LEDGER KOCH PRESS 2 2 TO KILL A MOCKINGBIRD HARPERCOLLINS 3 3 THE MISMEASURE OF MAN W.W. NORTON
But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,
1 5 WONDERFUL LIFE W.W.NORTON 2 5 THE MISMEASURE OF MAN W.W. NORTON 3 5 TO KILL A MOCKINGBIRD HARPERCOLLINS 4 5 MY LEDGER KOCH PRESS 5 4 TRUMAN SIMON SCHUSTER 6 4 GOSPEL PICADOR 7 4 HARRY POTTER AND THE GOBLET OF FIRE SCHOLASTIC 8 4 INNUMERACY VINTAGE BOOKS 9 4 JOHN ADAMS SIMON SCHUSTER 10 4 JOURNALS OF LEWIS AND CLARK MARINER 11 4 LETTERS AND PAPERS FROM PRISON SCRIBNER 12 4 PREACHING TO HEAD AND HEART ABINGDON PRESS 13 4 THE SHIPPING NEWS SIMON SCHUSTER 14 4 THE GOOD BOOK BARD 15 4 THE DISCOVERERS RANDOM HOUSE 16 3 THE COST OF DISCIPLESHIP TOUCHSTONE 17 3 SHOELESS JOE MARINER 18 3 KIERKEGAARD ANTHOLOGY PRINCETON UNIV PR 19 3 EMMA WHO SAVED MY LIFE ST MARTIN'S PRESS 20 3 EITHER/OR PENGUIN 21 3 CHARLOTTE'S WEB HARPERTROPHY 22 3 BOX SOCIALS MARINER 23 3 ANNE OF GREEN GABLES GRAMMERCY 24 3 WEST WITH THE NIGHT NORTH POINT PRESS 25 3 UNDER THE EYE OF THE CLOCK ARCADE PUB 26 3 TRUMPET OF THE SWAN HARPERCOLLINS 27 2 COMPLETE POEMS OF JOHN KEATS VIKING 28 1 POLAR EXPRESS HOUGHTON MIFFLIN 29 1 GOOD DOG, CARL LITTLE SIMON 30 1 MIDNIGHT MAGIC SCHOLASTIC 31 1 RUNAWAY BUNNY HARPERFESTIVAL
why final queries top 3 rows r different than inner query ?
Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1); INSERT INTO test('ABC'); INSERT INTO test(SYSDATE); INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY')); INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')); INSERT INTO test('15/01/2012'); ... commit;
Our organization has recently decided to go for storage metro cluster solution for disaster recovery. In a Data guard environment, we normally calculate how much archive log is generating and based on that value we calculate the required bandwidth.
For storage metro cluster, we need to find how much block is changing in our primary database, and the same rate of change would apply on DR cluster. Now, i need to give the assumption how much changing is happening in my system. How to calculate the change.
would there be any code change for spatial data after migration from 10g to 11g? In case of upgradation from 10R1 to 10R2, there were lot of issues regarding the spatial data code.
know beforehand so that it should not affect the service.I have already done the migration from 10g to 11g by 2 ways succesfully.
I'm getting some problem in my project. I've taken my form fields in tabular way. I also have the ID with auto-increment system there. I want that when user moves towards the 2nd row for inserting new record then the 1st record would be committed.
I'm getting my bank-id from database and making +1 increment and made that bank-id text box into display box.
When running my form then it is increasing one like this :
database record BKID-00001
and with one increment it is showing this in my form display field of 1st row in tabular form.
BKID-00002
but when I moves towards the 2nd row it doesn't commits the form and gives the BANK-ID AS "BKID-00002" again like the above row.
I have big source tables to load in a data warehouse. We are in a full Oracle environment. So I need to extract only delta since the last extract.
I need to capture even deleted rows from the source table.
I have tested the following solution:
- declare a materialized view log on the source table - load the content on this view log in my ODS - empty this view log - load my DWH with the captured delta
It is very simple and seems to work perfectly. I am just confused by the fact that nobody seems to have implemented such a solution.
I'm cloning a database from an existing dbca template. The clone is failing with datafile does not exist, because during the copying step, all the datafiles' name are changed, how do I force it to force dbca to keep the same names from the original database?
I am going to change some parameters on primary database (db cache, java pool etc.). I am not sure, if the changes are automaticallly propagated to standby or if I have to make the changes manually on stanby site too? I couldn't find clear answer in documentation. how parameter changes work in dg physical configuration?
oracle 11.2.0.3 I have insert only tables that receive srecords from multiple processes at a rate of about 200/second. Each transaction can have up to 100 records. I have another set of processes that queries this table for the latest data. These processes run anywhere from once a minute to once an hour. Processes do not get all of the data. They get data based on a type field.
Both of these are from java middle tiers. The process that queries data (The subscriber) does so at the request of many remote servers (there will be vast numbers). I am not allowed to expose these downstream databases to the internet (they are not oracle DBs anyway) so I cannot use streams or golden gate
So basicallyInsert Process: multiple sessions that combined insert records up to 200/second. There will be between 1-100 records per commit.
Query Process: Downtream process makes a request to my middle tier. This middle tier runs a query to get the latest data and passes it back. This design is set and I cannot change it.
1. right now we capture the insert time of the record. However, at this rate of inserts some processes will commit faster than others. So I cant use a 'greater than my insert time' query. 2. streams/golden gate won't work. can't register these DBs. 3. don't want to serialize my inserts because since I am not sure I can keep up with the insert rate. I don't even know what the specs will be for the production hardware. I have to actually deliver this before its decided. So I am being conservative. 4. I really want to avoid updates on this table if possible. In part due to my limited ability to test. 5. due to the number of downstream processes it is possible that it will request data and for some reason fail to insert the data locally. So the downstream application will keep track of the latest data it received. This means that a subscriber may need to request the same data again.
Is there a way to set up change data capture with multiple subscribers to handle this? if my subscribers are just queries? All the queries come from the same servers(there will be several, but all the same thing). If so, when I performance test this are there any wait issues I should keep an eye on?
how to change non - SYS oracle users' password in data guard envirnment. We all know that for SYS password change in data guard. DBA has to change in primary database by either "alter user SYS identified by xxxx" or create password file with orapwd.
Then scp password file to standby database. However, if I want to change SYSTEM or DBSNMP passwords, I change on primary with " alter user ....." SQL, then new passwords will be login dictionary. But this new SYSTEM pqssword will be shipped with redo log to standby and SYSTEM password on standby will be updated? I need technical answer on this question
I have the following chunk of code, which could return no rows, one row or many rows. When it returns one or many rows the DBMS_OUTPUT.PUT_LINE prints out my values. When no data is found 'Hello' is not printed?
Suggest a way I can change the code to make the exception print when no data is found?
SET SERVEROUTPUT ON; BEGIN FOR prec IN ( select * from xxx.part_maint where drop_partition = 'Y' ) LOOP DBMS_OUTPUT.PUT_LINE (prec.SCHEMA_NAME || ' ' || prec.OBJECT_NAME); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('hello'); END;
The prod stats has been implemented in development. The stats has been gathered 2 months back on dev while in production the stats has been gathered 2 weeks back.
My question shouldn't the high volume of data causes changes in plan in both the environment? My thinking is that plan can be different as the high volume of data are changing in prod it may lead to a different plan.
I'm trying to change the attributes on a display where the info is gathered via a routine that fills the data block with info. I've searched and tried using 'set item instance property' in several places within the data block using different triggers but still doesn't work.
No errors are encountered or shown. Attributes aren't changed based on a small test condition.
I have a requirement to change the instance parameter of the primary database and standby in the data guard. Any procedure to do the same. I have to do it directly on production and there is no test set up so I cannot experiment . The procedure I am thinking of is as below.
1. Stop the standby
2. backup the primary
3. Change the parameter and restart to make it effective
4. Start the primary
5. Go to stand by
6. Change the parameter and mount the database.
7. Check if both are in sync and apply logs if not.
I am using oracle 10g with data guard configured , I have primary ( A ) and standby database ( B ) .But because of some unavoidable conditions the primary database ( A ) got shutdown and was not starting , We shifted the standby database ( B ) at new location and changed it to primary with following command ,
startup mount; alter database recover managed standby database finish; alter database commit to switchover to physical primary; shutdown; startup;
This new primary ( B ) was open for end users for 2 days during which old primary ( A ) was shutdown .
I took the backup of ( B ) and restored it on A AND shutdown the B . Now A is acting as Primary database. Server B is shutdown . I want to change server B to standby database with A running as Primary .Is it possible ?
ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [89], [83], [], [], [], [], []
I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me