Tuesday, December 13, 2016

INSERT ALL and sequence NextVal

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1(n1 number, n2 number);
create table t2(n1 number, n2 number);
create table t3(n1 number, n2 number);

create sequence s1;

Problem:

INSERT ALL
    INTO t1(n1, n2) values(n1_seq, nn)
    INTO t2(n1, n2) values(n1_seq, nn) 
select s1.nextval n1_seq, rownum*10 nn from dual connect by level <= 2;

Error report -
SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

Fix:

INSERT ALL
    INTO t1(n1, n2) values(s1.nextval, nn)
    INTO t2(n1, n2) values(s1.currval, nn) 
    INTO t3(n1, n2) values(s1.currval, nn) 
select rownum*10 nn from dual connect by level <= 2;

select * from t1;
select * from t2;
select * from t3;