Re: multitable inserts and sequences
Date: Thu, 27 Jun 2013 08:22:20 -0700 (PDT)
Message-ID: <1372346540.54621.YahooMailNeo_at_web121603.mail.ne1.yahoo.com>
It's a single transaction, and once you call nextval for a sequence you can call currval all day and get the same value that nextval just set:
SQL> create sequence foo_seq;
Sequence created.
SQL> create table foo (id integer primary key, some_data varchar(10));
Table created.
SQL> create table bar (f_id integer not null references foo (id), other_data varchar(10));
Table created.
SQL>
SQL> insert all
2 into foo (id, some_data) values (foo_seq.nextval, data_one)
3 into bar (f_id, other_data) values (foo_seq.currval, data_two)
4 with data as (
5 select '1-one' as data_one, '1-two' as data_two from dual
6 union all
7 select '2-one', '2-two' from dual
8 union all
9 select '3-one', '3-two' from dual
10 )
11 select data_one, data_two
12 from data;
6 rows created.
SQL>
SQL> select * from foo;
ID SOME_DATA
---------- ----------2 2-one
1 1-one
3 3-one
SQL>
SQL> select * from bar;
F_ID OTHER_DATA
---------- ----------2 2-two
1 1-two
3 3-two
SQL>
SQL> declare
2 v_seqnextval number;
3 v_seqcurrval number;
4 begin
5 for i in 1..10 loop
6 select foo_seq.nextval into v_seqnextval from dual;
7 for j in 1..5 loop
8 select foo_seq.currval into v_seqcurrval from dual;
9 dbms_output.put_line(v_seqnextval||', '||v_seqcurrval);
10 end loop;
11 end loop;
12
13 end;
14 /
4, 4 4, 4 4, 4 4, 4 4, 4 5, 5 5, 5 5, 5 5, 5 5, 5 6, 6 6, 6 6, 6 6, 6 6, 6 7, 7 7, 7 7, 7 7, 7 7, 7 8, 8 8, 8 8, 8 8, 8 8, 8 9, 9 9, 9 9, 9 9, 9 9, 9 10, 10 10, 10 10, 10 10, 10 10, 10 11, 11 11, 11 11, 11 11, 11 11, 11 12, 12 12, 12 12, 12 12, 12 12, 12 13, 13 13, 13 13, 13
13, 13
13, 13
PL/SQL procedure successfully completed.
SQL>
I don't believe that this is an anomaly, I believe it's intended functionality for sequences; it's an old post but it's still a good one to read: http://dfitzjarrell.wordpress.com/2008/06/06/out-of-sequence/
David Fitzjarrell
From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Wednesday, June 26, 2013 3:37 AM
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 - 17:22:20 CEST