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;

Shawana

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.

Orthocephalic

Answer 2

Instead of modifying your database objects you can simply rewrite the multitable INSERT ALL into a single INSERT with multiple rows concatenated by UNION ALL:

INSERT INTO postal_code
SELECT postal_code_seq.NEXTVAL, description
FROM
(
    SELECT 'Coimbatore' description FROM dual UNION ALL
    SELECT 'Mumbai'     description FROM dual
);

Note that the sequence must be called in an outer query. Using a sequence in the inner query looks like it could simplify the code but would raise the error "ORA-02287: sequence number not allowed here".

Predominate

The error "ora-00947 not enough values" comes out. – Ostiary

The problem was solved, it turns out that not all columns were specified. – Ostiary

Answer 3

I would use a before insert trigger to populate the key column (if no value has been supplied by the insert) instead of this method. Sequences do not work well with multi-table insert.

Hessian

Answer 4

try to insert this multiple line in oracle database

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

Spermatozoid

Where are the substitution variables being set? – Orthocephalic

Answer 5

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

Spermatozoid

Where is the substitution variable being set? This doesn't address the problem with nextval only being incremented once for the whole statement, which is what causes the unique constraint violation. – Orthocephalic

When posting code as part of an answer you should include text which explains what the code is attempting to do, and how it solves the problem described in the question. This is not a helpful answer as it stands. – Clutter