Wednesday, 27 March 2013

Oracle PL/SQL Merge with sequence

In Oracle 11g it is not possible to use a sequence in the USING clause of a MERGE statement. To get around this limitation enter a dummy integer in the USING clause and specify the sequence in the VALUES clause.

Putting Sequence in the subquery following USING in a MERGE statement gives an error
-- Eg>

MERGE INTO mytab tab
USING (SELECT 1 as col1,  myseq.nextval seq from dual) q1
ON (tab.col1 = q1.col1)  -- The ON clause will take ANDs eg. ON (t1.c1 = t2.c1 AND t1.c2=t2.c2)
WHEN NOT MATCHED THEN
INSERT (
  col1
)
VALUES (
  myseq.nextval
);

Gives

Error report:
SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

Use a dummy value eg -999 instead

MERGE INTO mytab tab
USING (SELECT 1 as col1, -999 seq from dual) q1
ON (tab.col1 = q1.col1)
WHEN NOT MATCHED THEN
INSERT (
  col1
)
VALUES (
  myseq.nextval
);

No comments:

Post a Comment