| Lab Objective After this Lab, you will be able to: Often your programs need to handle an Oracle error having a particular number associated with it, but no name by which it can be referenced. As a result, you are unable to write a handler to trap this error. In a case like this, you can use a construct called pragma. A pragma is a special instruction to the PL/SQL compiler. It is important to note that pragmas are processed at the time of the compilation. The EXCEPTION_INIT pragma allows you to associate an Oracle error number with a name of a user-defined error. Once you associate an error name with an Oracle error number, you can reference the error and write a handler for it. The EXCEPTION_INIT pragma appears in the declaration section of a block as shown: DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT(exception_name, error_code); Notice that the declaration of the user-defined exception appears before the EXCEPTION_INIT pragma where it is used. The EXCEPTION_INIT pragma has two parameters: exception_name and error_code. The exception_name is the name of your exception, and the error_code is the number of the Oracle error you want to associate with your exception. Consider the following:  FOR EXAMPLE DECLARE v_zip ZIPCODE.ZIP%TYPE := '&sv_zip'; BEGIN DELETE FROM zipcode WHERE zip = v_zip; DBMS_OUTPUT.PUT_LINE ('Zip '||v_zip|| ' has been deleted'); COMMIT; END; In this example, the record corresponding to the value of zipcode provided by a user is deleted from the ZIPCODE table. Next, the message that a specific zipcode has been deleted is displayed on the screen. Compare the results running this example entering 06870 for the value of v_zip. The example produces the following output: Enter value for sv_zip: 06870 old 2: v_zip ZIPCODE.ZIP%TYPE := '&sv_zip'; new 2: v_zip ZIPCODE.ZIP%TYPE := '06870'; DECLARE * ERROR at line 1: ORA-02292: integrity constraint (STUDENT.STU_ZIP_FK) violated - child record found ORA-06512: at line 4 The error message generated by this example occurs because you are trying to delete a record from the ZIPCODE table while its child records exist in the STUDENT table, thus violating the referential integrity constraint STU_ZIP_FK. In other words, there is a record with a foreign key (STU_ZIP_FK) in the STUDENT table (child table) that references a record in the ZIPCODE table (parent table). Notice that this error has Oracle error number ORA-02292 assigned to it, but it does not have a name. As a result, you need to associate this error number with a user-defined exception, so you can handle this error in the script. Contrast the example if you modify it as follows (all changes are shown in bold letters): FOR EXAMPLE DECLARE v_zip ZIPCODE.ZIP%TYPE := '&sv_zip'; e_child_exists EXCEPTION; PRAGMA EXCEPTION_INIT(e_child_exists, -2292); BEGIN DELETE FROM zipcode WHERE zip = v_zip; DBMS_OUTPUT.PUT_LINE ('Zip '||v_zip|| ' has been deleted'); COMMIT; EXCEPTION WHEN e_child_exists THEN DBMS_OUTPUT.PUT_LINE ('Delete students for this '|| 'zipcode first'); END; In this example, you declare the exception e_child_exists. Then you associate the exception with the error number -2292. It is important to note you do not use ORA-02292 in the EXCEPTION_INIT pragma. Next, you add the exception-handling section to the PL/SQL block, so you trap this error. Notice that even though the exception e_child_exists is user-defined, you do not use the RAISE statement, as you saw in Chapter 10. Why do you think you don't use the RAISE statement? When you run this example using the same value of zipcode, the following output is produced: Enter value for sv_zip: 06870 old 2: v_zip ZIPCODE.ZIP%TYPE := '&sv_zip'; new 2: v_zip ZIPCODE.ZIP%TYPE := '06870'; Delete students for this zipcode first PL/SQL procedure successfully completed. Notice that this output contains a new error message displayed by the DBMS_OUTPUT.PUT_LINE statement. This version of the output is more descriptive than the previous version. Remember that the user of the program probably does not know about the referential integrity constraints existing in the database. Therefore, the EXCEPTION_INIT pragma improves the readability of your error-handling interface. If the need arises, you can use multiple EXCEPTION_INIT pragmas in your program. Lab 11.2 Exercises 11.2.1 Use EXCEPTION_INIT Pragma In this exercise, you insert a record in the COURSE table. The original PL/SQL script does not contain any exception handlers, so you are asked to define an exception and add the EXCEPTION_INIT pragma. Create the following PL/SQL script: -- ch11_2a.sql, version 1.0 SET SERVEROUTPUT ON BEGIN INSERT INTO course (course_no, description, created_by, created_date) VALUES (COURSE_NO_SEQ.NEXTVAL, 'TEST COURSE', USER, SYSDATE); COMMIT; DBMS_OUTPUT.PUT_LINE ('One course has been added'); END; Execute the script, and then answer the following questions: | a) | What output is printed on the screen? | | b) | Explain why the script does not execute successfully. | | c) | Add a user-defined exception to the script, so that the error generated by the INSERT statement is handled. | | d) | Run the new version of the script. Explain the output produced by the new version of the script. |
Lab 11.2 Exercise Answers This section gives you some suggested answers to the questions in Lab 11.2, 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.2.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-02290: check constraint (STUDENT.CRSE_MODIFIED_DATE_NNULL) violated ORA-06512: at line 2 | | b) | Explain why the script does not execute successfully. | | A2: | Answer: The script does not execute successfully because a NULL is inserted for the MODIFIED_BY and MODIFIED_DATE columns. The MODIFED_BY and MODIFIED_DATE columns have check constraints defined on them. These constraints can be viewed by querying one of the data dictionary tables. The data dictionary comprises tables owned by the user SYS. These tables provide the database with information that it uses to manage itself. Consider the following SELECT statement against one of Oracle's data dictionary tables, USER_CONSTRAINTS. This table contains information on various constraints defined on each table of the STUDENT schema. SELECT constraint_name, search_condition FROM user_constraints WHERE table_name = 'COURSE'; CONSTRAINT_NAME SEARCH_CONDITION ------------------------ --------------------------- CRSE_CREATED_DATE_NNULL "CREATED_DATE" IS NOT NULL CRSE_MODIFIED_BY_NNULL "MODIFIED_BY" IS NOT NULL CRSE_MODIFIED_DATE_NNULL "MODIFIED_DATE" IS NOT NULL CRSE_DESCRIPTION_NNULL "DESCRIPTION" IS NOT NULL CRSE_COURSE_NO_NNULL "COURSE_NO" IS NOT NULL CRSE_CREATED_BY_NNULL "CREATED_BY" IS NOT NULL CRSE_PK CRSE_CRSE_FK 8 rows selected. Notice that the last two rows refer to the primary and foreign key constraints, so there are no search conditions specified. Based on the results produced by the preceding SELECT statement, there are six columns having a NOT NULL constraint. However, the INSERT statement INSERT INTO course (course_no, description, created_by, created_date) VALUES (COURSE_NO_SEQ.NEXTVAL, 'TEST COURSE',USER, SYSDATE); has only four columns having NOT NULL constraints. The columns MODIFIED_BY and MODIFIED_DATE are not included in the INSERT statement. Any column of a table not listed in the INSERT statement has NULL assigned to it when a new record is added to the table. If a column has a NOT NULL constraint and is not listed in the INSERT statement, the INSERT statement fails and causes an error. | | c) | Add a user-defined exception to the script, so that the error generated by the INSERT statement is handled. | | A3: | Answer: Your script should look similar to the script shown. All changes are shown in bold letters. -- ch11_2b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE e_constraint_violation EXCEPTION; PRAGMA EXCEPTION_INIT(e_constraint_violation, -2290); BEGIN INSERT INTO course (course_no, description, created_by, created_date) VALUES (COURSE_NO_SEQ.NEXTVAL, 'TEST COURSE', USER, SYSDATE); COMMIT; DBMS_OUTPUT.PUT_LINE ('One course has been added'); EXCEPTION WHEN e_constraint_violation THEN DBMS_OUTPUT.PUT_LINE ('INSERT statement is '|| 'violating a constraint'); END; In this script, you declared the e_constraint_violation exception. Then, using the EXCEPTION_INIT pragma to associate the exception with the Oracle error number ORA-02290, the handler is written for the new exception e_constraint_violation. | | d) | Run the new version of the script. Explain the output produced by the new version of the script. | | A4: | Answer: Your output should look similar to the following: INSERT statement is violating a constraint PL/SQL procedure successfully completed. Once you define an exception and associate an Oracle error number with it, you can write an exception handler for it. As a result, as soon as the INSERT statement causes an error, control of the execution is transferred to the exception-handling section of the block. Then, the message "INSERT statement . . ." is displayed on the screen. Notice that once an exception is raised, the execution of the program does not halt. The script completes successfully. |
Lab 11.2 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.2. | 1) | A pragma is _____ a special procedure provided by Oracle. _____ a special instruction to the compiler.
| | 2) | A pragma is processed during _____ runtime. _____ compile time.
| | 3) | The EXCEPTION_INIT pragma associates a _____ built-in exception with a user-defined error number. _____ user-defined exception with a user-defined error number. _____ user-defined exception with an Oracle error number.
| | 4) | The EXCEPTION_INIT pragma needs which of the following parameters? _____ error_number only _____ error_name only _____ error_name and error_number
| | 5) | Which of the following is a valid error_number parameter? _____ ORA-02292 _____ 2292 _____ -2292
|
|
