Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

Defining Exceptions

Before an exception can be raised or handled, it must be defined. Oracle predefines thousands of exceptions, mostly by assigning numbers and messages to those exceptions. Oracle also assigns names to a relative few of these thousands—the most commonly encountered exceptions.

These names are assigned in the STANDARD package (one of two default packages in PL/SQL), as well as in other built-in packages such as UTL_FILE and DBMS_SQL. The code Oracle uses to define exceptions like NO_DATA_FOUND is the same code that you will write to define or declare your own exceptions. You can do this in two different ways, described in the following sections.

You can also declare your own exceptions by listing the name of the exception you want to raise in your program followed by the keyword EXCEPTION:

    DECLARE
       exception_name EXCEPTION;

The names for exceptions are similar in format to (and “read” just like) Boolean variable names, but they can be referenced in only two ways:

  • In a RAISE statement in the execution section of the program (to raise the exception), as in:

        RAISE invalid_company_id;
  • In the WHEN clauses of the exception section (to handle the raised exception), as in:

        WHEN invalid_company_id THEN
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 PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page