This is nothing new, but I wanted to throw out a quick post to document it. If you have a sequence.nextval in the insert part of a merge statement the merge calls nextval for all the updated rows as well.
Oracle has a bug report about this from a 9.2 issue, so this is nothing new:
Bug 6827003 : SEQUENCE # IN MERGE BEING UPDATED FOR BOTH INSERT AND UPDATE
I created a couple of testcases if you want to try them: sequencewithmerge.zip
Oracle’s bug report says you can work around the issue by encasing the sequence.nextval call in a function so I tried it and it works.
Anyway, you can’t count on the sequence only being advanced on inserted rows with merge statements if you include sequence.nextval in the insert part of the merge statement.
Bobby
