EXPERT RESPONSE
INSERT, APPEND, REPLACE, TRUNCATE in the control file refers to instruction to the database to:
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:
NAME VARCHAR2(100)
ADDRESS VARCHAR2(200)
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
BEGIN
:NEW.ADDRESS := REPLACE(:NEW.ADDRESS,'-', ',');
END;
/
The control file (test.ctl) listing will be as follows:
LOAD DATA
INFILE *
INTO TABLE TEST
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(NAME, ADDRESS)
BEGINDATA
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:
sqlldr USERID=/{@db_name} control=test.ctl log=test.log bad=test.bad
where @db_name is optional
The dashes will be replaced with comma because of the compiled before user trigger above.
|
