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.
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 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 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 output shown below (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. Answer: Consider the following script. Notice 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;
Notice, 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
The error message generated above 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 shown below. 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 output shown below:
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.
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, as long as OTHERS exception handler is used there is no need associate an Oracle error number with user-defined exception. When run, this exercise produces the output shown below:
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.