| return a column value using Sqlloader after loading [message #533945] |
Thu, 01 December 2011 16:32  |
shijumic
Messages: 31
Registered: May 2010
|
Member |
|
|
I have the following table intra_trades with t_id as the primary key. There is a trigger on that table that gets the next sequence and inserts it into the t_id column for every insert. I need to load data into that table using SqlLoader as chunks of 3000 rows and return the t_id back the script that Sqlload the data so that it can use that t_id's for the next process in the script.
intra_trades
t_id NUMBER(15) pk
t_name VARCHAR2(30)
t_loc VARCHAR2(40)
t_start TIMESTSTAMP
t_end TIMESTSTAMP
CREATE OR REPLACE TRIGGER intra_trades_bir
BEFORE INSERT
ON intra_trades
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT trades_seq.NEXTVAL INTO :NEW.t_id FROM DUAL;
END;
/
The problem is that the only unique key on that table is the t_id which has a sequence on it and it is the pk. There can be duplicate rows in that table to meet the business needs for the company. So it is hard to associate the rest of the data in a row with t_id. The only thing I can think of is return the t_ids in the order it inserted so if the script keeps the order of rows in the memory it can associate the tid with the rest of the intra_trades info.
How can I make the sqlloader return an array of t_ids that inserted? I need to return the t_ids's in the order it inserted so that the script can associate the t_id with the rest of the rest of the data in a row.
Thanks,
Shiju
|
|
|
|
|
|
|
|
|
|
| Re: return a column value using Sqlloader after loading [message #534110 is a reply to message #534094] |
Fri, 02 December 2011 11:56  |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SQL*Loader does not return anything but exit codes.
To use the returning clause, you can either use an external table or use SQL*Loader to load into a staging table, then insert from either the external table or staging table into the target table (intra_trades) and use the returning clause to obtain the primary key (t_id) that was just inserted.
It might help if you describe what the next process is that you need these t_id's for, as there may be other options. For example, if it is loading other columns in the row into another table, you may be able to use curval if you process one row at a time. SQL*Loader also has its own sequences that have a little more flexibility.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
