This chapter explains how to handle PL/SQL compile-time warnings and PL/SQL runtime errors. The latter are called exceptions .
The language of warning and error messages depends on the NLS_LANGUAGE parameter. For information about this parameter, see Oracle Database Globalization Support Guide .
If you have problems creating or running PL/SQL code, check the Oracle Database trace files. The DIAGNOSTIC_DEST initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing SHOW PARAMETER DIAGNOSTIC_DEST or query the V$DIAG_INFO view. For more information about diagnostic data, see Oracle Database AdministratorвЂ™s Guide .
12.1 Compile-Time Warnings
While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilationвЂ”for example, using a deprecated PL/SQL feature.
To see warnings (and errors) generated during compilation, either query the static data dictionary view *_ERRORS or, in the SQL*Plus environment, use the command SHOW ERRORS .
The message code of a PL/SQL warning has the form PLW- nnnnn .
Table 12-1 Compile-Time Warning Categories
Condition might cause unexpected action or wrong results.
Aliasing problems with parameters
Condition might cause performance problems.
Passing a VARCHAR2 value to a NUMBER column in an INSERT statement
Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable.
Code that can never run
By setting the compilation parameter PLSQL_WARNINGS , you can:
Enable and disable all warnings, one or more categories of warnings, or specific warnings
Treat specific warnings as errors (so that those conditions must be corrected before you can compile the PL/SQL unit)
You can set the value of PLSQL_WARNINGS for:
Your Oracle database instance
Use the ALTER SYSTEM statement, described in Oracle Database SQL Language Reference .
Use the ALTER SESSION statement, described in Oracle Database SQL Language Reference .
A stored PL/SQL unit
Use an ALTER statement from «ALTER Statements» with its compiler_parameters_clause .
In any of the preceding ALTER statements, you set the value of PLSQL_WARNINGS with this syntax:
For the syntax of value_clause , see Oracle Database Reference .
To display the current value of PLSQL_WARNINGS , query the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS .
Oracle Database Reference for more information about the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS
Oracle Database Error Messages Reference for the message codes of all PL/SQL warnings
Oracle Database Reference for more information about the static data dictionary view *_ERRORS
«PL/SQL Units and Compilation Parameters» for more information about PL/SQL units and compiler parameters
Example 12-1 Setting Value of PLSQL_WARNINGS Compilation Parameter
This example shows several ALTER statements that set the value of PLSQL_WARNINGS .
For the session, enable all warningsвЂ”highly recommended during development:
For the session, enable PERFORMANCE warnings:
For the procedure loc_var , enable PERFORMANCE warnings, and reuse settings:
For the session, enable SEVERE warnings, disable PERFORMANCE warnings, and treat PLW-06002 warnings as errors:
For the session, disable all warnings:
12.1.1 DBMS_WARNING Package
If you are writing PL/SQL units in a development environment that compiles them (such as SQL*Plus), you can display and set the value of PLSQL_WARNINGS by invoking subprograms in the DBMS_WARNING package.
Example 12-2 uses an ALTER SESSION statement to disable all warning messages for the session and then compiles a procedure that has unreachable code. The procedure compiles without warnings. Next, the example enables all warnings for the session by invoking DBMS_WARNING.set_warning_setting_string and displays the value of PLSQL_WARNINGS by invoking DBMS_WARNING.get_warning_setting_string . Finally, the example recompiles the procedure, and the compiler generates a warning about the unreachable code.
Unreachable code could represent a mistake or be intentionally hidden by a debug flag.
DBMS_WARNING subprograms are useful when you are compiling a complex application composed of several nested SQL*Plus scripts, where different subprograms need different PLSQL_WARNINGS settings. With DBMS_WARNING subprograms, you can save the current PLSQL_WARNINGS setting, change the setting to compile a particular set of subprograms, and then restore the setting to its original value.
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_WARNING package
Example 12-2 Displaying and Setting PLSQL_WARNINGS with DBMS_WARNING Subprograms
Disable all warning messages for this session:
With warnings disabled, this procedure compiles with no warnings:
Enable all warning messages for this session:
Check warning setting:
12.2 Overview of Exception Handling
Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources. You cannot anticipate all possible exceptions, but you can write exception handlers that let your program to continue to operate in their presence.
Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers. For example, an exception-handling part could have this syntax:
In the preceding syntax example, ex_name_ n is the name of an exception and statements_ n is one or more statements. (For complete syntax and semantics, see «Exception Handler» .)
When an exception is raised in the executable part of the block, the executable part stops and control transfers to the exception-handling part. If ex_name_1 was raised, then statements_1 run. If either ex_name_2 or ex_name_3 was raised, then statements_2 run. If any other exception was raised, then statements_3 run.
After an exception handler runs, control transfers to the next statement of the enclosing block. If there is no enclosing block, then:
If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation.
If the exception handler is in an anonymous block, then control transfers to the host environment (for example, SQL*Plus)
If an exception is raised in a block that has no exception handler for it, then the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a block has a handler for it or there is no enclosing block (for more information, see «Exception Propagation» ). If there is no handler for the exception, then PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome (for more information, see «Unhandled Exceptions» ).
12.2.1 Exception Categories
The exception categories are:
The runtime system raises internally defined exceptions implicitly (automatically). Examples of internally defined exceptions are ORA-00060 (deadlock detected while waiting for resource) and ORA-27102 (out of memory).
An internally defined exception always has an error code, but does not have a name unless PL/SQL gives it one or you give it one.
A predefined exception is an internally defined exception that PL/SQL has given a name. For example, ORA-06500 (PL/SQL: storage error) has the predefined name STORAGE_ERROR .
You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package. For example, you might declare an exception named insufficient_funds to flag overdrawn bank accounts.
You must raise user-defined exceptions explicitly.
Table 12-2 summarizes the exception categories.
Table 12-2 Exception Categories
Has Error Code
Only if you assign one
Only if you assign one
For a named exception, you can write a specific exception handler, instead of handling it with an OTHERS exception handler. A specific exception handler is more efficient than an OTHERS exception handler, because the latter must invoke a function to determine which exception it is handling. For details, see «Retrieving Error Code and Error Message» .
12.2.2 Advantages of Exception Handlers
Using exception handlers for error-handling makes programs easier to write and understand, and reduces the likelihood of unhandled exceptions.
Without exception handlers, you must check for every possible error, everywhere that it might occur, and then handle it. It is easy to overlook a possible error or a place where it might occur, especially if the error is not immediately detectable (for example, bad data might be undetectable until you use it in a calculation). Error-handling code is scattered throughout the program.
With exception handlers, you need not know every possible error or everywhere that it might occur. You need only include an exception-handling part in each block where errors might occur. In the exception-handling part, you can include exception handlers for both specific and unknown errors. If an error occurs anywhere in the block (including inside a sub-block), then an exception handler handles it. Error-handling code is isolated in the exception-handling parts of the blocks.
In Example 12-3, a procedure uses a single exception handler to handle the predefined exception NO_DATA_FOUND , which can occur in either of two SELECT INTO statements.
If multiple statements use the same exception handler, and you want to know which statement failed, you can use locator variables, as in Example 12-4.
You determine the precision of your error-handling code. You can have a single exception handler for all division-by-zero errors, bad array indexes, and so on. You can also check for errors in a single statement by putting that statement inside a block with its own exception handler.
Example 12-3 Single Exception Handler for Multiple Exceptions
Invoke procedure (there is a DEPARTMENTS table, but it does not have a LAST_NAME column):
Invoke procedure (there is no EMP table):
Example 12-4 Locator Variables for Statements that Share Exception Handler
12.2.3 Guidelines for Avoiding and Handling Exceptions
To make your programs as reliable and safe as possible:
Use both error-checking code and exception handlers.
Use error-checking code wherever bad input data can cause an error. Examples of bad input data are incorrect or null actual parameters and queries that return no rows or more rows than you expect. Test your code with different combinations of bad input data to see what potential errors arise.
Sometimes you can use error-checking code to avoid raising an exception, as in Example 12-7.
Add exception handlers wherever errors can occur.
Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Errors can also arise from problems that are independent of your codeвЂ”for example, disk storage or memory hardware failureвЂ”but your code still must take corrective action.
Design your programs to work when the database is not in the state you expect.
For example, a table you query might have columns added or deleted, or their types might have changed. You can avoid problems by declaring scalar variables with %TYPE qualifiers and record variables to hold query results with %ROWTYPE qualifiers.
Whenever possible, write exception handlers for named exceptions instead of using OTHERS exception handlers.
Learn the names and causes of the predefined exceptions. If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them.
Have your exception handlers output debugging information.
If you store the debugging information in a separate table, do it with an autonomous routine, so that you can commit your debugging information even if you roll back the work that the main subprogram did. For information about autonomous routines, see «AUTONOMOUS_TRANSACTION Pragma» .
For each exception handler, carefully decide whether to have it commit the transaction, roll it back, or let it continue.
Regardless of the severity of the error, you want to leave the database in a consistent state and avoid storing bad data.
Avoid unhandled exceptions by including an OTHERS exception handler at the top level of every PL/SQL program.
Make the last statement in the OTHERS exception handler either RAISE or an invocation of of a subroutine marked with SUPPRESSES_WARNING_6009 pragma. (If you do not follow this practice, and PL/SQL warnings are enabled, then you get PLW-06009.) For information about RAISE or an invocation of the RAISE_APPLICATION_ERROR , see «Raising Exceptions Explicitly» .
12.3 Internally Defined Exceptions
Internally defined exceptions (ORA- n errors) are described in Oracle Database Error Messages Reference . The runtime system raises them implicitly (automatically).
An internally defined exception does not have a name unless either PL/SQL gives it one (see «Predefined Exceptions» ) or you give it one.
If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them. Otherwise, you can handle them only with OTHERS exception handlers.
To give a name to an internally defined exception, do the following in the declarative part of the appropriate anonymous block, subprogram, or package. (To determine the appropriate block, see «Exception Propagation» .)
Declare the name.
An exception name declaration has this syntax:
For semantic information, see «Exception Declaration» .
Associate the name with the error code of the internally defined exception.
For semantic information, see «EXCEPTION_INIT Pragma» .
An internally defined exception with a user-declared name is still an internally defined exception, not a user-defined exception.
Example 12-5 gives the name deadlock_detected to the internally defined exception ORA-00060 (deadlock detected while waiting for resource) and uses the name in an exception handler.
Example 12-5 Naming Internally Defined Exception
12.4 Predefined Exceptions
Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD . The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.
Table 12-3 lists the names and error codes of the predefined exceptions.