Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Error, and the line number it occurred on

    So, if you write something without an error handler, you'll get the line number of the problem in the error message:
    Code:
    create or replace procedure proc1
    as
      num number;
    begin
      num := 1/0;
    end proc1;
    /
    
    PROCEDURE PROC1 compiled
    
    
    exec proc1();
    
    
    ORA-01476: divisor is equal to zero
    ORA-06512: at "FORBESC.PROC1", line 5
    ORA-06512: at line 1
    01476. 00000 -  "divisor is equal to zero"
    But once you add an error handler, you lose that information, and the RAISE() or RAISE_APPLICATION_ERROR() line number shows up in the error message:
    Code:
    create or replace procedure proc1
    as
      num number;
    begin
      num := 1/0;
    exception
      when others then
        raise;
    end proc1;
    /
    
    PROCEDURE PROC1 compiled
    
    
    exec proc1();
    
    ORA-01476: divisor is equal to zero
    ORA-06512: at "FORBESC.PROC1", line 8
    ORA-06512: at line 1
    01476. 00000 -  "divisor is equal to zero"
    We're looking a add a common error logging mechanism to our code, but in doing so, we're losing information about where an error is occurring. Is there a way to get both? An Exception Handler, and retain the line number of where an error occurred?

    Thanks,
    --=Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    PL/SQL: Tracing Lines

    Code:
    CREATE OR REPLACE PROCEDURE proc3
    IS
    BEGIN
      DBMS_OUTPUT.put_line ('calling proc2');
      proc2;
    EXCEPTION
      WHEN OTHERS
      THEN
        DBMS_OUTPUT.put_line ('Error stack at top level:');
        my_putline (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    END;
    /
    
    And now when I run proc3, I will see the following output:
    
    SQL> SET SERVEROUTPUT ON
    SQL> exec proc3
    calling proc2
    calling proc1
    running proc1
    Error stack at top level:
    ORA-06512: at "SCOTT.PROC1", line 4
    ORA-06512: at "SCOTT.PROC2", line 6
    ORA-06512: at "SCOTT.PROC3", line 4
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Perfect! Thanks! --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •