RE: multitable inserts and sequences

Sounds like you gave some thought to how the currval and nextval would be evaluated with respect to the order in which the inserts may be processed. But this was still bugging me, seems like something I was still forgetting (this was 5-7 years ago, and I was helping someone with this, I'm a little fuzzy).

Take a look at http://asktom.oracle.com/pls/asktom/f?p=100:11:8091295109158::::P11_QUESTION_ID:6915127515933, and the comment "because the nextval is evaluated (as documented) once per row from the rowsource".

You can reference NEXTVAL on each of the two inserts, and they will both return the same value. I did a test, referencing nextval on both inserts and ended up with the exact same set of values in both tables. I did it again with an insert into the parent, and *two* inserts for the child table. And I got the "same" rows twice in the child, matching the parent. By the same token, added another column to the child table, defined it as the primary key, and used a second sequence, and referenced it for this new PK column, and using newseq.NEXTVAL on each of the two inserts into the child, immediately threw a unique constraint violation.

I think, considering I favor natural keys over surrogate keys (not intended to start that whole discussion <grin>) I may have nudged the person toward not using sequences at all for this anyway. I've tried to find my notes on this, no luck. But something else to consider before you drop into PL/SQL for doing this.

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 Thomas
> Kellerer
> Sent: Friday, June 28, 2013 12:55 AM
> To: oracle-l_at_freelists.org
> Subject: Re: multitable inserts and sequences
>
> Hello all,
>
> Larry Elkins, 28.06.2013 03:58:
>
> > 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.
>
> Thanks for the answer. We will then keep on using PL/SQL and a loop that guarantees the sync between
> the sequence usage (even though there is no FK we do require the values to be "in sync".
>
>
> Jonathan Lewis, 27.06.2013 21:04:
> > 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.
>
> Good point ;)
>
> Regards
> Thomas
>
> --
> http://www.freelists.org/webpage/oracle-l

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