Unique Constrain violation

CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(50) );

CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50), dept_id NUMBER,
CONSTRAINT emp_fk FOREIGN KEY (dept_id)
REFERENCES dept (dept_id) );

CREATE SEQUENCE myseq1 NOCACHE;
Now examine this statement:

INSERT ALL INTO emp (emp_id, emp_name)
VALUES (myseq1.nextVal, ‘name1’) — name1 insertion
INTO dept (dept_id, dept_name)
VALUES (10, ‘dept1’) — dept1 insertion
INTO emp (emp_id, emp_name, dept_id)
VALUES (myseq1.nextVal, ‘name2’, 10) — name2 insertion

SELECT * FROM dual;

Question: when ran above, giving me error msg and unique constraint violated, can someone please explain me where and how unique key is violated?

Thanks in advance!

I tried reading oracle support document, tried running query in Oracle SQl developer.
Now I’m not getting the reason behind that error.

>Solution :

sequence.NEXTVAL in INSERT ALL is evaluated only once.
Either you change to multi INSERT, either you wrap the sequence.NEXTVAL in a function.