Re: how to get value of auto-increment PK of row just inserted?

Otis Gospodnetic wrote at 09-Feb-00 17:13:29 on the subject Re: how to get value of auto-increment PK of row just inserted?:

[...]

>I'm still struggling with this issue a litte, though :(
>I need to make 2 INSERTs using COMMIT, like this:

>-- this row gets a PK from sequence
>INSERT INTO user_auth (username,password) VALUES ('u', 'p');

>-- this row has a FK that references the above PK
>INSERT INTO user_info (user_id,name) VALUES (????,'Name Here');

>COMMIT;
Hello Otis,
there are some approaches to that:

1. You could so a "select seq_test.nextval from dual;" to get the new PK and then do the two inserts.
2. With Oracle8i you have the option to use something like

   insert into tab_test values (seq_test.nextval,...) return pk into    var_test;

   insert into tab_test2 values(var_test,...);

   That means you can get an insert to return some value into a variable.    Please check the manual for the exact syntax since I don't have it at    hand right now.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |