RE: multitable inserts and sequences
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "thomas.kellerer@xxxxxxxxxx" <thomas.kellerer@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 27 Jun 2013 19:04:21 +0000
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Thomas Kellerer [thomas.kellerer@xxxxxxxxxx]
Sent: 26 June 2013 10:37
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l--
//www.freelists.org/webpage/oracle-l
Other related posts:
