Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Chapter 11 Exceptions: Advanced Concepts

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;

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. 

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. 

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.