3 Replies Latest reply on Mar 23, 2010 11:11 AM by 578885

    capturing oracle error codes into a variable

    user633278
      Hi

      Can someone show me how it is possible to save an Oracle defined error code into a variable? What I am trying to do is when a stored procedure fails an Oracle error is raised, such as ORA-xxxx, then pass this code into variable to be saved into a log.

      How do I achieve this?
        • 1. Re: capturing oracle error codes into a variable
          fsitja
          You could do something like this for example:
          SQL> create table error_message (msg varchar2(4000));
           
          Table created
           
          SQL> declare
            2    v_num number;
            3  begin
            4    v_num := 5/0;
            5  exception
            6    when others then
            7      insert into error_message values (DBMS_UTILITY.FORMAT_ERROR_STACK);
            8  end;
            9  / 
           
          PL/SQL procedure successfully completed
           
          SQL> select * from error_message;
           
          MSG
          --------------------------------------------------------------------------------
          ORA-01476: divisor is equal to zero
           
          SQL> 
          • 2. Re: capturing oracle error codes into a variable
            Solomon Yakobson
            user633278 wrote:

            How do I achieve this?
            Function SQLCODE in PL/SQL exception handler returns error code. SQLERRM returns message:
            SQL> declare
              2      x number;
              3  begin
              4      x := 1/0;
              5    exception
              6      when others
              7        then
              8          dbms_output.put_line('Error code: ' || SQLCODE);
              9          dbms_output.put_line('Error message: ' || SQLERRM);
             10  end;
             11  /
            Error code: -1476
            Error message: ORA-01476: divisor is equal to zero
            
            PL/SQL procedure successfully completed.
            
            SQL> 
            SY.
            • 3. Re: capturing oracle error codes into a variable
              578885
              Hi, as an extension to this query, how do we capture the line number of the PLSQL code where the error occured (ora-06512)?

              I have seen an option to initialize the statement number to a variable and print the variable in the exception.
              There is should be a better way definitely..