O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required