9-4. Determining Which Error Occurred Inside the OTHERS Handler
Problem
Your code is continually failing via an exception, and the OTHERS handler is being invoked. You need to determine the exact cause of the exception so that it can be repaired.
Solution
Code the OTHERS exception handler as indicated by Recipe 9-2, and use the SQLCODE and DBMS_UTILITY.FORMAT_ERROR_STACK functions to return the Oracle error code and message text for the exception that has been raised. The following example demonstrates the usage of these functions, along with the procedure that was used in Recipe 9-3, for obtaining the error code and message when the OTHERS handler is invoked.
CREATE OR replace PROCEDURE salary_increase(emp_id IN NUMBER,
pct_increase IN NUMBER)
AS
salary employees.salary%TYPE;
max_salary jobs.max_salary%TYPE;
invalid_increase EXCEPTION;
error_number NUMBER;
error_message VARCHAR2(1500);
BEGIN
SELECT salary,
max_salary
INTO salary, max_salary
FROM employees,
jobs
WHERE employee_id = emp_id
AND jobs.job_id = employees.employee_id;
IF ( salary + ( salary * pct_increase ) ) <= max_salary THEN
UPDATE employees
SET salary = ( salary + ( salary * pct_increase ) )
WHERE employee_id = emp_id;
dbms_output.Put_line('SUCCESSFUL SALARY INCREASE FOR EMPLOYEE #: '
|| emp_id
|| '. NEW SALARY = '
|| salary + ( salary * pct_increase ));
ELSE
RAISE invalid_increase;
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.Put_line('UNSUCCESSFUL INCREASE, NO EMPLOYEE RECORD FOUND '
|| 'FOR THE '
|| 'GIVEN ID'), WHEN invalid_increase THEN
dbms_output.Put_line('UNSUCCESSFUL INCREASE. YOU CANNOT INCREASE THE '
|| 'EMPLOYEE '
|| 'SALARY BY '
|| pct_increase
|| ' PERCENT...PLEASE ENTER '
|| 'A SMALLER INCREASE AMOUNT TO TRY AGAIN'),
WHEN OTHERS THEN
error_number := SQLCODE;
error_message := DBMS_UTILITY.FORMAT_ERROR_STACK;
dbms_output.Put_line('UNSUCCESSFUL INCREASE. AN UNKNOWN ERROR HAS '
|| 'OCCURRED, '
|| 'PLEASE TRY AGAIN OR CONTACT ADMINISTRATOR'
|| ' Error #: '
|| error_number
|| ' - '
|| error_message);
END;
When this procedure is executed, the following error will be returned:
UNSUCCESSFUL INCREASE. AN UNKNOWN ERROR HAS OCCURRED, PLEASE TRY AGAIN OR CONTACT
ADMINISTRATOR Error #: -1722 - ORA-01722: invalid number
This example intentionally raises an error in order to demonstrate the functionality of these utilities. A reference to the line number that raised the error may also be helpful. To learn more about writing an exception handler that returns line numbers, please see Recipe 9-9.
How It Works
The SQLCODE and DBMS_UTILITY.FORMAT_ERROR_STACK functions provide the means to determine what code and message had caused the last exception that was raised. The SQLCODE function will return the Oracle error number for internal exceptions and +1 for a user-defined exception. The DBMS_UTILITY.FORMAT_ERROR_STACK function will return the Oracle error message for any internal exception that is raised, and it will contain the text User-Defined Exception for any named user exception that is raised. A user-defined exception may receive a custom error number, as you will read about in Recipe 9-9. In such cases, the SQLCODE function will return this custom error number if raised.
To use these functions, you must assign them to a variable because they cannot be called outright. For instance, if you wanted to use the SQLCODE within a CASE statement, you would have to assign the function to a variable first. Once that has been done, you could use the variable that was assigned the SQLCODE in the statement.
Oracle includes DBMS_UTILITY.FORMAT_ERROR_STACK, which can be used to return the error message associated with the current error. DBMS_UTILITY.FORMAT_ERROR_STACK can hold up to 1,899 characters, so there is rarely a need to truncate the message it returns. SQLERRM is a similar function that can be used to return the error message, but it only allows messages up to 512 bytes to be displayed. Oftentimes, SQLERRM messages need to be truncated for display. Oracle recommends using DBMS_UTILITY.FORMAT_ERROR_STACK over SQLERRM because this utility doesn't have such a small message limitation.
However, SQLERRM does have its place, because there are some benefits of using it. A handy feature of SQLERRM is that you can pass an error number to it and retrieve the corresponding error message. Any error number that is passed to SQLERRM should be negative; otherwise, you will receive the message User-defined error. Table 9-2 displays the error number ranges and their corresponding messages using SQLCODE and SQLERRM.
Table 9-2. SQLCODE Return Codes and Meanings
| Code | Description |
| Negative Oracle Error Number | Internal Oracle exception |
| 0 | No exceptions raised |
| +1 | User-defined exception |
| +100 | NO_DATA_FOUND |
| -20000 to -20999 | User-defined error with PRAGMA EXCEPTION_INIT |
Note PRAGMA EXCEPTION_INIT is used to associate an Oracle error number with an exception name.
If you choose to use SQLERRM, the code is not much different from using DBMS_UTILITY.FORMAT_ERROR_STACK, but you will probably need to include some code to truncate the result. The next example demonstrates the same example that was used in the solution to this recipe, but it uses SQLERRM instead of DBMS_UTILITY.FORMAT_ERROR_STACK.
CREATE OR replace PROCEDURE salary_increase(emp_id IN NUMBER,
pct_increase IN NUMBER)
AS
salary employees.salary%TYPE;
max_salary jobs.max_salary%TYPE;
invalid_increase EXCEPTION;
error_number NUMBER;
error_message VARCHAR2(1500);
BEGIN
SELECT salary,
max_salary
INTO salary, max_salary
FROM employees,
jobs
WHERE employee_id = emp_id
AND jobs.job_id = employees.employee_id;
IF ( salary + ( salary * pct_increase ) ) <= max_salary THEN
UPDATE employees
SET salary = ( salary + ( salary * pct_increase ) )
WHERE employee_id = emp_id;
dbms_output.Put_line('SUCCESSFUL SALARY INCREASE FOR EMPLOYEE #: '
|| emp_id
|| '. NEW SALARY = '
|| salary + ( salary * pct_increase ));
ELSE
RAISE invalid_increase;
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.Put_line('UNSUCCESSFUL INCREASE, NO EMPLOYEE RECORD FOUND '
|| 'FOR THE '
|| 'GIVEN ID'), WHEN invalid_increase THEN
dbms_output.Put_line('UNSUCCESSFUL INCREASE. YOU CANNOT INCREASE THE '
|| 'EMPLOYEE '
|| 'SALARY BY '
|| pct_increase
|| ' PERCENT...PLEASE ENTER '
|| 'A SMALLER INCREASE AMOUNT TO TRY AGAIN'),
WHEN OTHERS THEN
error_number := SQLCODE;
error_message := Substr(sqlerrm, 1, 150);
dbms_output.Put_line('UNSUCCESSFUL INCREASE. AN UNKNOWN ERROR HAS OCCURRED, '
|| 'PLEASE TRY AGAIN OR CONTACT ADMINISTRATOR'
|| ' Error #: '
|| error_number
|| ' - '
|| error_message);
END;
There are some other tools that can be used to further diagnose which errors are being raised and even to see the entire stack trace. These tools are further explained within Recipe 9-9. By combining the techniques learned in this recipe with those you will learn about in Recipe 9-9, you are sure to have a better chance of diagnosing your application issues.