Oracle Aps Technical

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 Erro r "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...

The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code. The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code. You could use the SQLCODE & SQLERRM  function to raise an error as follows: EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Error - '||SQLCODE||' -ERROR- '||SQLERRM); END; Or you could log the error to a table using the SQLCODE & SQLERRM function as follows: EXCEPTION WHEN OTHERS THEN ERR_CODE := SQLCODE; ERR_MSG := SUBSTR(SQLERRM, 1, 200); INSERT INTO XXERROR_TABLE (ERROR_CODE, ERROR_MSG) VALUES (ERR_CODE, ERR_MSG); END;