Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
4-37.	To allow each of the DML statements to execute, you must ensure that any exception raised by the statement is trapped and handled. This is done by enclosing each statement in its own anonymous block. Since you just want to keep on processing the DML statements, you can use the “I don’t care” handler as shown here: BEGIN BEGIN UPDATE emp SET empno = 100 WHERE empno > 5000; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN DELETE FROM dept WHERE deptno = 10; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN DELETE FROM emp WHERE deptno = 10; EXCEPTION WHEN OTHERS THEN NULL; END; END;
Q:
4-38.	To handle an exception (that is, an error code) by name, it must be assigned a name. In the STANDARD package Oracle assigns a name to a variety of error codes, such as ORA-01403 (NO_DATA_FOUND). ORA-01014 is not assigned a name, so you have to do it yourself—using the EXCEPTION_INIT pragma, as shown here: DECLARE shutting_down EXCEPTION; PRAGMA EXCEPTION_INIT (shutting_down, -1014); BEGIN ... EXCEPTION WHEN shutting_down THEN DBMS_OUTPUT.PUT_LINE ('Cannot comply. System is shutting down!'); RAISE; END;
Q:
4-39.	Examples (a) and (f) are valid usages of this pragma. To see what is wrong with the other declaration sections, run the pragmaei.sql script and examine the output: SQL> @pragmaei Just twelve months in a year… DECLARE * ERROR at line 1: PLS-00701: illegal ORACLE error number 1843 for PRAGMA EXCEPTION_INIT ...