9-9. Tracing an Exception to Its Origin
Problem
Your application continues to raise an exception that is being caught with the OTHERS handler. You've used SQLCODE and DBMS_UTILITY.FORMAT_ERROR_STACK to help you find the cause of the exception but are still unable to do so.
Solution
Use the stack trace for the exception to trace the error back to its origination. In particular, use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and DBMS_UTILITY.FORMAT_CALL_TRACE to help you find the cause of the exception. The following solution demonstrates the use of FORMAT_ERROR_BACKTRACE:
CREATE OR REPLACE PROCEDURE obtain_emp_detail(emp_info IN VARCHAR2) IS
emp_qry VARCHAR2(500);
emp_first employees.first_name%TYPE;
emp_last employees.last_name%TYPE;
email employees.email%TYPE;
valid_id_count NUMBER := 0;
valid_flag BOOLEAN := TRUE;
temp_emp_info VARCHAR2(50);
BEGIN
emp_qry := 'SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ';
IF emp_info LIKE '%@%' THEN
temp_emp_info := substr(emp_info,0,instr(emp_info,'@')-1);
emp_qry := emp_qry || 'WHERE EMAIL = :emp_info';
ELSE
SELECT COUNT(*)
INTO valid_id_count
FROM employees
WHERE employee_id = emp_info;
IF valid_id_count > 0 THEN
temp_emp_info := emp_info;
emp_qry := emp_qry || 'WHERE EMPLOYEE_ID = :id';
ELSE
valid_flag := FALSE;
END IF;
END IF;
IF valid_flag = TRUE THEN
EXECUTE IMMEDIATE emp_qry
INTO emp_first, emp_last, email
USING temp_emp_info;
DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last || ' - ' || email);
ELSE
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE USED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE USED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('AN UNEXPECTED ERROR HAS OCCURRED, PLEASE ' ||
'TRY AGAIN'),
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
Here are the results when calling within invalid argument information:
SQL> exec obtain_emp_detail('junea@'),
THE INFORMATION YOU HAVE USED DOES NOT MATCH ANY EMPLOYEE RECORD
ORA-06512: at "OBTAIN_EMP_DETAIL", line 32
PL/SQL procedure successfully completed.
As you can see, the exact line number that caused the exception to be raised is displayed. This is especially useful if you use a development environment that includes line numbering for your source code. If not, then you can certainly count out the line numbers manually.
Similarly, DBMS_UTILITY.FORMAT_CALL_STACK lists the object number, line, and object where the issue had occurred. The following example uses the same procedure as the previous example, but this time DBMS_UTILITY.FORMAT_CALL_STACK is used in the exception handler:
CREATE OR REPLACE PROCEDURE obtain_emp_detail(emp_info IN VARCHAR2) IS
emp_qry VARCHAR2(500);
emp_first employees.first_name%TYPE;
emp_last employees.last_name%TYPE;
email employees.email%TYPE;
valid_id_count NUMBER := 0;
valid_flag BOOLEAN := TRUE;
temp_emp_info VARCHAR2(50);
BEGIN
emp_qry := 'SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ';
IF emp_info LIKE '%@%' THEN
temp_emp_info := substr(emp_info,0,instr(emp_info,'@')-1);
emp_qry := emp_qry || 'WHERE EMAIL = :emp_info';
ELSE
SELECT COUNT(*)
INTO valid_id_count
FROM employees
WHERE employee_id = emp_info;
IF valid_id_count > 0 THEN
temp_emp_info := emp_info;
emp_qry := emp_qry || 'WHERE EMPLOYEE_ID = :id';
ELSE
valid_flag := FALSE;
END IF;
END IF;
IF valid_flag = TRUE THEN
EXECUTE IMMEDIATE emp_qry
INTO emp_first, emp_last, email
USING temp_emp_info;
DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last || ' - ' || email);
ELSE
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE USED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE USED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('AN UNEXPECTED ERROR HAS OCCURRED, PLEASE ' ||
'TRY AGAIN'),
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
END;
Here are the results when calling within invalid argument information:
SQL> exec obtain_emp_detail('june@'),
THE INFORMATION YOU HAVE USED DOES NOT MATCH ANY EMPLOYEE RECORD
----- PL/SQL Call Stack -----
object line object
handle number
name
24DD3280 47 procedure OBTAIN_EMP_DETAIL
273AA66C 1
anonymous block
PL/SQL procedure successfully completed.
Each of the two utilities demonstrated in this solution serves an explicit purpose—to assist you in finding the cause of exceptions in your applications.
How It Works
Oracle provides a few different utilities to help diagnose and repair issues with code. The utilities discussed in this recipe provide feedback regarding exceptions that have been raised within application code. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is used to display the list of lines that goes back to the point at which your application fails. This utility was added in Oracle Database 10g. Its ability to identify the exact line number where the code has failed can save the time of reading through each line to look for the errors. Using this information along with the Oracle exception that is raised should give you enough insight to determine the exact cause of the problem.
The result from DBMS_UTILITY.FORMAT_ERROR_BACKTRACE can be assigned to a variable since it is a function. Most likely a procedure will be used to log the exceptions so that they can be reviewed at a later time. Such a procedure could accept the variable containing the result from DBMS_UTILITY.FORMAT_ERROR_BACKTRACE as input.
The DBMS_UTILITY.FORMAT_CALL_STACK function is used to print out a formatted string of the execution call stack or the sequence of calls for your application. It displays the different objects used, along with line numbers from which calls were made. It can be very useful for pinpointing those errors that you are having trouble resolving. It can also be useful for obtaining information regarding the execution order of your application. If you are unsure of exactly what order processes are being called, this function will give you that information.
Using a combination of these utilities when debugging and developing your code is a good practice. You may find it useful to create helper functions that contain calls to these utilities so that you can easily log all stack traces into a database table or a file for later viewing. These can be of utmost importance when debugging issues or evaluating application execution.