sql loader and foreign key

[weirdcpu]

Hello,

I try to load the content of a file into two tables with sqlloader.

The first table has an indentifier which is obtains with an SEQUENCE,
The second table has a field which contains the ident of the first table has a foreign key

Everything is ok except the management of the foreign key for the second table : sqlloader begins by loading the first table and load after the second table.
So the value of the foreign key in the second table is load with the last value of the ident of the first table.

What i seek is an execution of sqlloader line by line of the data file : First line of data into each table, then second line of data... This way i could obtain the current value of the sequence into the foreign_key.

How can i do this ?

Details :

First my file 'data.dat' :

Item1 data1
Item2 data2
Item3 data3....

which is load with theses instructions for sqlloader :

LOAD DATA 'data.dat'
INTO TABLE myfirsttable
FIELDS TERMINATED BY WHITESPACE
{
FIRSTTABLE_ID 'MY_SEQUENCE.nextval'
...other instructions to load data in the first table
}
INTO TABLE mysecondtable
{
FIRSTTABLE_ID_FK 'MY_SEQUENCE.currval'
...other instructions to load data in the second table

 

[Thargy]

weirdcpu,

you don't.

A couple of solutions spring to mind.

Since all your data starts in one file, you already know the connection between data. Therefore, temporarily disable the sequence on table 1, and hard code the primary/foreign key in the sql loader files. Once data is in the tables, reinstate the referential integrity.

Alternatively, load the data in to table one, and using sql plus insert into table 2 using a select statement to get the pk from the first. This does have a downside, in that you absolutely have to load tables in the correct sequence to satisfy the RI, otherwise the system will (correctly) reject data.

Is that enough guidance, or would you like an example?

Regards

T

Grinding away at things Oracular

 

[weirdcpu]

thanks for your answer,

But i have found how to do this : i used the option rows=1 wich make a commit after each line of the data file. So sqlloader load one line of the data file into each table and the commit keeps current the value of sequence.

By the way i can't disable the sequence on table1 as the database is used by a software with the data already here.

thank for your help.