Insert multiple rows with sequence in Oracle

Question

This is the query that I used to insert multiple rows into the oracle database. But when I use the sequence inside it, it causes an error like ORA-00001: a 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

Limitations for multi-user inserts include:

• You cannot specify a sequence in any part of a multiple insertion. Multiple insertion is considered as a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent links in the statement return the same number.

This is not entirely true: you can use a sequence, it always gets the same value, so it can be useful to create parent and child records at a time by accessing the same sequence.

If you want to continue using insert all , you can get around this using a non-deterministic function that gets the value of the sequence:

 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 this is a little inconvenient. You are probably better off using separate insertion instructions — using multi-user insertion into the same table doesn’t really greatly increase you — or a trigger to set a unique column from a sequence or a CTE / inline view to generate values ​​for insertion.

Answer 2

I would use the before insert trigger to populate the key column (if the value was not added by the insert) instead of this method. Sequences do not work with inserts from multiple tables.

Answer 3

INSERT ALL INTO POSTAL_CODE (postal_code, desc) VALUES (postal_code.NEXTVAL, and desc) INTO POSTAL_CODE (postal_code, desc) VALUES (postal_code.NEXTVAL, & desp) SELECT * FROM DUAL;

Answer 4

try pasting this multiple row into oracle database

 INTO POSTAL_CODE (postal_code,desc) VALUES(&postal_code,&desc) SELECT * FROM DUAL;