Chapter 11 Exceptions: Advanced Concepts | Oracle PL[s]SQL by Example

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.

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.

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.