How to Insert to Multiple Tables in Oracle SQL at runtime? The first table sets the primary key.?

Question

I am inserting to Table 1 (which sets the primary key - generated by a sequence) and also to Table 2 which references the primary key in Table 1 as a foreign key.

Is there a way to reference the Table 1 primary key as a foreign key in the Table 2 insertion? Thanks.

Answer 1

try using two inserts as part of single transaction block:

SET TRANSACTION NAME 'DoubleInsert';

insert into table1(pk,...) values (mySequence.nextVal,...);

insert into table2(fk,....) values(mySequence.curVal,...);

COMMIT;

or without transactions, fetch first sequence into temporary variable and then execute two inserts using the stored value.