Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way to get the line number where an exception was thrown?

Tags:

Im working on a pl-sql script, in which I have about 10 TO_CHAR conversions.

One of them is throwing an

ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

exception.

Currently, im logging the message with this piece of code

EXCEPTION   WHEN OTHERS THEN     DBMS_OUTPUT.put_line('Exception message is '||SQLERRM(sqlcode));     ROLLBACK; 

I'd like to add (mostly for debugging purposes) the line where the exception is thrown, in order to receive a message in the form of

ORA-06502: PL/SQL: numeric or value error: character string buffer too small (at line x) 

Is there an easy way to do this?

like image 783
Tom Avatar asked Oct 19 '09 15:10

Tom


People also ask

How can I get line number in stack trace?

The java. lang. StackTraceElement. getLineNumber() method returns the line number of the source line containing the execution point represented by this stack trace element.

What happens when an exception is thrown and the catch block is not found?

If an exception is thrown and its current function scope has no catch block, the exception will "bubble up" the call stack to the calling function until it finds a matching catch block. All finally blocks it encounters along the way will be executed.

What is stack trace in C#?

A trace of the method calls is called a stack trace. The stack trace listing provides a way to follow the call stack to the line number in the method where the exception occurs. The StackTrace property returns the frames of the call stack that originate at the location where the exception was thrown.


2 Answers

You need 10g to use

DBMS_OUTPUT.put_line('Error in '|| $$plsql_unit || ' at ' || $$plsql_line);

also look into using

DBMS_UTILITY.format_error_backtrace

there is an article in Oracle Magazine from april '05 by Steven Feuerstein:

http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25plsql-093886.html

Cheers, niels

like image 165
Niels Castle Avatar answered Oct 21 '22 15:10

Niels Castle


The answers have mentioned both, $$PLSQL_LINE & DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. But I would like to add a bit about the difference between them:

  1. Predefined Inquiry Directives $$PLSQL_LINE & $$PLSQL_UNIT
    PLSQL_LINE predefined inquiry directive is a PLS_INTEGER literal value indicating the line number reference to $$PLSQL_LINE in the current program unit.
    From its definition, PLSQL_LINE is not suitable for exceptions logging because it will provide the line number of the exception, rather than the line number of the error occurred itself. This makes it difficult to detect the error location especially with big program units, unless you wrap every statement with exception handler as Jeffrey answer's stated.
    However, the good thing about PLSQL_LINE, it provides the number without the need of any extraction, or string parsing. Hence, it could be more suitable for other logging purposes.
  2. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope.
    The advantage of using this procedure, is that it provides the exact line number in the program using where the error occurs, and not where the procedure call appears However, the procedure returns a string like ORA-XXXXX: at "<program_unit_name>", line xx. So if you are interested in extracting the line number itself, for whatever logging purpose you want, you will need parse the string.

Finally, to make the difference clear, below are two procedures, with the same content. You can run them and notice the output difference

CREATE OR REPLACE PROCEDURE proc_plsql_line     IS     BEGIN        RAISE VALUE_ERROR;     EXCEPTION        WHEN VALUE_ERROR        THEN           DBMS_OUTPUT.put_line ( 'Error raised in: '|| $$plsql_unit ||' at line ' || $$plsql_line || ' - '||sqlerrm);    END; / 

And

CREATE OR REPLACE PROCEDURE proc_backtrace     IS     BEGIN        RAISE VALUE_ERROR;     EXCEPTION        WHEN VALUE_ERROR        THEN           DBMS_OUTPUT.put_line ( 'Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);    END; / 

Execution:

exec proc_plsql_line;  Error raised in: PROC_PLSQL_LINE at line 8 - ORA-06502: PL/SQL: numeric or value error   exec proc_backtrace;  Error raised: ORA-06512: at "PROC_BACKTRACE", line 4 - ORA-06502: PL/SQL: numeric or value error 
like image 34
Hawk Avatar answered Oct 21 '22 16:10

Hawk