Loging queries and their error Text

logoracle

I need to find out the statements that are unsuccessfully executed in sql, along with their error trace. As of now I created a trigger which is triggered on server error, which stores the sql text and the SQLERRM in a table.

Is that the right way to do? Is there a more efficient way? I searched about AWR and Auditing. But they dont seem to serve this purpose.

I use Oracle SQL by the way.

Best Answer

This is a common case where autonomous transactions would be helpful. In a situation where you have a transaction the might or not fail, you'll want to log the error, but as an independent transaction, without affecting the main one.

Example from Tom Kyte:

create or replace procedure log_error( ..... )
as
   pragma autonomous_transaction;
begin
   insert into logging_table (...) values (...);
   commit;
end;



and then in your plsql code you can: 


begin
    .... your code
exception
    when others then 
           log_error( .... );
           RAISE;  /* this is crucial, you MUST re-raise the exception */
end;

If you need to know more about autonomous transaction, please check: http://docs.oracle.com/cd/E25054_01/server.1111/e25789/transact.htm#i7733

Related Question