Solved

How to return line number on error in plsql

Posted on 2002-07-10
9
2,689 Views
Last Modified: 2010-05-18
If an error occurs in pl/sql code you can show the error in the exception block using sqlcode and sqlerrm or use dbms_utility.format_error_stack which only displays the first line of the error stack. Is there some way to get the other lines of the error stack to get the line number where the error occured?
0
Comment
Question by:wimbaaijens
9 Comments
 
LVL 6

Expert Comment

by:M-Ali
ID: 7143264
Try dbms_utility.format_call_stack.

In PLSQL use
  DBMS_OUTPUT.PUT_LINE(dbms_utility.format_call_stack);


HTH
Ali

0
 

Author Comment

by:wimbaaijens
ID: 7143303
The call stack only tells me who called my procedure and in which line it was called, it doesn't tell me on which line the error occured.
0
 
LVL 6

Expert Comment

by:M-Ali
ID: 7143389
I guess you are using nested procedures, you need to call the "call stack" procedure where the exception is raised. Also read this :

http://asktom.oracle.com/pls/ask/f?p=4950:8:1027284::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:433029981484,%7Bdbms_utility.format_call_stack%7D


Ali
0
 

Author Comment

by:wimbaaijens
ID: 7143412
I am indeed using nested procedures and am able to determine in which procedure the error occured using the call stack, but I want to know the line number where the error occured not only the procedure where the error occured.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:mnicoras
ID: 7143450
Hi,

I know what you want and I tell you that the solution we use it's currently the only way to have a good trace for errors.

everywhere in the PLSQL source code we have defined a variable mStep PLS_INTEGER and for every major action (DML statement, conversion assignments and so on we increment the step with 10 (for future changes we increment by 10 because some new inserted changes to have range-values for mStep). At the end of every procedure, function we have a general exception block on which we raise further the error bu including in the message the step value... Something like:


procedure x is
mStep PLS_INTEGER:=0;
begin
mStep := 10;
....
mStep := 50;
....
mStep := 150;
....
exception
  when others then
    rollback;
    raise_application_error(-20001,'step='||mStep||' code='||sqlcode||' msg='||sqlerrm);
end;

best regards,
Marius Nicoras
0
 
LVL 47

Accepted Solution

by:
schwertner earned 200 total points
ID: 7143454
This article has attachments which implement ErrorPkg, a generic error
handler for PL/SQL.  This package uses DBMS_UTILITY.FORMAT_ERROR_STACK and
DBMS_UTILITY. FORMAT_CALL_STACK to report details on errors and the PL/SQL
call stack when they occur.

To install this package, first run the error_tables.sql script to create
the database tables, then the error_pkg SQL script to create the package
itself.


Use of the ErrorPkg
-------------------

The error_test.sql file illustrates how to use this package:
 
SQL> @error_test
SQL> DROP TABLE ttt;
 
Table dropped.
 
SQL>
SQL> CREATE TABLE ttt (f1 number);
 
Table created.
 
SQL>
SQL> CREATE OR REPLACE TRIGGER ttt_insert
  2    BEFORE INSERT ON ttt
  3  BEGIN
  4    RAISE ZERO_DIVIDE;
  5  END ttt_insert;
  6  /
 
Trigger created.
 
SQL>
SQL> CREATE OR REPLACE PROCEDURE C AS
  2  BEGIN
  3    INSERT INTO ttt VALUES (7);
  4  EXCEPTION
  5    WHEN OTHERS THEN
  6      ErrorPkg.HandleAll(FALSE);
  7      RAISE;
  8  END C;
  9  /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE B AS
  2  BEGIN
  3    C;
  4  EXCEPTION
  5    WHEN OTHERS THEN
  6      ErrorPkg.HandleAll(FALSE);
  7      RAISE;
  8  
  8  END B;
  9  /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE A AS
  2    v_ErrorSeq NUMBER;
  3  BEGIN
  4    B;
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      ErrorPkg.HandleAll(TRUE);
  8      ErrorPkg.StoreStacks('Scott', v_ErrorSeq, TRUE);
  9      ErrorPkg.PrintStacks('Scott', v_ErrorSeq);
 10  END A;
 11  /
 
Procedure created.
 
SQL> show errors
No errors.
SQL> exec a
17-JAN-97 11:07:46  Module: Scott  Error #5:  ORA-1476: divisor is equal to zero
Complete Call Stack:
  Object Handle  Line Number  Object Name
  -------------  -----------  -----------
  e02a5a4c       6            procedure SCOTT.C
  e02cb4f4       3            procedure SCOTT.B
  e029c7ec       4            procedure SCOTT.A
  e02cad38       1            anonymous block
Complete Error Stack:
  ORA-1476: divisor is equal to zero
  ORA-6512: at "SCOTT.TTT_INSERT", line 2
  ORA-4088: error during execution of trigger 'SCOTT.TTT_INSERT'
 
PL/SQL procedure successfully completed.


Call HandleAll(FALSE) at the lowest level of procedure nesting that you
want to track errors, then reraise the error.  The error is popped off
the PL/SQL call stack, so call HandleAll(FALSE) each time, since it is in
procedures C and B.

At the topmost level (procedure A), HandleAll(TRUE) should be called,
followed by StoreStacks.  StoreStacks records the error information in the
database tables, and returns the sequence number used to record the
information.  PrintStacks echoes out the error information from the tables
to the screen using DBMS_OUTPUT.


Attachments
-----------

------------------------------------------------------------------------------
error_tables.sql
------------------------------------------------------------------------------
DROP TABLE errors CASCADE CONSTRAINTS;

CREATE TABLE errors (
  module       VARCHAR2(50),
  seq_number   NUMBER,
  error_number NUMBER,
  error_mesg   VARCHAR2(100),
  error_stack  VARCHAR2(2000),
  call_stack   VARCHAR2(2000),
  timestamp    DATE,
  PRIMARY KEY (module, seq_number));

DROP TABLE call_stacks;

CREATE TABLE call_stacks (
  module        VARCHAR2(50),
  seq_number    NUMBER,
  call_order    NUMBER,
  object_handle VARCHAR2(10),
  line_num      NUMBER,
  object_name   VARCHAR2(80),
  PRIMARY KEY (module, seq_number, call_order),
  FOREIGN KEY (module, seq_number) REFERENCES errors ON DELETE CASCADE);

DROP TABLE error_stacks;

CREATE TABLE error_stacks (
  module        VARCHAR2(50),
  seq_number    NUMBER,
  error_order   NUMBER,
  facility      CHAR(3),
  error_number  NUMBER(5),
  error_mesg    VARCHAR2(100),
  PRIMARY KEY (module, seq_number, error_order),
  FOREIGN KEY (module, seq_number) REFERENCES errors ON DELETE CASCADE);

DROP SEQUENCE error_seq;

CREATE SEQUENCE error_seq
  START WITH 1
  INCREMENT BY 1;


------------------------------------------------------------------------------
error_pkg.sql
------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE ErrorPkg AS
  /* Generic error handling package, using DBMS_UTILITY.FORMAT_ERROR_STACK
     and DBMS_UTILITY.FORMAT_CALL_STACK.  This package stores general
     error information in the errors table, with detailed call stack and
     error stack information in the call_stacks and error_stacks tables,
     respectively. */

  -- Entry point for handling errors.  HandleAll should be called from all
  -- exception handlers where you want the error to be logged.  p_Top should be
  -- TRUE only at the topmost level of procedure nesting.  It should be FALSE
  -- at other levels.  See error_readme.txt for details on usage.
  PROCEDURE HandleAll(p_Top BOOLEAN);

  -- Prints the error and call stacks (using DBMS_OUTPUT) for the given
  -- module and sequence number.
  PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
                        p_SeqNum IN errors.seq_number%TYPE);

  -- Unwinds the call and error stacks, and stores them in the errors and
  -- call_stacks tables.  Returns the sequence number under which the
  -- error is stored.
  -- If p_CommitFlag is TRUE, then the inserts are committed.
  -- In order to use StoreStacks, an error must have been handled.  Thus
  -- HandleAll should have been called with p_Top = TRUE.
  PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
                        p_SeqNum OUT errors.seq_number%TYPE,
                        p_CommitFlag BOOLEAN DEFAULT FALSE);

END ErrorPkg;
/
show errors

CREATE OR REPLACE PACKAGE BODY ErrorPkg AS

  v_NewLine     CONSTANT CHAR(1) := CHR(10);

  v_Handled     BOOLEAN := FALSE;
  v_ErrorStack  VARCHAR2(2000);
  v_CallStack   VARCHAR2(2000);

  PROCEDURE HandleAll(p_Top BOOLEAN) IS
  BEGIN
    IF p_Top THEN
      v_Handled := FALSE;
    ELSIF NOT v_Handled THEN
      v_Handled := TRUE;
      v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK;
      v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK;
    END IF;      
  END HandleAll;

  PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
                        p_SeqNum IN errors.seq_number%TYPE) IS
    v_TimeStamp errors.timestamp%TYPE;
    v_ErrorMsg  errors.error_mesg%TYPE;

    CURSOR c_CallCur IS
      SELECT object_handle, line_num, object_name
        FROM call_stacks
        WHERE module = p_Module
        AND seq_number = p_SeqNum
        ORDER BY call_order;

    CURSOR c_ErrorCur IS
      SELECT facility, error_number, error_mesg
        FROM error_stacks
        WHERE module = p_Module
        AND seq_number = p_SeqNum
        ORDER BY error_order;
  BEGIN
    SELECT timestamp, error_mesg
      INTO v_TimeStamp, v_ErrorMsg
      FROM errors
      WHERE module = p_Module
      AND seq_number = p_SeqNum;

    -- Output general error information.
    DBMS_OUTPUT.PUT(TO_CHAR(v_TimeStamp, 'DD-MON-YY HH24:MI:SS'));
    DBMS_OUTPUT.PUT('  Module: ' || p_Module);
    DBMS_OUTPUT.PUT('  Error #' || p_SeqNum || ':  ');
    DBMS_OUTPUT.PUT_LINE(v_ErrorMsg);

    -- Output the call stack.
    DBMS_OUTPUT.PUT_LINE('Complete Call Stack:');
    DBMS_OUTPUT.PUT_LINE('  Object Handle  Line Number  Object Name');
    DBMS_OUTPUT.PUT_LINE('  -------------  -----------  -----------');
    FOR v_CallRec in c_CallCur LOOP
      DBMS_OUTPUT.PUT(RPAD('  ' || v_CallRec.object_handle, 15));
      DBMS_OUTPUT.PUT(RPAD('  ' || TO_CHAR(v_CallRec.line_num), 13));
      DBMS_OUTPUT.PUT_LINE('  ' || v_CallRec.object_name);
    END LOOP;

    -- Output the error stack.
    DBMS_OUTPUT.PUT_LINE('Complete Error Stack:');
    FOR v_ErrorRec in c_ErrorCur LOOP
      DBMS_OUTPUT.PUT('  ' || v_ErrorRec.facility || '-');
      DBMS_OUTPUT.PUT(TO_CHAR(v_ErrorRec.error_number) || ': ');
      DBMS_OUTPUT.PUT_LINE(v_ErrorRec.error_mesg);
    END LOOP;
   
  END PrintStacks;

  PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
                        p_SeqNum OUT errors.seq_number%TYPE,
                        p_CommitFlag BOOLEAN DEFAULT FALSE) IS
    v_SeqNum     NUMBER;

    v_Index      NUMBER;
    v_Length     NUMBER;
    v_End        NUMBER;

    v_Call       VARCHAR2(100);
    v_CallOrder  NUMBER := 1;
    v_Handle     call_stacks.object_handle%TYPE;
    v_LineNum    call_stacks.line_num%TYPE;
    v_ObjectName call_stacks.object_name%TYPE;

    v_Error      VARCHAR2(120);
    v_ErrorOrder NUMBER := 1;
    v_Facility   error_stacks.facility%TYPE;
    v_ErrNum     error_stacks.error_number%TYPE;
    v_ErrMsg     error_stacks.error_mesg%TYPE;

    v_FirstErrNum errors.error_number%TYPE;
    v_FirstErrMsg errors.error_mesg%TYPE;
  BEGIN
    -- First get the error sequence number.
    SELECT error_seq.nextval
      INTO v_SeqNum
      FROM dual;

    p_SeqNum := v_SeqNum;

    -- Insert the first part of the header information into the errors table.
    INSERT INTO errors
      (module, seq_number, error_stack, call_stack, timestamp)
    VALUES
      (p_Module, v_SeqNum, v_ErrorStack, v_CallStack, SYSDATE);

    -- Unwind the error stack to get each error out.  We do this by scanning
    -- the error stack string.  Start with the index at the beginning of the
    -- string.
    v_Index := 1;
 
    -- Loop through the string, finding each newline.  A newline ends each
    -- error on the stack.
    WHILE v_Index <  LENGTH(v_ErrorStack) LOOP
      -- v_End is the position of the newline.
      v_End := INSTR(v_ErrorStack, v_NewLine, v_Index);

      -- Thus, the error is between the current index and the newline.
      v_Error := SUBSTR(v_ErrorStack, v_Index, v_End - v_Index);

      -- Skip over the current error, for the next iteration.
      v_Index := v_Index + LENGTH(v_Error) + 1;

      -- An error looks like 'facility-number: mesg'.  We need to get each
      -- piece out for insertion.

      -- First, the facility is the first 3 characters of the error.
      v_Facility := SUBSTR(v_Error, 1, 3);

      -- Remove the facility and the dash (always 4 characters).
      v_Error := SUBSTR(v_Error, 5);

      -- Now we can get the error number.
      v_ErrNum := TO_NUMBER(SUBSTR(v_Error, 1, INSTR(v_Error, ':') - 1));

      -- Remove the error number, colon and space (always 7 characters).
      v_Error := SUBSTR(v_Error, 8);

      -- What's left is the error message.
      v_ErrMsg := v_Error;

      -- Insert the errors, and grab the first error number and message
      -- while we're at it.
      INSERT INTO error_stacks
        (module, seq_number, error_order, facility, error_number, error_mesg)
      VALUES
        (p_Module, p_SeqNum, v_ErrorOrder, v_Facility, v_ErrNum, v_ErrMsg);

      IF v_ErrorOrder = 1 THEN
        v_FirstErrNum := v_ErrNum;
        v_FirstErrMsg := v_Facility || '-' || TO_NUMBER(v_ErrNum) ||
                         ': ' || v_ErrMsg;
      END IF;

      v_ErrorOrder := v_ErrorOrder + 1;
    END LOOP;
 
    -- Update the errors table with the message and code.
    UPDATE errors
      SET error_number = v_FirstErrNum,
          error_mesg = v_FirstErrMsg
      WHERE module = p_Module
      AND seq_number = v_SeqNum;

    -- Now we need to unwind the call stack, to get each call out.  We do this
    -- be scanning the call stack string.  Start with the index after the
    -- first call on the stack.  This will be after the first occurrence of
    -- 'name' and the newline.
    v_Index := INSTR(v_CallStack, 'name') + 5;

    -- Loop through the string, finding each newline.  A newline ends each
    -- call on the stack.
    WHILE v_Index <  LENGTH(v_CallStack) LOOP
      -- v_End is the position of the newline.
      v_End := INSTR(v_CallStack, v_NewLine, v_Index);

      -- Thus, the call is between the current index and the newline.
      v_Call := SUBSTR(v_CallStack, v_Index, v_End - v_Index);

      -- Skip over the current call, for the next iteration.
      v_Index := v_Index + LENGTH(v_Call) + 1;

      -- Within a call, we have the object handle, then the line number, then
      -- the object name, separated by spaces.  We need to separate them
      -- out for insertion.

      -- Trim white space from the call first.
      v_Call := LTRIM(v_Call);

      -- First get the object handle.
      v_Handle := SUBSTR(v_Call, 1, INSTR(v_Call, ' '));

      -- Now, remove the object handle, then the white space from the call.
      v_Call := SUBSTR(v_Call, LENGTH(v_Handle) + 1);
      v_Call := LTRIM(v_Call);

      -- Now we can get the line number.
      v_LineNum := TO_NUMBER(SUBSTR(v_Call, 1, INSTR(v_Call, ' ')));

      -- Remove the line number, and white space.
      v_Call := SUBSTR(v_Call, LENGTH(v_LineNum) + 1);
      v_Call := LTRIM(v_Call);

      -- What is left is the object name.
      v_ObjectName := v_Call;

      -- Insert all calls except the call for ErrorPkg.
      IF v_CallOrder > 1 THEN
        INSERT INTO call_stacks
          (module, seq_number, call_order, object_handle, line_num, object_name)
        VALUES
          (p_Module, v_SeqNum, v_CallOrder, v_Handle, v_LineNum, v_ObjectName);
      END IF;

      v_Callorder := v_CallOrder + 1;

    END LOOP;

    IF p_CommitFlag THEN
      commit;
    END IF;
  END StoreStacks;

END ErrorPkg;
/

show errors

------------------------------------------------------------------------------
error_test.sql
------------------------------------------------------------------------------
DROP TABLE ttt;

CREATE TABLE ttt (f1 number);

CREATE OR REPLACE TRIGGER ttt_insert
  BEFORE INSERT ON ttt
BEGIN
  RAISE ZERO_DIVIDE;
END ttt_insert;
/

CREATE OR REPLACE PROCEDURE C AS
BEGIN
  INSERT INTO ttt VALUES (7);
EXCEPTION
  WHEN OTHERS THEN
    ErrorPkg.HandleAll(FALSE);
    RAISE;
END C;
/
show errors

CREATE OR REPLACE PROCEDURE B AS
BEGIN
  C;
EXCEPTION
  WHEN OTHERS THEN
    ErrorPkg.HandleAll(FALSE);
    RAISE;

END B;
/
show errors

CREATE OR REPLACE PROCEDURE A AS
  v_ErrorSeq NUMBER;
BEGIN
  B;
EXCEPTION
  WHEN OTHERS THEN
    ErrorPkg.HandleAll(TRUE);
    ErrorPkg.StoreStacks('Scott', v_ErrorSeq, TRUE);
    ErrorPkg.PrintStacks('Scott', v_ErrorSeq);
END A;
/
show errors
0
 

Author Comment

by:wimbaaijens
ID: 7148556
This is what I've been looking for.

Marius your answer is a good one too (I've been using it myself) but it makes large pieces of code hard to read that is why I've been looking for a solution which doesn't need additional lines of code but direcly reads the error stack.
0
 

Expert Comment

by:sunil2expert
ID: 7537444
This work excellent for returning call stack.
But this still does not return line number for procedures.
Any other ideas.
0
 

Expert Comment

by:tfliegel
ID: 8732320
This error_stack thing only works, if the error is raised in a trigger, for errors in packages you might as well just use sqlerrm.
Conclusion: dbms_utility.format_error_stack is pretty much worthless.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now