| SQL Loader [message #73216] |
Wed, 03 March 2004 07:20  |
alka
Messages: 10
Registered: January 2002
|
Junior Member |
|
|
I have a csv file.. Which contains data. I need to upload it to oracle through SQL Loader.. Now my question is...
There is field in CSV like module1.. There is two coulmns corresponding to this field in oracle table like module1_1 and module1_2
This field module1 is 3 varchar2
Now i have to grab first two digit of this field. If it is odd like 01,03,05,07,09 then it should go to module1_1
but if it is even like 02,04,06,08 then it should go to coulmn module1_2
Please let me know I do it....
Thanks
Alka
|
|
|
|
| Re: SQL Loader [message #73237 is a reply to message #73216] |
Sat, 06 March 2004 07:39  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You may have to load your data into a separate staging table, then use a before insert row trigger on that staging table to checking whether mod(to_number(substr(your_column,1,2)),2)=0 or not and insert into the proper column in your table, for example:
-- SQL*Loader control file:
LOAD DATA
INFILE 'test.dat'
REPLACE
INTO TABLE staging_table
FIELDS TERMINATED BY WHITESPACE
TRAILING NULLCOLS
(temp_module)
-- before insert row trigger on staging table:
CREATE OR REPLACE TRIGGER staging_table_trigger
BEFORE INSERT ON staging_table
FOR EACH ROW
BEGIN
IF MOD (TO_NUMBER (SUBSTR (:NEW.temp_module, 1, 2)), 2) = 0
THEN
INSERT INTO oracle_table (module2) VALUES (:NEW.temp_module);
ELSE
INSERT INTO oracle_table (module1) VALUES (:NEW.temp_module);
END IF;
NULL;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
