When I use the SQL loader to load this data to a table, I need a CTL file. How can I decode this date to change the column values with hyphens to (addr1-addr2-addr3) to comma (addr1,addr2,addr3)?Name Address selva addr1-addr2-addr3 joy asd1-asd2-asd3
1. Insert rows in an already empty table; this instruction will fail if the table is not empty.
2. Insert rows in a non-empty table.
3. Replace or truncate rows in a table and insert data in the data file or in the control file. Data can be part of the control file preceded by the instruction BEGINDATA.
In order for you to replace the hyphens with comma in the rows in the table, you need to create a before insert trigger on the table as follows.
Say the table is called TEST with the following description:
The trigger text to achieve your objective is as follows:
CREATE OR REPLACE TRIGGER REPLACE_DASHES_WITH_COLUMN
BEFORE INSERT ON TEST
FOR EACH ROW
:NEW.ADDRESS := REPLACE(:NEW.ADDRESS,'-', ',');
The control file (test.ctl) listing will be as follows:
INTO TABLE TEST
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
Sonali Bendre, c/o Azim Fahmi-Somewhere outthere-Bollywood
Preity Zinta, "c/o Azim Fahmi-In your dreams-None of your business"
"George Bush", 1600 Pennsylvania Avenue-Whitehouse-Washington D.C.
Tony Blair, 10 Downing Street-London-UK-POSTALCODE HERE
Telaram Thakur, 10 Shabzi Mandi-Jackson Heights-New York
From command line if you type the following command:
where @db_name is optional
The dashes will be replaced with comma because of the compiled before user trigger above.
This was first published in January 2005