7 Handling Pl Sql Errors

#
Error Handling
Overview

In PL/SQL a warning or 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 an 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 runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

To handle raised exceptions, you 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.

Advantages of Exceptions

Using exceptions for error handling has several advantages. Without exception handling, every time you issue a command, you must check for execution errors. Exceptions also improve reliability. You need not worry about checking for an error at every point it might occur. Just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled.

Predefined Exceptions

An internal exception is raised explicitly whenever your PL/SQL program violates an ORACLE rule or exceeds a system-dependent limit. Every ORACLE error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common ORACLE errors as exceptions. For example, the predefined exception NO_DATA_FOUND is raised if a SELECT INTO statement returns no rows.

PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown below:

Exception Name ORACLE Error SQLCODE Value

CURSOR_ALREADY OPEN ORA-06511 -6511
DUP_VAL_ON_INDEX ORA-00001 -1
INVALID_CURSOR ORA-01001 -1001
INVALID_NUMBER ORA-01722 -1722
LOGIN_DENIED ORA-01017 -1017
NO_DATA_FOUND ORA-01403 +100
NOT_LOGGED_ON ORA-01012 -1012
PROGRAM_ERROR ORA-06501 -6501
STORAGE_ERROR ORA-06500 -6500
TIMEOUT_ON_RESOURCE ORA-00051 -51
TOO_MANY_ROWS ORA-01422 -1422
VALUE_ERROR ORA-06502 -6502
ZERO_DIVIDE ORA-01476 -1476

* CURSOR_ALREADY_OPEN is raised if you try to OPEN an already open cursor.

* DUP_VAL_ON_INDEX is raised if you try to store duplicate values in a database column that is constrained by a unique index.

* INVALID_CURSOR is raised if you try an illegal cursor operation. For example, if you try to CLOSE an unopened cursor.

* INVALID_NUMBER is raised in a SQL statement if the conversion of a character string to a number fails.

* LOGIN_DENIED is raised if you try logging on to ORACLE with an invalid username/password.

* NO_DATA_FOUND is raised if a SELECT INTO statement returns no rows or if you reference an uninitialized row in a PL/SQL table.

* NOT_LOGGED_ON is raised if your PL/SQL program issues a database call without being logged on to ORACLE.

* PROGRAM_ERROR is raised if PL/SQL has an internal problem.

* STORAGE_ERROR is raised if PL/SQL runs out of memory or if memory is corrupted.

* TIMEOUT_ON_RESOURCE is raised if a timeout occurs while ORACLE is waiting for a resource.

* TOO_MANY_ROWS is raised if a SELECT INTO statement returns more than one row.

* VALUE_ERROR is raised if an arithmetic, conversion, truncation, or constraint error occurs.

* ZERO_DIVIDE is raised if you try to divide a number by zero.

User-defined Exceptions

PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION.

DECLARE
past_due EXCEPTION;
acct_num NUMBER(5);
BEGIN

Exceptions and variable declarations are similar. But remember, an exception is an error condition, not an object. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

* Using EXCEPTION_INIT. To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive, which can be thought of as a parenthetical remark to the compiler.

In PL/SQL, the predefined pragma EXCEPTION_INIT tells the compiler to associate an exception name with an ORACLE error number. That allows you to refer to any internal exception by name and to write a specific handler for it.

You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax

PRAGMA EXCEPTION_INIT(exception_name, ORACLE_error_number);

where exception_name is the name of a previously declared exception.

DECLARE
insufficient_privileges EXCEPTION;
PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);
-----------
— ORACLE returns error number -1031 if, for example
— you try to UPDATE a table for which you have only
— SELECT privileges
-
----------
BEGIN

EXCEPTION
WHEN insufficient_privileges THEN
— handle the error

END;

* Using raise_application_error. A package named DBMS_STANDARD (part of the Procedural Database Extention) provides language facilities that help your application interact with ORACLE. This package includes a procedure named raise_application_error, which lets you issue user-defined error messages from a stored subprogram or database trigger. The calling syntax is

raise_application_error(error_number, error_message);

where error_number is a negative integer in the range -20000..-20999 and error_message is a character string up to 512 bytes in length.

An application can call raise_application_error only from an executing stored subprogram. When called, raise_application_error ends a subprogram, rolls back any database changes it made, and returns a user-defined error message to the application.

PROCEDURE raise_salary (emp_id NUMBER, increase NUMBER) IS
current_salary NUMBER;
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current_salary is NULL THEN
raise_application_error(-20101, 'Salary is missing');
ELSE
UPDATE emp SET sal = current_salary + increase
WHERE empno = emp_id;
END IF;
END raise_salary;

The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. Furthermore, it can use EXCEPTION_INIT to map specific error numbers returned by raise_application_error to exceptions of its own.

DECLARE

null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(null_salary, -20101);

How Exceptions Are Raised

Internal exceptions are raised implicitly by the runtime system, as are user-defined exceptions that you have associated with an ORACLE error number using EXCEPTION_INIT. However, other user-defined exceptions must be raised explicitly by RAISE statements.

* Using RAISE statement. PL/SQL blocks and subprograms should RAISE an exception only when an error makes it undesirable or impossible to finish processing. You can code a RAISE statement for a given exception anywhere within the scope of that exception.

DECLARE
out_of_stock EXCEPTION;
number_on_hand NUMBER(4);
BEGIN

IF number_on_hand < 1 THEN
RAISE out_of_stock;
END IF;

EXCEPTION
WHEN out_of_stock THEN
— handle the error
END;

You can also raise a predefined exception explicitly:

RAISE INVALID_NUMBER;

That way, you can use an exception handler written for the predefined exception to process other errors.

Sometimes, you want to reraise an exception, that is, handle it locally, then pass it to an enclosing block. To reraise an exception, simply place a RAISE statement in the local handler, as shown below

DECLARE
out_of_balance EXCEPTION;
BEGIN

--- beginning of sub-block -----
BEGIN

IF … THEN
RAISE out_of_balance; — raise the exception
END IF;

EXCEPTION
WHEN out_of_balance THEN
— handle the error
RAISE; — reraise the current exception
END;
--- end of sub-block -------
EXCEPTION
WHEN out_of_balance THEN
- handle the error differently

END;

Omitting the exception name in a RAISE statement, which is allowed only in an exception handler, reraises the current exception.

Handling Raised Exceptions

When an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part and control does NOT return to where the exception was raised. In other words, you cannot resume processing where you left off.

The optional OTHERS exception handler, which is always the last handler in a block or subprogram, acts as the handler for all exceptions not named specifically. Use of the OTHERS handler guarantees that no exception will go unhandled.

If you want two or more exceptions to execute the same sequence of statements, list the exception names in the WHEN clause, separating them by the keyword OR. The keyword OTHERS cannot appear in the list of exception names; it must appear by itself.


EXCEPTION
WHEN … THEN
- handle the error differently
WHEN … OR … THEN
- handle the error differently

WHEN OTHERS THEN
- handle the error differently
END;

  1. Using SQLCODE and SQLERRM. You cannot use SQLCODE and SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement.

DECLARE
err_num NUMBER;
err_msg CHAR(100);
BEGIN

WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;

The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to err_msg. SQLCODE and SQLERRM are especially useful in the OTHERS exception handler because they tell you which internal exception was raised.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.