RE: multitable inserts and sequences
Date: Thu, 27 Jun 2013 19:04:21 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901408FE6_at_exmbx06.thus.corp>
I think the problem with this is that until it's documented to be working as expected you can't guarantee that it will work in the future however many times you test it now.
Imagine Oracle introducing an array-based optimisation (like the MERGE one) which says: create an array of rows to be inserted into table 1 create an array of rows to be inserted into table 2
Insert into table 1 - deriving the nextvals on the array inserts Insert into table 2 - deriving the currvals on the array insert ... giving you the last value from the first array as the only value inserted on the second array.
Clearly anyone dealing with the code for multi-table insert SHOULDN'T overlook the possible use of sequences - but stranger oversights have happened in the past.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Thomas Kellerer [thomas.kellerer_at_mgm-tp.com] Sent: 26 June 2013 10:37
To: oracle-l_at_freelists.org
Subject: multitable inserts and sequences
Hello,
we have a situation where we are using NEXTVAL and CURRVAL in the same multi-table insert statement.
Basically this is something like this:
create sequence foo_seq;
create table foo (id integer primary key, some_data varchar(10));
create table bar (f_id integer not null references foo (id), other_data varchar(10));
insert all
into foo (id, some_data) values (foo_seq.nextval, data_one)
into bar (f_id, other_data) values (foo_seq.currval, data_two)
with data as (
select '1-one' as data_one, '1-two' as data_two from dual
union all
select '2-one', '2-two' from dual
union all
select '3-one', '3-two' from dual
)
select data_one, data_two
from data;
In reality the CTE is a bit more complicated, but the basic structure is the same. BAR is a temporary table which is used in later steps, and I only added the foreign key for this test in order to see any "problem" right away. In reality there is no FK between the temp table and the "foo" table.
Running the above statement, everything is inserted correctly.
As far as I can tell, the above situation is not listed under the section "Restrictions on Sequence Values" in the manual.
But I wonder if this usage of NEXTVAL and CURRVAL is guaranteed to work, or is this working by coincidence?
Regards
Thomas
-- http://www.freelists.org/webpage/oracle-l-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 27 2013 - 21:04:21 CEST