|Populating Master-Detail Foreign Key Values Using Sequences - This article presents some safe methods for populating master-detail foreign key columns when using sequences.|
A Woods said...How about using order_lines_seq.currval to populate order_id in order_lines?
A Woods said...Sorry, meant to say, how about using "orders_seq.currval" to populate order_id in order_lines?
sure you can..but the catch is when you do it in 2 different statements (one insert going against orders and another one going against order_lines table) there is no guarantee that oracle would retain the same currval. (think Multi-user env)
hence you have to go by Tim's way of pl/sql
fetch the value once into temp variable
reference the variable in your 2 statements
or you can implement your idea within a single SQL statement like..
Wrote file afiedt.buf
1 Insert all
2 into orders (id,description)
3 values (orders_seq.nextval,lv_ord_desc)
4 into order_lines(id,order_id,description)
5 values (order_lines_seq.nextval,orders_seq.currval,lv_ord_line_desc)
7 'Dummy Order Desc' lv_ord_desc,
8* 'Dummy Order line Description' lv_ord_line_desc from dual
2 rows created.
Jason H said...No, currval is session-specific. It is perfectly acceptable and "safe" in a multi-user environment to use seq.nextval for the master record and seq.currval for child/detail records. "Your" currval is not affected by another session's currval.
I overlooked this simple solution myself and didn't come back to the "obvious" currval until one of my developers complained that he couldn't use RETURNING with OCCI.
rash said...the autonumber using trigger and sequence was exactly what i wanted...thanks Tim!
dude said...Dude, use currval and remove this article. It only propogates confusion since you didn't read the docs on currval.
Interesting. So what happens when triggers are being used to populate the ID value and the developer is not aware of the trigger value? The the RETURNING clause is pretty useful.
Remember, some of use have to support people who come from other environments and are not necessarily fluent in Oracle. The point of the article is to show what methods are available, and in the case of the first example, what not to do.