Insert All with Sequence.nextVal Generates Unique Constraint violation

Question

Why does this query throw an error:

Error report - ORA-00001: unique constraint (ON24MASTER.LANGUAGE_CODE_PK) violated

Query

INSERT ALL 
    INTO LANGUAGE_CODE(language_code_id,label,language_cd,country_cd,is_elite)
        VALUES(SEQ_LANGUAGE_CODE_ID.NEXTVAL,'Abkhazian','ab',NULL,'N')
    INTO LANGUAGE_CODE(language_code_id,label,language_cd,country_cd,is_elite)
        VALUES(SEQ_LANGUAGE_CODE_ID.NEXTVAL,'Afar','aa',NULL,'N')
    INTO LANGUAGE_CODE(language_code_id,label,language_cd,country_cd,is_elite)
        VALUES(SEQ_LANGUAGE_CODE_ID.NEXTVAL,'Afrikaans','af',NULL,'N')
    INTO LANGUAGE_CODE(language_code_id,label,language_cd,country_cd,is_elite)
        VALUES(SEQ_LANGUAGE_CODE_ID.NEXTVAL,'Akan','ak',NULL,'N')
SELECT 1 FROM DUAL;

Considering select seq_language_code_id.nextval from dual; = 198 and the latest id in db is 180;

What is wrong ? can I not use insert all with .nextVal ?

Answer 1

Nope, you can't do it with INSERT ALL. Have a look at what's going on:

SQL> create sequence seqa;

Sequence created.

SQL> create table test (id number);

Table created.

SQL> insert all
  2    into test values (seqa.nextval)
  3    into test values (seqa.nextval)
  4    into test values (seqa.nextval)
  5    into test values (seqa.nextval)
  6  select * from dual;

4 rows created.

SQL> select * From test;

        ID
----------
         1
         1
         1
         1

SQL>

See? All NEXTVALs are just the same, which - in your case - leads to a primary key violation, which means that you'll have to run separate INSERT INTO statements:

SQL> insert into test values (seqa.nextval);

1 row created.

SQL> insert into test values (seqa.nextval);

1 row created.

SQL> insert into test values (seqa.nextval);

1 row created.

SQL> select  * From test;

        ID
----------
         1
         1
         1
         1
         2
         3
         4

7 rows selected.

SQL>