| Lab Objective After this Lab, you will be able to: In Chapter 7, you learned about the Oracle exception OTHERS. You will recall that all Oracle errors can be trapped with the help of the OTHERS exception handler. Consider the following example.  FOR EXAMPLE 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 "07458" is entered for the value of zipcode, this example produces the following output: Enter value for sv_zip: 07458 old 2: v_zip VARCHAR2(5) := '&sv_zip'; new 2: v_zip VARCHAR2(5) := '07458'; An error has occurred PL/SQL procedure successfully completed. This output informs you that an error has occurred at runtime. However, 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, or maybe there is a datatype mismatch caused by the SELECT INTO statement. As you can see, even though this is a simple example, there are a number of possible runtime errors that can occur. Of course, you cannot always know all of the possible runtime errors that may occur when a program is running. Therefore, it is a good practice to have the OTHERS exception handler in your script. To improve the error-handling interface of your program, Oracle provides you with 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. Consider what happens if you modify the preceding by adding the SQLCODE and SQLERRM functions as follows (all changes are shown in bold letters): FOR EXAMPLE 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 example produces the output shown: Enter value for sv_zip: 07458 old 2: v_zip VARCHAR2(5) := '&sv_zip'; new 2: v_zip VARCHAR2(5) := '07458'; Error code: -6502 Error message: ORA-06502: PL/SQL: numeric or value error PL/SQL procedure successfully completed. In this example, you declare two variables: v_err_code and v_err_msg. Then, in the exception-handling section of the block, you assign SQLCODE to the variable v_err_code, and SQLERRM to the variable v_err_msg. Next, you use the DBMS_OUTPUT.PUT_LINE statements to display the error number and the error message on the screen. 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 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: FOR EXAMPLE 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, 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, the SQLERRM accepts the values of 100, 200, and -20,000 respectively. When executed, this example produces the output shown: 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. Since there is no exception raised, it returns 0. Next, the value returned by the SQLCODE function is accepted as a parameter by SQLERRM. This function returns the message "ORA-0000: normal, . . . ." Next, SQLERRM accepts 100 as its parameter and returns "ORA-01402: no data . . . ." Notice that when the SQLERRM 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 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. Lab 11.3 Exercises 11.3.1 Use SQLCODE and SQLERRM In this exercise, you add a new record to the ZIPCODE table. The original PL/SQL script does not contain any exception handlers. You are asked to add an exception-handling section to this script. Create the following PL/SQL script: -- ch11_3a.sql, version 1.0 SET SERVEROUTPUT ON BEGIN INSERT INTO ZIPCODE (zip, city, state, created_by, created_date, modified_by, modified_date) VALUES ( '10027', 'NEW YORK', 'NY', USER, SYSDATE, USER, SYSDATE); COMMIT; END; Execute the script and answer the following questions: | a) | What output is printed on the screen? | | b) | Modify the script so that the script completes successfully, and the error number and message are displayed on the screen. | | c) | Run the new version of the script. Explain the output produced by the new version of the script. |
Lab 11.3 Exercise Answers This section gives you some suggested answers to the questions in Lab 11.3, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with. 11.3.1 Answers| a) | What output is printed on the screen? | | A1: | Answer: Your output should look like the following: BEGIN * ERROR at line 1: ORA-00001: unique constraint (STUDENT.ZIP_PK) violated ORA-06512: at line 2 The INSERT statement INSERT INTO ZIPCODE (zip, city, state, created_by, created_date, modified_by, modified_date) VALUES ('10027', 'NEW YORK', 'NY', USER, SYSDATE, USER, SYSDATE); causes an error because a record with zipcode 10027 already exists in the ZIPCODE table. Column ZIP of the ZIPCODE table has a primary key constraint defined on it. Therefore, when you try to insert another record with the value of ZIP already existing in the ZIPCODE table, the error message "ORA-00001: unique constraint . . ." is generated. | | b) | Modify the script so that the script completes successfully, and the error number and message are displayed on the screen. | | A2: | Answer: Your script should resemble the script shown. All changes are shown in bold letters. -- ch11_3b.sql, version 2.0 SET SERVEROUTPUT ON BEGIN INSERT INTO ZIPCODE (zip, city, state, created_by, created_date, modified_by, modified_date) VALUES ('10027', 'NEW YORK', 'NY', USER, SYSDATE, USER, SYSDATE); COMMIT; EXCEPTION WHEN OTHERS THEN DECLARE v_err_code NUMBER := SQLCODE; v_err_msg VARCHAR2(100) := SUBSTR(SQLERRM, 1, 100); BEGIN DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code); DBMS_OUTPUT.PUT_LINE ('Error message: '|| v_err_msg); END; END; In this script, you add an exception-handling section with the OTHERS exception handler. Notice that two variables v_err_code and v_err_msg, are declared, in the exception-handling section of the block, adding an inner PL/SQL block. | | c) | Run the new version of the script. Explain the output produced by the new version of the script. | | A3: | Answer: Your output should look similar to the following: Error code: -1 Error message: ORA-00001: unique constraint (STUDENT.ZIP_PK) violated PL/SQL procedure successfully completed. Because the INSERT statement causes an error, control is transferred to the OTHERS exception handler. The SQLCODE function returns -1, and the SQLERRM function returns the text of the error corresponding to the error code -1. Once the exception-handling section completes its execution, control is passed to the host environment. |
Lab 11.3 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 11.3. | 1) | The SQLCODE function returns an Oracle error number. _____ True _____ False
| | 2) | The SQLERRM function returns the error text corresponding to a specific error number. _____ True _____ False
| | 3) | When the SQLERRM function cannot return an error message corresponding to a particular error number, which of the following occurs? _____ SQLERRM causes an error. _____ SQLERRM does not return anything. _____ SQLERRM returns "non-ORACLE exception" message.
| | 4) | What is the maximum length of the error text returned by the SQLERRM function? _____ 450 bytes _____ 550 bytes _____ 512 bytes
| | 5) | The SQLCODE function always returns a negative number. _____ True _____ False
|
|
