Inserting into two oracle tables with a sequence

Question

I have two tables in Oracle, job and reference.

I want to insert a new record into both tables, with a key generated from a sequence. Something like:

insert into (
select j.jobid, j.fileid, j.jobname, r.reffileid 
from job j
inner join reference r on j.jobid=r.jobid)
values (jobidsequence.nextval, 4660, 'name', 4391);

This, of course, leads to:

ORA-01776: cannot modify more than one base table through a join view

Is there a way of doing this without using PL/SQL? I'd dearly love to do it using just SQL.

Answer 1

You can use a side-effect of the insert all multi-table insert syntax for this:

insert all
into job (jobid, fileid, jobname)
values (jobidsequence.nextval, fileid, jobname)
into reference (jobid, reffileid)
values (jobidsequence.nextval, reffileid)
select  4660 as fileid, 'name' as jobname, 4391 as reffileid
from dual;

2 rows inserted.

select * from job;

     JOBID     FILEID JOBNAME  
---------- ---------- ----------
        42       4660 name       

select * from reference;

     JOBID  REFFILEID
---------- ----------
        42       4391 

SQL Fiddle.

From the restrictions:

You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.

Clearly I am using a sequence in the values clause, so the first sentence doesn't seem quite accurate; but you can't use it in the select part. (I'm not 100% sure if it can be used in the values in all versions, but the documentation is a little misleading in any case, and contradicts itself).

So I'm taking advantage of the fact that because it is a single statement, the two references to nextval get the same number, as the third sentence says, so the same sequence value is used in both tables.