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?
The java. lang. StackTraceElement. getLineNumber() method returns the line number of the source line containing the execution point represented by this stack trace element.
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.
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.
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
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:
$$PLSQL_LINE & $$PLSQL_UNIT
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With