PL SQL
EXCEPTIONS
Overview of PL/SQL Runtime Error Handling
• In PL/SQL, an error condition is called an exception. Exceptions can be internally
defined (by the runtime system) or user defined. Examples of internally defined
exceptions include division by zero and out of memory. Some common internal
exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR.
The other internal exceptions can be given names.
• You can define exceptions of your own in the declarative part of any PL/SQL block,
subprogram, or package. For example, you might define an exception
named insufficient funds to flag overdrawn bank accounts. Unlike internal
exceptions, user-defined exceptions must be given names.
• When an error occurs, an exception is raised. That is, normal execution stops and
control transfers to the exception-handling part of your PL/SQL block or
subprogram. Internal exceptions are raised implicitly (automatically) by the run-
time system. User-defined exceptions must be raised explicitly
by RAISE statements, which can also raise predefined exceptions.
• To handle raised exceptions, write separate routines called exception handlers.
After an exception handler runs, the current block stops executing and the
enclosing block resumes with the next statement. If there is no enclosing block,
control returns to the host environment.
• The following example calculates a price-to-earnings ratio for a company. If the
company has zero earnings, the division operation raises the predefined
exception ZERO_DIVIDE, the execution of the block is interrupted, and control is
transferred to the exception handlers. The optional OTHERS handler catches all
exceptions that the block does not name specifically.
Overview of PL/SQL Runtime Error Handling
The last example illustrates exception handling. With some better error
checking, we could have avoided the exception entirely, by substituting a
null for the answer if the denominator was zero
Exception Handling in PL/SQL
• An exception is an error which disrupts the normal flow of program instructions. PL/SQL
provides us the exception block which raises the exception thus helping the programmer to
find out the fault and resolve it.
• There are two types of exceptions defined in PL/SQL
1. User defined exception.
2. System defined exceptions.
Syntax:
WHEN exception THEN statement;
DECLARE
declarations section;
BEGIN
executable command(s);
EXCEPTION
WHEN exception1 THEN
statement1;
WHEN exception2 THEN
statement2;
[WHEN others THEN]
/* default exception handling code */
END;
Note: When other keyword should be used only at the end of the
exception handling block as no exception handling part present
later will get executed as the control will exit from the block after
executing the WHEN OTHERS.
System Defined Exceptions
These exceptions are predefined in PL/SQL which get raised WHEN certain database rule
is violated.
System-defined exceptions are further divided into two categories:
– Named System exceptions.
– Unnamed System exceptions.
– Named system exceptions: They have a predefined name by the system like
ACCESS_INTO_NULL, DUP_VAL_ON_INDEX, LOGIN_DENIED etc.
Exception Handling – Built in Exceptions
Exception Name Meaning
ACCESS_INTO_NULL A program attempts to assign values to the attributes of an uninitialized object.
CASE_NOT_FOUND None of the choices in the WHEN clauses of a CASE statement is selected, and there is
no ELSE clause.
COLLECTION_IS_NULL A program attempts to apply collection methods other than EXISTS to an uninitialized nested
table or varray, or the program attempts to assign values to the elements of an uninitialized
nested table or varray.
CURSOR_ALREADY_OPEN A program attempts to open an already open cursor. A cursor must be closed before it can be
reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program
cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX A program attempts to store duplicate values in a database column that is constrained by a
unique index.
INVALID_CURSOR A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER In a SQL statement, the conversion of a character string into a number fails because the string
does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This
exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not
evaluate to a positive number.
LOGIN_DENIED A program attempts to log on to Oracle with an invalid username or password
NO_DATA_FOUND A SELECT INTO statement returns no rows, or your program references a deleted element in a
nested table or an uninitialized element in an index-by table. Because this exception is used
internally by some SQL functions to signal that they are finished, you should not rely on this
exception being propagated if you raise it within a function that is called as part of a query.
NOT_LOGGED_ON A program issues a database call without being connected to Oracle.
PROGRAM_ERROR PL/SQL has an internal problem.
Exception Handling – Built in Exceptions
Exception Name Meaning
ROWTYPE_MISMATCH The host cursor variable and PL/SQL cursor variable involved in an assignment have
incompatible return types. For example, when an open host cursor variable is passed to a
stored subprogram, the return types of the actual and formal parameters must be
compatible.
SELF_IS_NULL A program attempts to call a MEMBER method, but the instance of the object type has
not been initialized. The built-in parameter SELF points to the object, and is always the
first parameter passed to a MEMBER method.
STORAGE_ERROR PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_CO A program references a nested table or varray element using an index number larger
UNT than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LI A program references a nested table or varray element using an index number (-1 for
MIT example) that is outside the legal range.
SYS_INVALID_ROWID The conversion of a character string into a universal rowid fails because the character
string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE A time-out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS A SELECT INTO statement returns more than one row.
VALUE_ERROR An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when
your program selects a column value into a character variable, if the value is longer than
the declared length of the variable, PL/SQL aborts the assignment and
raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion
of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE A program attempts to divide a number by zero
User-defined Exceptions
• PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be
declared and then raised explicitly, using either a RAISE statement or the
Procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
• The syntax for declaring an exception is − DECLARE my-exception EXCEPTION;
• The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the
exception invalid_id is raised.
DECLARE c_id [Link]%type := &cc_id;
c_name [Link]%type;
c_addr [Link]%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN IF c_id <= 0 THEN RAISE ex_invalid_id;
ELSE SELECT name,
address INTO c_name, c_addr FROM customers WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END IF;
EXCEPTION WHEN ex_invalid_id THEN dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN dbms_output.put_line('No such customer!');
WHEN others THEN dbms_output.put_line('Error!');
END;
When the above code is executed at the SQL prompt, it produces the following result −
Enter value for cc_id: -6 (let's enter a value -6)
old 2: c_id [Link]%type := &cc_id;
new 2: c_id [Link]%type := -6;
ID must be greater than zero!
PL/SQL procedure successfully completed.