Home » RDBMS Server » Server Utilities » return a column value using Sqlloader after loading (Oracle 10g - 10.2.0.5.0 )
return a column value using Sqlloader after loading [message #533945] Thu, 01 December 2011 16:32 Go to next message
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 #533948 is a reply to message #533945] Thu, 01 December 2011 17:23 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
there is no need for the trigger.
just INSERT trades_seq.NEXTVAL directly
You can obtain the T_ID value by utilizing RETURNING clause as shown below

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#sthref6482
Re: return a column value using Sqlloader after loading [message #534092 is a reply to message #533948] Fri, 02 December 2011 10:15 Go to previous messageGo to next message
shijumic
Messages: 31
Registered: May 2010
Member
I am not sure how I can use RETURNING clause in Sqlloader. I have to use Sqlloader or some other bulk loading method like FOR ALL for this inserts.Can I use RETURNING clause in a Sqlloader control file? Would you explain it with an example?
Re: return a column value using Sqlloader after loading [message #534094 is a reply to message #534092] Fri, 02 December 2011 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
>I am not sure how I can use RETURNING clause in Sqlloader
You can't use RETURNING by SQLLDR.
You could utilize EXTERNAL TABLE then write INSERT with RETURNING.
I can not think of another way to obtain T_ID values.
Re: return a column value using Sqlloader after loading [message #534110 is a reply to message #534094] Fri, 02 December 2011 11:56 Go to previous message
Barbara Boehmer
Messages: 8919
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.

Previous Topic: Question on export/import
Next Topic: SQL*Loader (sqlldr) Timezone Import "UTC"
Goto Forum:
  


Current Time: Tue Jan 28 07:24:18 CST 2020