How retrieve ids inserted into a table with a sequence?

[ju]

Hi,
I must insert few rows into an oracle table. But in this table, there is a trigger which generate the id with a sequence:
" IF :new.c_thrd_in_no IS NULL THEN
SELECT THRD_c_thrd_in_no.nextval
INTO :new.c_thrd_in_no
FROM dual;
END IF;"

And i would like to retrieve the new ids which correspond to the rows inserted and put these ids into a dynamic relashionship file.


I succeed to retrieve into a file the new id to insert, but i don't know how to merge this file with my other file which contain the others data to insert. (the 2 files don't have the same number of columns).

if anybody have an idea...

thanks

[DSguru2B]

There must be a key column in your file that contains the generated keys and the other file. Do a lookup on the keys. Or thats not the case? If not, then how do you want to attach the keys generated by one table with the other file?

[ray.wurlod]

Have an extra column in the target table that identifies the run (or the run date) when the row was inserted. Use this to identify the rows inserted by a particular run.

[ju]

Use the date is not possible because the date is generated too. And if i selected the rows inserted in the table, i loose the id correspond in mys old file.

1)
Exemple:
I have a file:
"IDFILE","NAME",...
"TRY1","HENRY",...
"TRY2","ZIDANE",...

and i must insert these rows in a table where ids and date of creation are generated.

In the table, data inserted wille be for exemple:
IDTABLE,NAME,...,CREATIONDATE
22650,"HENRY",...,30/05/07 10:05:15
22651,"ZIDANE",...,30/05/07 10:05:15

And i would like to have a new file with the old and the new Ids.
"IDFILE","IDTABLE"
"TRY1","22650"
"TRY2","22651"


2)
Or another solution would be to insert directly in my first file the idTABLE that will be generated thanks this request:
SELECT THRD_c_thrd_in_no.nextval FROM dual; -->22650
The file will look like to
"IDFILE","NAME",...,"IDTABLE"
"TRY1","HENRY",...,22650
"TRY2","ZIDANE",...,22651

and inserted these data into the table with the IDTABLE. (Its possible, because in the trigger, we test if the IDTABLE is empty or not)

But how know how much select i must to do, and how merge the result of the request with the first file?



So, the first solution is perhaps less difficult...

[chulett]

Go with #2. Don't use the trigger. Pull the sequence value in your job so you know what it is before you use it. Then you can write the new ID to your file as well.