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