After completing this lab, you will be able to
- Use SQLCODE and SQLERRM
Oracle gives you two built-in functions, SQLCODE and SQLERRM, 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.
v_zip VARCHAR2(5) := ‘&sv_zip’;
SELECT city, state
INTO v_city, v_state
WHERE zip = v_zip;
DBMS_OUTPUT.PUT_LINE (v_city||‘, ‘||v_state);
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);
Notice that this output is more informative than the output produced by the previous version of the example, because it displays the error message. As soon as you know which runtime error has occurred in your program, you can take steps to prevent this error’s recurrence. Generally, the SQLCODE function returns a negative number for an error number. However, there are a few exceptions:
- When SQLCODE is referenced outside the exception section, it returns 0 for the error code. The value of 0 means successful completion.
- When SQLCODE is used with the user-defined exception, it returns +1 for the error code. SQLCODE 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 SQLCODE. However, you can provide the error number yourself if such a need arises. Consider the following example:
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));
In this example, SQLCODE and SQLERRM are used in the executable section of the PL/SQL block. The SQLERRM function accepts the value of the SQLCODE in the second DBMS_OUTPUT.PUT_LINE statement. In the following DBMS_OUPUT.PUT_LINE statements, SQLERRM 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:
PL/SQL procedure successfully completed.
The first DBMS_OUTPUT.PUT_LINE statement displays the value of the SQLCODE function. Because no exception is raised, it returns 0. Next, SQLERRM accepts as a parameter the value returned by the SQLCODE function. This function returns the message ORA-0000: normal, … . Next, SQLERRM accepts 100 as its parameter and returns ORA-01403: no data found. Notice that when SQLERRM accepts 200 as its parameter, it cannot find an Oracle exception that corresponds to the error number 200. Finally, when SQLERRM accepts –20,000 as its parameter, no error message is returned. Remember that –20,000 is an error number that can be associated with a named user-defined exception.