Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from 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
  1. Just twelve months in a year…

  2. DECLARE * ERROR at line 1: PLS-00701: illegal ORACLE error number 1843 for PRAGMA EXCEPTION_INIT ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page