Inserting multiple rows with sequence in Oracle

Question

This is the query i have used for insert multiple rows in oracle database. But when am using sequence within it it raises error as ORA-00001: unique constraint. How to do it.

INSERT ALL
  INTO POSTAL_CODE( postal_code,desc)
    VALUES(postal_code.NEXTVAL,'Coimbatore')
  INTO POSTAL_CODE (postal_code,desc)
    VALUES(postal_code.NEXTVAL,'Mumbai') SELECT * FROM DUAL;

Answer 1

The restrictions on multitable inserts include:

• 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.

That isn't quite true - you can use a sequence, it just always gets the same value, so it can be useful to create parent and child records in one go by referring to the same sequence.

If you want to continue to use insert all you could work around that by using a non-deterministic function that gets the sequence value:

CREATE FUNCTION get_seq RETURN NUMBER IS
BEGIN
  RETURN postal_code_seq.nextval;
END;
/

INSERT ALL
  INTO POSTAL_CODE( postal_code,description)
    VALUES(get_seq,'Coimbatore')
  INTO POSTAL_CODE (postal_code,description)
    VALUES(get_seq,'Mumbai') SELECT * FROM DUAL;

2 rows inserted.

SELECT * FROM postal_code;

                            POSTAL_CODE DESCRIPTION        
--------------------------------------- --------------------
                                      1 Coimbatore          
                                      2 Mumbai              

But that's a bit awkward. You're probably better off using individual insert statements - using a multitable insert into a single table isn't really gaining you much anyway - or a trigger to set the unique column from the sequence, or a CTE/inline view to generate the values to insert.