Chapter 11 Exceptions: Advanced Concepts

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Appendix D.  Answers to Test Your Thinking Sections


1)

Create the following script. Modify the script created in this section in Chapter 10 (Question 1 of the Test Your Thinking section). Raise a user-defined exception with the RAISE_APPLICATION_ERROR statement. Otherwise, display how many students there are in a section. Make sure your program is able to process all sections.

A1:

Answer: Recall the script created in Chapter 10:

 SET SERVEROUTPUT ON SIZE 5000  DECLARE     CURSOR section_cur IS        SELECT section_id          FROM section;     v_total NUMBER;     e_too_many_students EXCEPTION;  BEGIN     FOR section_rec in section_cur LOOP        BEGIN           -- calculate number of students enrolled           SELECT COUNT(*)             INTO v_total             FROM enrollment            WHERE section_id = section_rec.section_id;           IF v_total >= 15 THEN              RAISE e_too_many_students;           ELSE              DBMS_OUTPUT.PUT_LINE ('There are '||v_total||                 ' students for section ID '||                 section_rec.section_id);           END IF;        EXCEPTION           WHEN e_too_many_students THEN              DBMS_OUTPUT.PUT_LINE ('There are too many '||                 'students for '||section_rec.section_id);        END;     END LOOP;  END; 

Next, consider a modified version of this script. All changes are shown in bold letters:

 SET SERVEROUTPUT ON SIZE 5000  DECLARE     CURSOR section_cur IS        SELECT section_id          FROM section;     v_total NUMBER;  BEGIN     FOR section_rec in section_cur LOOP        BEGIN           -- calculate number of students enrolled           SELECT COUNT(*)             INTO v_total             FROM enrollment            WHERE section_id = section_rec.section_id;           IF v_total >= 15 THEN              RAISE_APPLICATION_ERROR (-20000,                 'A section cannot have 15 '||                 'or more students enrolled');           ELSE              DBMS_OUTPUT.PUT_LINE ('There are '||v_total||                 ' students for '||section ID '||                 section_rec.section_id);           END IF;        END;     END LOOP;  END; 

In this version of the script, you are using the RAISE_APPLICATON_ERROR statement to handle the following error condition: If the number of students enrolled for a particular section is equal to or greater than 15, the error is raised. It is important to remember that RAISE_APPLICATION_ERROR statement works with the unnamed user-defined exceptions. Therefore, notice that there is no reference to the exception E_TOO_MANY_STUDENTS anywhere in this script. On the other hand, an error number has been associated with the error message.

When run, this exercise produces the following output (due to the size of the output, only a part of it is shown):

 There are 0 students for section ID 79  There are 1 students for section ID 80  There are 3 students for section ID 81  There are 2 students for section ID 82  There are 2 students for section ID 83  There are 2 students for section ID 84  There are 5 students for section ID 85  There are 6 students for section ID 86  There are 7 students for section ID 87  There are 5 students for section ID 88  There are 12 students for section ID 89    There are 5 students for section ID 155  There are 8 students for section ID 156  PL/SQL procedure successfully completed. 
2)

Create the following script. Try to add a record to the INSTRUCTOR table without providing values for the columns MODIFIED_BY and MODIFIED_DATE. Define an exception and associate it with the Oracle error number, so that the error generated by the INSERT statement is handled.

A2:

Answer: Consider the following script. Notice that there are no exception handlers in this script:

 DECLARE     v_first_name INSTRUCTOR.FIRST_NAME%TYPE :=        '&sv_first_name';     v_last_name INSTRUCTOR.LAST_NAME%TYPE := '&sv_last_name';  BEGIN     INSERT INTO INSTRUCTOR        (instructor_id, first_name, last_name)     VALUES (INSTRUCTOR_ID_SEQ.NEXTVAL, v_first_name,        v_last_name);     COMMIT;  END; 

In this version of the script, you are trying to add a new record to the INSTRUCTOR table. The INSERT statement has only three columns: INSTRUCTOR_ID, FIRST_ NAME, and LAST_NAME. The value for the column INSTRUCTOR_ID is determined from the sequence INSTRUCTOR_ID_SEQ, and the values for the columns FIRST_NAME and LAST_NAME are provided by the user.

When run, this script produces the following error message:

 Enter value for sv_first_name: John  old   2:       '&sv_first_name';  new   2:       'John';  Enter value for sv_last_name: Smith  old   3:       '&sv_last_name';  new   3:       'Smith';  DECLARE  *  ERROR at line 1:  ORA-01400: cannot insert NULL into ("STUDENT".  "INSTRUCTOR"."CREATED_BY")  ORA-06512: at line 5 

This error message states that a NULL value cannot be inserted in to the column CREATED_BY of the INSTRUCTOR table. Therefore, you need to add an exception handler to the script, as follows. All changes are shown in bold letters:

 SET SERVEROUTPUT ON  DECLARE     v_first_name INSTRUCTOR.FIRST_NAME%TYPE :=        '&sv_first_name';     v_last_name INSTRUCTOR.LAST_NAME%TYPE := '&sv_last_name';     e_non_null_value EXCEPTION;     PRAGMA EXCEPTION_INIT(e_non_null_value, -1400);  BEGIN     INSERT INTO INSTRUCTOR        (instructor_id, first_name, last_name)     VALUES        (INSTRUCTOR_ID_SEQ.NEXTVAL, v_first_name, v_last_name);     COMMIT;  EXCEPTION     WHEN e_non_null_value THEN        DBMS_OUTPUT.PUT_LINE ('A NULL value cannot be '||           inserted. Check constraints on the           INSTRUCTOR table.');  END; 

In this version of the script, you declare a new exception called E_NON_NULL_ VALUE. Next, you associate an Oracle error number with this exception. As a result, you are able to add an exception-handling section to trap the error generated by Oracle.

When run, the new version produces the following output:

 Enter value for sv_first_name: John  old   2:       '&sv_first_name';  new   2:       'John';  Enter value for sv_last_name: Smith  old   3:       '&sv_last_name';  new   3:       'Smith';  A NULL value cannot be inserted. Check constraints on the  INSTRUCTOR table.  PL/SQL procedure successfully completed. 
3)

Modify the script created in the previous exercise. Instead of declaring a user-defined exception, add the OTHERS exception handler to the exception-handling section of the block. Then display the error number and the error message on the screen.

A3:

Answer: Your script should look similar to the following. All changes are shown in bold letters.

 SET SERVEROUTPUT ON  DECLARE     v_first_name INSTRUCTOR.FIRST_NAME%TYPE :=        '&sv_first_name';     v_last_name INSTRUCTOR.LAST_NAME%TYPE := '&sv_last_name';  BEGIN     INSERT INTO INSTRUCTOR        (instructor_id, first_name, last_name)     VALUES        (INSTRUCTOR_ID_SEQ.NEXTVAL, v_first_name, v_last_name);     COMMIT;  EXCEPTION     WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE ('Error code: '||SQLCODE);        DBMS_OUTPUT.PUT_LINE ('Error message: '||           SUBSTR(SQLERRM, 1, 200));  END; 

Notice that as long as the OTHERS exception handler is used, there is no need associate an Oracle error number with a user-defined exception. When run, this exercise produces the following output:

 Enter value for sv_first_name: John  old   2:       '&sv_first_name';  new   2:       'John';  Enter value for sv_last_name: Smith  old   3:       '&sv_last_name';  new   3:       'Smith';  Error code: -1400  Error message: ORA-01400: cannot insert NULL into  ("STUDENT"."INSTRUCTOR"."CREATED_BY")  PL/SQL procedure successfully completed. 


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net