How to Find the exact Error line in SQL Scripts using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

If you encounter any error in SQL file and many of the times the error console never display the correct line number where the actual error exist. Instead it will display the wrong lines and which will re-route us in somewhere !!!

To avoid this we have an standard API DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and this show the correct line number where our real issue exist !

Sample Script:

DECLARE

err_code NUMBER;
err_msg  VARCHAR2(240);

BEGIN

{
...
.....
Logic of the Program
...
.....
}

EXCEPTION
     WHEN OTHERS THEN

      ROLLBACK;

err_code := SQLCODE;
err_msg  := SQLERRM;

FND_FILE.PUT_LINE(fnd_file.LOG,err_code||'->'||err_msg);
FND_FILE.PUT_LINE(fnd_file.LOG,DBMS_UTILITY.FORMAT_ERROR_STACK);
FND_FILE.PUT_LINE(fnd_file.LOG,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END;
/

Note: If your SQL scripts contains commented lines like /* */ or -- or REM then kindly ignore those lines and start your count from actual SQL script starts

Ex: SQL Code 

REM Script Name

-- Development Script version 1.0

/* 

Comments 1

Comments 2

*/

Declare

Line 1

Line 2

BEGIN

Line 3

Line 4

Exception 

When Others then 

Line 5

END;

Total Lines:  23 but actual script contains only 16

Kindly Ignore the comment lines (i.e) first 6 lines and then consider the remaining lines for the error findings

How to check Patch Details in R12.2.x

select ad_patch.is_patch_applied('R12',-1,19283503) from dual;