Re: User Sequence Numbers

Karsten Farrell wrote:

> insert into child_table (parent_name,child_nr,...)
> select parent_name,
> (
> select decode(child_nr,null,1,max(child_nr)+1)
> from child_table
> where child_table.parent_name(+) = parent_table.parent_name
> )
> from parent_table

Maybe - but remember: i am using multiple sessions. And i fear that oracle makes no atomic transaction. For example the *substatements* might be executed in *parallel* in the different sessions.

Especially the "select decode(child_nr,null,1,max(child_nr)+1)" is critical.

Imagine the following execution sequence: assume child_nr = 5

Session 1: do subselect: select decode(child_nr,null,1,max(child_nr)+1) -> 6 Session 2: do subselect: select decode(child_nr,null,1,max(child_nr)+1) -> also 6
Session 1: insert into child_table (parent_name,child_nr,...) ... select parent_name, (... result from above ...)... Session 2: insert into child_table (parent_name,child_nr,...) ... select parent_name, (... result from above ...)...

So the vital question is: Does Oracle execute your statement ATOMIC ?

cu,
hal Received on Mon Feb 03 2003 - 02:42:13 CST