RE: multitable inserts and sequences

Since you don't have a PK/FK relationship in real life, this doesn't really apply to your case. While hesitant to take this slightly off topic with my response, it may be worth mentioning a particular gotcha I've encountered, as have others, if people reading this thread get the idea of using this (your demonstrated) approach for tables that *do* a PK/FK between them.

In short, the order in which the tables are inserted is not determinant. Take a look at Note: ID 265826.1, referencing bug 2891576. So, if someone is considering this approach, the workaround is deferred constraints (our choice when facing this), or, temporarily disabling the constraint. The issue *wasn't* the sequence, it was the order in which the inserts were processed. So, just a head's up to those considering it.

Regards,

Larry G. Elkins
elkinsl_at_verizon.net
Cell: 214.695.8605

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
> Sent: Thursday, June 27, 2013 2:04 PM
> To: thomas.kellerer_at_mgm-tp.com; oracle-l_at_freelists.org
> Subject: RE: multitable inserts and sequences
>
>
>
>
> 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-l

--
http://www.freelists.org/webpage/oracle-l