Problem solve Get help with specific problems with your technologies, process and projects.

Capturing errors from MERGE statement

Is there any way to capture errors from a MERGE statement? Also, is there any way to know how many records were inserted or updated for the MERGE statement like SQL%ROWCOUNT? Any assistance greatly appreciated.

You capture errors the same way you would if you were doing regular INSERT and UPDATE statements....with exception handlers. Just include a WHEN OTHERS exception handler in the block where your MERGE statement is and have to display SQLCODE and SQLERRM if an error occurs. Then you can figure out which specific errors are occurring and create individual exception handlers for those.

As far as being able to tell counts for inserts and updates performed by MERGE, there is not currently a way to do that. I've seen the question posed several times before on Metalink and Oracle support as simply said: You can create an enhancement request by going to Metalink -> News & Events and go to -> The Oracle Enhancement Request System!

For More Information

Dig Deeper on Oracle error messages

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.