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
  1. Just twelve months in a year…

  2. DECLARE * ERROR at line 1: PLS-00701: illegal ORACLE error number 1843 for PRAGMA EXCEPTION_INIT ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.