CODEWITHSUNDEEP
oracle10g
sanjana d best
sanjana d best

Reputation: 23

use two .nextval in an insert statement

I'm using oracle database and facing a problem where two id_poduct.nextval is creating as error: ORA-00001: unique constraint (SYSTEM.SYS_C004166) violated

It is a primary key. To use all is a requirement. Can I use 2 .nextval in a statement?

insert all 
   into sale_product values (id_product.nextval, id.currval, 'hello', 123, 1) 
   into sale_product values (id_product.nextval, id.currval, 'hi', 123, 1) 
select * from dual;

Upvotes: 2

Views: 4346

Answers (2)

Jon Heller
Jon Heller

Reputation: 36922

insert into sale_product
select id_product.nextval, id.currval, a, b, c
from
(
    select 'hello' a, 123 b, 1 c from dual union all
    select 'hi'    a, 123 b, 1 c from dual
);

This doesn't use the insert all syntax, but it works the same way if you are only inserting into the same table.

Upvotes: 3

Gaurav Soni
Gaurav Soni

Reputation: 6346

The value of id_product.NEXTVAL in the first INSERT is the same as the second INSERT, hence you'll get the unique constraint violation. if you remove the constraint and perform the insert, you'll notice the duplicate values!

The only way is to perform two bulk INSERTS in sequence or to have two seperate sequences with a different range, the latter would require an awful lot of coding and checking.

create table temp(id  number ,id2 number);

insert all 
   into temp values (supplier_seq.nextval, supplier_seq.currval) 
   into temp values (supplier_seq.nextval, supplier_seq.currval) 
select * from dual;


    ID        ID2
---------- ----------
     2          2
     2          2

Refrence The subquery of the multitable insert statement cannot use a sequence http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2080134

Upvotes: 2

Related Questions