I have a situation where I'm attempting to load into two tables using INSERT ALL and the select statement used to provide the data includes a sequence NEXTVAL. Now I know that Oracle specifically says you can't use a sequence like this but I'm wondering if it's possible to get round this. I've checked out AskTom but the situation he deals there with is slightly different to what I need, as I need to insert the same sequence value into two tables.
So in an ideal world my SQL statement would be:
insert all
into t1 ( a, b, c ) values ( seq_val, val1, val2 )
into t2 ( x, y, z ) values ( seq_val, val3, val4 )
select some_sequence.nextval seq_val, val1, val2, val3, val4 from some_table
Obviously this causes "ORA-02287: sequence number not allowed here".
I saw one solution that suggested using nextval and currval, so something like:
insert all
into t1 ( a, b, c ) values ( some_sequence.nextval, val1, val2 )
into t2 ( x, y, z ) values ( some_sequence.currval, val3, val4 )
select val1, val2, val3, val4 from some_table
...but this assumes that the order in which the inserts happen will always be consistent...which I wouldn't have thought would be absolutely guaranteed? Is that true? I've tried using it on a simple example (table with few thousand rows) and it does work, but I'm not sure whether it would definitely work in all cases?
Is there a better way of achieving this? Would it be better to ditch the INSERT ALL altogether and instead use BULK COLLECT and FORALL to insert into the two tables?