Oracle PL/SQL by Example, Fifth Edition (2015)

---

In Chapter 8, you learned about the Oracle exception OTHERS. All Oracle errors can be trapped with the help of the OTHERS exception handler, as illustrated in the following example:

For Example ch10_4a.sql

DECLARE
v_zip VARCHAR2(5) := '&sv_zip';
v_city VARCHAR2(15);
v_state CHAR(2);
BEGIN
SELECT city, state
INTO v_city, v_state
FROM zipcode
WHERE zip = v_zip;

DBMS_OUTPUT.PUT_LINE (v_city||', '||v_state);

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

When the user enters 07458 for the value of the ZIP code, this example produces the following output:

An error has occurred

This output informs you that an error occurred at runtime, but you do not know what the error is and what caused it. Maybe there is no record in the ZIPCODE table corresponding to the value provided at runtime, maybe there is a data type mismatch caused by the SELECT INTO statement, or maybe the SELECT INTO statement returned more than one row. As you can see, even though this is a simple example, a number of runtime errors might potentially occur.

Of course, you cannot always identify every possible runtime error that might occur when a program is running. Therefore, it is a good practice to include the OTHERS exception handler in your script. To improve the error-handling interface of your program, the Oracle platform provides two built-in functions, SQLCODE and SQLERRM, that can be used with the OTHERS exception handler. The SQLCODE function returns the Oracle error number, and the SQLERRM function returns the error message. The maximum length of a message returned by the SQLERRM function is 512 bytes, which is the maximum length of an Oracle database error message.

Consider what happens if you modify the preceding example by adding the SQLCODE and SQLERRM functions as follows (modifications are highlighted in bold):

For Example ch10_4b.sql

DECLARE
v_zip VARCHAR2(5) := '&sv_zip';
v_city VARCHAR2(15);
v_state CHAR(2);
v_err_code NUMBER;
v_err_msg VARCHAR2(200);
BEGIN
SELECT city, state
INTO v_city, v_state
FROM zipcode
WHERE zip = v_zip;

DBMS_OUTPUT.PUT_LINE (v_city||', '||v_state);

EXCEPTION
WHEN OTHERS
THEN
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code);
DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);
END;

When executed, this version of the example produces the following output:

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

This version of the script includes two new variables: v_err_code and v_err_msg. In the exception-handling section of the block, the value returned by the SQLCODE function is assigned to the variable v_err_code, and the value returned by the SQLERRM function is assigned to the variable v_err_msg. Next, the error number and the error message are displayed on the screen via the DBMS_OUTPUT.PUT_LINE statements.

Notice that this output is more informative than the output produced by the previous version of the example because it displays the error message. Once you know which runtime error has occurred in your program, you can take steps to prevent its recurrence.

Generally, the SQLCODE function returns a negative number for an error number. However, there are a few exceptions:

When the SQLCODE function is referenced outside the exception section, it returns 0 for the error code. The value of 0 means successful completion.

When the SQLCODE function is used with the user-defined exception, it returns +1 for the error code.

The SQLCODE function returns a value of 100 when the NO_DATA_FOUND exception is raised.

The SQLERRM function accepts an error number as a parameter, and it returns an error message corresponding to the error number. Usually, it works with the value returned by the SQLCODE function. However, you can provide the error number yourself if such a need arises. Consider the following example:

For Example ch10_5a.sql

BEGIN
DBMS_OUTPUT.PUT_LINE ('Error code: '||SQLCODE);
DBMS_OUTPUT.PUT_LINE ('Error message1: '||SQLERRM(SQLCODE));
DBMS_OUTPUT.PUT_LINE ('Error message2: '||SQLERRM(100));
DBMS_OUTPUT.PUT_LINE ('Error message3: '||SQLERRM(200));
DBMS_OUTPUT.PUT_LINE ('Error message4: '||SQLERRM(-20000));
END;

In this example, the SQLCODE and SQLERRM functions are used in the executable section of the PL/SQL block. The SQLERRM function accepts the value provided by SQLCODE in the second DBMS_OUTPUT.PUT_LINE statement. In the following DBMS_OUPUT.PUT_LINEstatements, the SQLERRM function accepts the values of 100, 200, and –20,000 respectively. When executed, this example produces the following output:

Error code: 0
Error message1: ORA-0000: normal, successful completion
Error message2: ORA-01403: no data found
Error message3: -200: non-ORACLE exception
Error message4: ORA-20000:

The first DBMS_OUTPUT.PUT_LINE statement displays the value of the SQLCODE function. Because no exception has been raised, it returns 0. Next, the value returned by the SQLCODE function is accepted as a parameter by the SQLERRM function. This function returns the message “ORA-0000: normal, successful completion.” Next, the SQLERRM function accepts 100 as its parameter and returns “ORA-01402: no data....” Notice that when the SQLERRM function accepts 200 as its parameter, it is not able to find an Oracle exception that corresponds to the error number 200. Finally, when the SQLERRM function accepts –20,000 as its parameter, no error message is returned. Recall that –20,000 is an error number that can be associated with a named user-defined exception.

Summary

In this chapter, you have concluded your exploration of error handling and exceptions. You learned how to use the RAISE_APPLICATION_ERROR procedure and the SQLCODE and SQLERMM functions to create meaningful error messages in your code. In addition, you learned about theEXCEPTION_INIT pragma, which enables you to associate a user-defined exception with the Oracle error number.