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

Question

Hi. 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?

Answer 1

you need 10g or above. Check DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.

http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html

Answer 2

Hi there,

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/technology/oramag/oracle/05-mar/o25plsql.html

Cheers, niels

Answer 3

You could put your exception handler around every statement.