Capturing errors from MERGE statement

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

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.