Oracle7 Server Application Developer's Guide

Contents Index Home Previous Next

Error Handling

Oracle allows user-defined errors in PL/SQL code to be handled so that user-specified error numbers and messages are returned to the client application. Once received, the client application can handle the error based on the user-specified error number and message returned by Oracle.

User-specified error messages are returned using the RAISE_APPLICATION_ERROR procedure:

RAISE_APPLICATION_ERROR(error_number, 'text', keep_error_stack)

This procedure terminates procedure execution, rolls back any effects of the procedure, and returns a user-specified error number and message (unless the error is trapped by an exception handler). ERROR_NUMBER must be in the range of -20000 to -20999. Error number -20000 should be used as a generic number for messages where it is important to relay information to the user, but having a unique error number is not required. TEXT must be a character expression, 2 Kbytes or less (longer messages are ignored). KEEP_ERROR_STACK can be TRUE, if you want to add the error to any already on the stack, or FALSE, if you want to replace the existing errors. By default, this option is FALSE.

Attention: Some of the Oracle-supplied packages, such as DBMS_OUTPUT, DBMS_DESCRIBE, and DBMS_ALERT, use application error numbers in the range -20000 to -20005. See the descriptions of these pacakges for more information.

The RAISE_APPLICATION_ERROR procedure is often used in exception handlers or in the logic of PL/SQL code. For example, the following exception handler selects the string for the associated user-defined error message and calls the RAISE_APPLICATION_ERROR procedure:

...
WHEN NO_DATA_FOUND THEN
   SELECT error_string INTO message
   FROM error_table,
   V$NLS_PARAMETERS V
   WHERE error_number = -20101 AND LANG = v.value AND
      v.name = "NLS_LANGUAGE";
   raise_application_error(-20101, message);
...

Several examples earlier in this chapter also demonstrate the use of the RAISE_APPLICATION_ERROR procedure. The next section has an example of passing a user-specified error number from a trigger to a procedure. For information on exception handling when calling remote procedures, see [*].

Declaring Exceptions and Exception Handling Routines

User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application. When an exception is raised (signaled), the normal execution of the PL/SQL block stops and a routine called an exception handler is invoked. Specific exception handlers can be written to handle any internal or user-defined exception.

Application code can check for a condition that requires special attention using an IF statement. If there is an error condition, two options are available:

You can also define an exception handler to handle user-specified error messages. For example, Figure 7 - 2 illustrates

Declare a user-defined exception in a procedure or package body (private exceptions) or in the specification of a package (public exceptions). Define an exception handler in the body of a procedure (standalone or package).

Figure 7 - 2. Exceptions and User-Defined Errors

Unhandled Exceptions

In database PL/SQL program units, an unhandled user-error condition or internal error condition that is not trapped by an appropriate exception handler causes the implicit rollback of the program unit. If the program unit includes a COMMIT statement before the point at which the unhandled exception is observed, the implicit rollback of the program unit can only be completed back to the previous commit.

Additionally, unhandled exceptions in database-stored PL/SQL program units propagate back to client-side applications that call the containing program unit. In such an application, only the application program unit call is rolled back (not the entire application program unit) because it is submitted to the database as a SQL statement.

If unhandled exceptions in database PL/SQL program units are propagated back to database applications, the database PL/SQL code should be modified to handle the exceptions. Your application can also trap for unhandled exceptions when calling database program units and handle such errors appropriately. For more information, see ``Handling Errors in Remote Procedures.''

Handling Errors in Distributed Queries

You can use a trigger or stored procedure to create a distributed query. This distributed query is decomposed by the local Oracle into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

If a portion of a distributed statement fails, for example, due to an integrity constraint violation, Oracle returns error number ORA-02055. Subsequent statements or procedure calls return error number ORA-02067 until a rollback or rollback to savepoint is issued.

You should design your application to check for any returned error messages that indicate that a portion of the distributed update has failed. If you detect a failure, you should rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.

Handling Errors in Remote Procedures

When a procedure is executed locally or at a remote location, four types of exceptions can occur:

When using local procedures, all of these messages can be trapped by writing an exception handler, such as shown in the following example:

EXCEPTION
   WHEN ZERO_DIVIDE THEN
   /* ...handle the exception */

Notice that the WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:

DECLARE
    ...
    null_salary EXCEPTION;
    PRAGMA EXCEPTION_INIT(null_salary, -20101);
BEGIN
    ...
    RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
    ...
EXCEPTION
    WHEN null_salary THEN
        ...

When calling a remote procedure, exceptions are also handled by creating a local exception handler. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local procedure, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.

Compile Time Errors

When you use SQL*Plus to submit PL/SQL code, and the code contains errors, you receive notification that compilation errors have occurred, but no immediate indication of what the errors are. For example, if you submit a standalone (or stored) procedure PROC1 in the file proc1.sql as follows:

SVRMGR> @proc1

and there are one or more errors in the code, you receive a notice such as

MGR-00072: Warning: Procedure PROC1 created with compilation errors

In this case, use the SHOW ERRORS command in SQL*Plus to get a list of the errors that were found. SHOW ERRORS with no argument lists the errors from the most recent compilation. You can qualify SHOW ERRORS using the name of a procedure, function, package, or package body:

SQL> SHOW ERRORS PROC1
SQL> SHOW ERRORS PROCEDURE PROC1

See the SQL*Plus User's Guide and Reference for complete information about the SHOW ERRORS command.

Attention: Before issuing the SHOW ERRORS command, use the SET CHARWIDTH command to get long lines on output. For example:

SET CHARWIDTH 132

is usually a good choice.

For example, assume you want to create a simple procedure that deletes records from the employee table using SQL*Plus:

CREATE PROCEDURE fire_emp(emp_id NUMBER) AS
   BEGIN
      DELETE FROM emp WHER empno = emp_id;
   END
/

Notice that the CREATE PROCEDURE statement has two errors: the DELETE statement has an error (the 'E' is absent from WHERE) and the semicolon is missing after END.

After the CREATE PROCEDURE statement is issued and an error is returned, a SHOW ERRORS statement would return the following lines:

SHOW ERRORS;

ERRORS FOR PROCEDURE FIRE_EMP:
LINE/COL       ERROR
-------------- --------------------------------------------
3/24   PL/SQL-00103: Encountered the symbol "EMPNO" wh. . .
5/0    PL/SQL-00103: Encountered the symbol "END" when . . .
2 rows selected.

Notice that each line and column number where errors were found is listed by the SHOW ERRORS command.

Alternatively, you can query the following data dictionary views to list errors when using any tool or application:

The error text associated with the compilation of a procedure is updated when the procedure is replaced, and deleted when the procedure is dropped.

Original source code can be retrieved from the data dictionary using the following views: ALL_SOURCE, USER_SOURCE, and DBA_SOURCE. See the Oracle7 Server Reference manual for more information about these data dictionary views.

Debugging

You can debug stored procedures and triggers using the DBMS_OUTPUT supplied package. You put PUT and PUT_LINE statements in your code to output the value of variables and expressions to your terminal. See the section ``Output from Stored Procedures and Triggers'' [*] for more information about the DBMS_OUTPUT package.

A more convenient way to debug, if your platform supports it, is to use the Oracle Procedure Builder, which is part of the Oracle Developer/2000 tool set. Procedure Builder lets you execute PL/SQL procedures and triggers in a controlled debugging environment, and you can set breakpoints, list the values of variables, and perform other debugging tasks. See the Oracle Procedure Builder Developer's Guide for more information.

Contents Index Home Previous Next