Capture sql/pl error code

[meridian99]

I was wondering if there is a way in a script of sql/pl code within the 'exception' block to capture a oracle error code to assist in debugging. My code is failing on an INSERT.

thanks

 

[sem]

Check SQLCODE value.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Sure,

But by "capture", what do you actually want to do with the error code...put it in a table...display it to the screen...something else?

Below is a way to place the error code in a table:

create table errors (when_occurred date, error_code varchar2(100));

declare
    x number;
    hold_err varchar2(100);
begin
    x := 'a' + 'b';
exception
    when others then
        hold_err := sqlerrm;
        insert into errors values (sysdate,hold_err);
        commit;
end;
/

PL/SQL procedure successfully completed.

select to_char(when_occurred,'yyyy-mm-dd hh24:mi:ss')a,error_code
from errors;

Error
Time                ERROR_CODE
------------------- -------------------------------------------------------------------------------
2005-05-26 09:10:02 ORA-06502: PL/SQL: numeric or value error: character to number conversion error

1 row selected.
***************************************************************************************************

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.