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. |