Identify the line number where an exception was thrown

To identify the line number where an exception was thrown in PL/SQL, you can use the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This function provides a backtrace that includes the line number(s) in the PL/SQL block, procedure, or function where the error occurred, making it easier to pinpoint the location of the problem.

EXCEPTION WHEN OTHERS THEN 

DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM); 

END;

Output:

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

The above Example will throw the Error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" but we cannot find which line causing the issue without debugging the entire program. Here We can use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to identify the line number of the code which causing the issue.

EXCEPTION WHEN OTHERS THEN 

DBMS_OUTPUT.PUT_LINE('Error Backtrace: 'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); 

END;

 Output:

Error Message: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Error Backtrace: ORA-06512: at line 6