Saturday, May 15, 2010

Errorlogging Clause in 10.1 and above



I was working on the Errorlogging clause in the Insert statement which can be used to capture errors that may occur while inserting, and I noticed that Errorlogging clause does not automatically create an error logging table, if it is not specified. I was trying this using SQLDeveloper, so was not sure whether it was a bug in the SD or not. To test it, I ran it using SQL*Plus.

Here is the Insert statement with Error logging clause

Log Errors Syntax (Screen -01)
Since it appeared that Error log table can be optional, I decided to omit and run the Insert statement. However, Oracle returned an Error (Screen -02)

Not mentioning table is Not an option (Screen -02)
So, decided to follow the textbook approach and created the errorlog table (Screen -03)

Created Error Log Table (Screen -03)

As you can see from the screen below, the procedure got executed successfully (Screen -04)

Procedure to generate Error (Screen -04)

The data got inserted into the Err$_Employees Table (Screen -05). So, I think I might have hit into a Bug, however, I am still researching about it.
Data Inserted Into Error Log Table (Screen -05)



References:  

Click here to view Insert Statement
Click here to view DBMS_ERRLOG Statement

Click here to view well written example @orafaq.com
Bug: Could not find any bug other that this old one in Metalink # Bug 5255455