2 Replies Latest reply on Dec 19, 2013 10:58 AM by Solomon Yakobson

    error

    994149

      suppose i wrote one procedure which having 200 lines of program and i wrote the exception

      block also ,if any way i got one error in my program but i need to know

      1>how i know in which line i have an error ?

      plz give me one example .................

        • 1. Re: error
          Karthick2003

          There are tools in DBMS_UTILITY

           

          FORMAT_CALL_STACK

          FORMAT_ERROR_BACKTRACE
          FORMAT_ERROR_STACK

           

           

          SQL> declare
            2  begin
            3    raise no_data_found;
            4  exception
            5      when no_data_found then
            6        dbms_output.put_line('Error occured at...');
            7        dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
            8        raise;
            9  end;
          10  /
          Error occured at...
          ORA-06512: at line 3

           

          declare
          *
          ERROR at line 1:
          ORA-01403: no data found
          ORA-06512: at line 8

          • 2. Re: error
            Solomon Yakobson

            Exceptions blocks must be used only when really needed. It is a common mistake to write something like:

             

            WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); RAISE;

             

            Such exception block not just useless but, as you already noted, masks actuall error line. But in some cases we do need WHEN OTHERS. For example we need to close cursors or files. In such case we can use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:

             

            Here we know error was raised on line 4:

             

            SQL> set serveroutput on
            SQL> create or replace
              2    procedure p1
              3      is
              4      begin
              5          raise no_data_found;
              6  end;
              7  /

            Procedure created.

            SQL> exec p1
            BEGIN p1; END;

            *
            ERROR at line 1:
            ORA-01403: no data found
            ORA-06512: at "SCOTT.P1", line 4
            ORA-06512: at line 1

             

            Here we lost original line where error was raised and get line where it was re-raised.

             

            SQL> create or replace
              2    procedure p1
              3      is
              4      begin
              5          raise no_data_found;
              6        exception
              7          when others then dbms_output.put_line(sqlerrm); raise;
              8  end;
              9  /

            Procedure created.

            SQL> exec p1
            ORA-01403: no data found
            BEGIN p1; END;

            *
            ERROR at line 1:
            ORA-01403: no data found
            ORA-06512: at "SCOTT.P1", line 6
            ORA-06512: at line 1

             

            Here we lost original line where error was raised but use dbms_utility.format_error_backtrace to get it:

             

            SQL> create or replace
              2    procedure p1
              3      is
              4      begin
              5          raise no_data_found;
              6        exception
              7          when others then dbms_output.put_line(sqlerrm);
              8                           dbms_output.put_line(dbms_utility.format_error_backtrace);
              9                           raise;
            10  end;
            11  /

            Procedure created.

            SQL> exec p1
            ORA-01403: no data found
            ORA-06512: at "SCOTT.P1", line 4

            BEGIN p1; END;

            *
            ERROR at line 1:
            ORA-01403: no data found
            ORA-06512: at "SCOTT.P1", line 8
            ORA-06512: at line 1


            SQL>

             

            SY.