A solution to find the exact line number where the exception
is raised in oracle PL/SQL block.
There is a provision in Oracle to know exactly what is the line number of the
error, to achieve this, we need to add the statement
“DBMS_UTILITY.FORMAT_ERROR_BACKTRACE” in the raise application error which will
give the exact line number.
Sample:
01
CREATE OR REPLACE PROCEDURE error_test
02
IS
03
BEGIN
04
DECLARE
05
numb NUMBER;
06
BEGIN
07
numb := 'test';
08
EXCEPTION
09
WHEN OTHERS
10
THEN
11
raise_application_error (
12
-20000,
13
'test error
' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || SQLERRM);
14
END;
15
END;
Execute the above SP: exec error_test;
Error details:
Before (without DBMS_UTILITY.FORMAT_ERROR_BACKTRACE):
ORA-20000: test error
ORA-06502: PL/SQL: numeric or value error: character to
number conversion error
ORA-06512: at "MASTERS.ERROR_TEST", line 11
ORA-06512: at line 1
After (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE):
ORA-20000: test error
ORA-06512: at
"MASTERS.ERROR_TEST", line 7
ORA-06502: PL/SQL: numeric or value error: character to
number conversion error
ORA-06512: at "MASTERS.ERROR_TEST", line 11
ORA-06512: at line 1
