Oracle begin exception end

This chapter describes the flexible error trapping and error handling you can use in your PL/SQL programs.

For more information on error-handling and exceptions in PL/SQL, see "PL/SQL Error Handling" in Oracle Database PL/SQL Language Reference .

See the end of this chapter for TimesTen-specific considerations.

The following topics are covered:

Under standing exceptions

This section provides an overview of exceptions in PL/SQL programming, covering the following topics:

About exceptions

An exception is a PL/SQL error that is raised during program execution, either implicitly by TimesTen or explicitly by your program. Handle an exception by trapping it with a handler or propagating it to the calling environment.

For example, if your SELECT statement returns multiple rows, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message. ( ORA messages, originally defined for Oracle Database, are similarly implemented by TimesTen.)

You can handle such exceptions in your PL/SQL block so that your program completes successfully. For example:

Exc eption types

There are three types of exceptions:

Predefined exceptions are error conditions that are defined by PL/SQL.

Non-predefined exceptions include any standard TimesTen errors.

User-defined exceptions are exceptions specific to your application.

In TimesTen, these three types of exceptions are used in the same way as in Oracle Database.

Exception	Description	How to handle
Predefined TimesTen error	One of approximately 20 errors that occur most often in PL/SQL code	You are not required to declare these exceptions. They are predefined by TimesTen. TimesTen implicitly raises the error.
Non-predefined TimesTen error	Any other standard TimesTen error	These must be declared in the declarative section of your application. TimesTen implicitly raises the error and you can use an exception handler to catch the error.
User-defined error	Error defined and raised by the application	These must be declared in the declarative section. The developer raises the exception explicitly.

Trapping exceptions

This section describes how to trap predefined TimesTen errors or user-defined errors.

Trap ping predefined TimesTen errors

Trap a predefined TimesTen error by referencing its predefined name in your exception-handling routine. PL/SQL declares predefined exceptions in the STANDARD package.

Table 4-1 lists predefined exceptions supported by TimesTen, the associated ORA error numbers and SQLCODE values, and descriptions of the exceptions.

Table 4-1 Predefined exceptions

Exception name	Oracle Database error number	SQLCODE	Description

Program attempted to assign values to the attributes of an uninitialized object.

None of the choices in the WHEN clauses of a CASE statement were selected and there is no ELSE clause.

Program attempted to apply collection methods other than EXISTS to an uninitialized nested table or varray, or program attempted to assign values to the elements of an uninitialized nested table or varray.

Program attempted to open an already opened cursor.

Program attempted to insert duplicate values in a column that is constrained by a unique index.

There is an illegal cursor operation.

Conversion of character string to number failed.

Single row SELECT returned no rows or your program referenced a deleted element in a nested table or an uninitialized element in an associative array (index-by table).

PL/SQL has an internal problem.

Host cursor variable and PL/SQL cursor variable involved in an assignment statement have incompatible return types.

PL/SQL ran out of memory or memory was corrupted.

A program referenced a nested table or varray using an index number larger than the number of elements in the collection.

A program referenced a nested table or varray element using an index number that is outside the legal range (for example, -1).

The conversion of a character string into a universal rowid failed because the character string does not represent a ROWID value.

Single row SELECT returned multiple rows.

An arithmetic, conversion, truncation, or size constraint error occurred.

A program attempted to divide a number by zero.

Example 4-1 Using the ZERO_DIVIDE predefined exception

In this example, a PL/SQL program attempts to divide by 0. The ZERO_DIVIDE predefined exception is used to trap the error in an exception-handling routine.

Trap ping user-defined exceptions

You can define your own exceptions in PL/SQL in TimesTen, and you can raise user-defined exceptions explicitly with either the PL/SQL RAISE statement or the RAISE_APPLICATION_ERROR procedure.

Usi ng the RAISE statement

The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler. RAISE statements can raise predefined exceptions, or user-defined exceptions whose names you decide.

Example 4-2 Using RA ISE statement to trap user-defined exception

In this example, the department number 500 does not exist, so no rows are updated in the departments table. The RAISE statement is used to explicitly raise an exception and display an error message, returned by the SQLERRM built-in function, and an error code, returned by the SQLCODE built-in function. Use the RAISE statement by itself within an exception handler to raise the same exception again and propagate it back to the calling environment.

Given the same error condition in TimesTen and Oracle Database, SQLCODE returns the same error code, but SQLERRM does not necessarily return the same error message. This is also noted in "TimesTen error messages and SQL codes".

Us ing the RAISE_APPLICATION_ERROR procedure

Use the RAISE_APPLICATION_ERROR procedure in the executable section or exception section (or both) of your PL/SQL program. TimesTen reports errors to your application so you can avoid returning unhandled exceptions.

Use an error number between -20,000 and -20,999. Specify a character string up to 2,048 bytes for your message.

Example 4-3 Using the RAISE_APPLICATION_ERROR procedure

This example attempts to delete from the employees table where last_name=Patterson . The RAISE_APPLICATION_ERROR procedure raises the error, using error number -20201.

Sho wing errors in ttIsql

You can use the show errors command in ttIsql to see details about errors you encounter in executing anonymous blocks or compiling packages, procedures, or functions. This is shown in Example 4-4.

Example 4-4 ttIsql show errors command

Again consider Example 2-17. Assume the same package specification shown there, which declares the procedures and functions hire_employee , remove_employee , and num_above_salary . But instead of the body definition shown there, consider the following, which defines hire_employee and num_above_salary but not remove_employee :

Attempting this body definition after the original package specification results in the following:

To get more information, run ttIsql and use the command show errors . In this example, show errors provides the following:

Dif ferences in TimesTen: exception handing and error behavior

You should be aware of some error-related behaviors that differ between TimesTen PL/SQL and Oracle Database PL/SQL:

TimesTen PL/SQL tran saction and rollback behavior for unhandled exceptions

TimesTen PL/SQL differs from Oracle Database PL/SQL in a scenario where an application executes PL/SQL in the middle of a transaction, and an unhandled exception occurs during execution of the PL/SQL. Oracle Database rolls back to the beginning of the anonymous block. TimesTen does not roll back.

An application should always handle any exception that results from execution of a PL/SQL block, as in the following example, run with autocommit disabled:

The second INSERT fails because values must be unique, so an exception occurs and the program performs a rollback. Running this in TimesTen results in the following.

The result is equivalent in Oracle Database, with the SELECT results showing no rows.

Now consider a TimesTen example where the exception is not handled, again run with autocommit disabled:

In TimesTen, the SELECT query indicates execution of the first two inserts:

If you execute this in Oracle Database, there is a rollback to the beginning of the PL/SQL block, so the results of the SELECT indicate execution of only the first insert:

If there is an unhandled exception in a PL/SQL block, TimesTen leaves the transaction open only to allow the application to assess its state and determine appropriate action.

An application in TimesTen should not execute a PL/SQL block while there are uncommitted changes in the current transaction, unless those changes together with the PL/SQL operations really do constitute a single logical unit of work and the application is able to determine appropriate action. Such action, for example, might consist of a rollback to the beginning of the transaction.

If autocommit is enabled and an unhandled exception occurs in TimesTen, the entire transaction is rolled back.

TimesTen er ror messages and SQL codes

Given the same error condition, TimesTen does not guarantee that the error message returned by TimesTen is the same as the message returned by Oracle Database, although the SQL code is the same. Therefore, the information returned by the SQLERRM function may be different, but that returned by the SQLCODE function is the same.

For further information:

Example 4-2 uses SQLERRM and SQLCODE .

Refer to "Warnings and Errors" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps for information about specific TimesTen error messages.

Refer to "SQLERRM Function" and "SQLCODE Function" in Oracle Database PL/SQL Language Reference for general information.

W arnings not visible in PL/SQL

Oracle Database does not have the concept of runtime warnings, so Oracle Database PL/SQL does not support warnings.

TimesTen does have the concept of warnings, but because the TimesTen PL/SQL implementation is based on the Oracle Database PL/SQL implementation, TimesTen PL/SQL does not support warnings.

As a result, in TimesTen you could execute a SQL statement and see a resulting warning, but if you execute the same statement through PL/SQL you would not see the warning.

Unsup ported predefined errors

"Trapping predefined TimesTen errors" lists predefined exceptions supported by TimesTen, the associated ORA error numbers and SQLCODE values, and descriptions of the exceptions.

Table 4-2 notes predefined exceptions that are not supported by TimesTen.

Table 4-2 Predefined exceptions not supported by TimesTen

Exception name	Oracle Database error number	SQLCODE	Description

User name or password is invalid.

Program issued a database call without being connected to the database.

Program attempted to invoke a MEMBER method, but the object was not initialized.

Timeout occurred while the database was waiting for a resource.

Possibility of runtime errors after clean compile (use of Oracle Database SQL parser)

The TimesTen PL/SQL implementation uses the Oracle Database SQL parser in compiling PL/SQL programs. (This is discussed in "PL/SQL in TimesTen versus PL/SQL in Oracle Database".) As a result, if your program uses Oracle Database syntax or built-in procedures that are not supported by TimesTen, the issue is not discovered during compilation. A runtime error occurs during program execution, however.

Use of TimesTen expressions at runtime

TimesTen SQL includes several constructs that are not present in Oracle Database SQL. The PL/SQL language does not include these constructs. To use TimesTen-specific SQL from PL/SQL, execute the SQL statements using the EXECUTE IMMEDIATE statement. This avoids compilation errors.

For lists of TimesTen-specific SQL and expressions, see "Compatibility Between TimesTen and Oracle Databases" in Oracle TimesTen Application-Tier Database Cache User’s Guide .

For more information about EXECUTE IMMEDIATE , refer to "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)".

There is nothing more exhilarating than to be shot at without result. —Winston Churchill

Run-time errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program.

With many programming languages, unless you disable error checking, a run-time error such as stack overflow or division by zero stops normal processing and returns control to the operating system. With PL/SQL, a mechanism called exception handling lets you "bulletproof" your program so that it can continue operating in the presence of errors.

This chapter discusses the following topics:

Overview of PL/SQL Error Handling

In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time 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, 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.

In the example below, you calculate and store a price-to-earnings ratio for a company with ticker symbol XYZ. If the company has zero earnings, the predefined exception ZERO_DIVIDE is raised. This stops normal execution of the block and transfers control to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically.

The last example illustrates exception handling, not the effective use of INSERT statements. For example, a better way to do the insert follows:

In this example, a subquery supplies values to the INSERT statement. If earnings are zero, the function DECODE returns a null. Otherwise, DECODE returns the price-to-earnings ratio.

Advantages of PL/SQL Exceptions

Using exceptions for error handling has several advantages. Without exception handling, every time you issue a command, you must check for execution errors:

Error processing is not clearly separated from normal processing; nor is it robust. If you neglect to code a check, the error goes undetected and is likely to cause other, seemingly unrelated errors.

With exceptions, you can handle errors conveniently without the need to code multiple checks, as follows:

Exceptions improve readability by letting you isolate error-handling routines. The primary algorithm is not obscured by error recovery algorithms. 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 PL/SQL Exceptions

An internal exception is raised implicitly 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, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.

To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.

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 in the following list:

Базы данных

Это учебное пособие объясняет, как использовать встроенные исключительные ситуации в Oracle/PLSQL c синтаксисом и примерами.

Описание

Встроенные исключительные ситуации это исключительные ситуации, которые имеют определенные имена в PL/SQL. Они определены в стандартном пакете в PL/SQL и не могут быть определены программистом.
Oracle имеет стандартный набор встроенных исключительных ситуаций:

Исключительные ситуации ORACLE	Ошибки Oracle	Пояснения
DUP_VAL_ON_INDEX	ORA-00001	Вы пытались выполнить операторы insert или update поля, изменение значения которого нарушит ограничение уникальности поля.
TIMEOUT_ON_RESOURCE	ORA-00051	Возбуждается при возникновении таймаута, когда ORACLE ожидает ресурса.
TRANSACTION_BACKED_OUT	ORA-00061	Откат удаленной части транзакции.
INVALID_CURSOR	ORA-01001	Вы пытаетесь сослаться на курсор, который еще не существует. Это могло произойти потому, что вы выполняете выборку (fetch) курсора, который был закрыт (close) или не был открыт (open).
NOT_LOGGED_ON	ORA-01012	Вы пытаетесь выполнить вызов в Oracle, не подключившись к Oracle.
LOGIN_DENIED	ORA-01017	Вы пытаетесь войти в Oracle с неверными имя пользователя / пароль.
NO_DATA_FOUND	ORA-01403	Вы пробовали один из следующих вариантов:

1. 1. Вы выполнили SELECT INTO и запрос не вернул ни одной строки.
2. 2. Вы ссылаетесь на неинициализированную строку в таблице.
3. 3. Вы читаете после конца файла пакета UTL_FILE.

TOO_MANY_ROWSORA-01422Вы пытались выполнить SELECT INTO и запрос вернул более одной строки.ZERO_DIVIDEORA-01476Вы пытались поделить число на ноль.INVALID_NUMBERORA-01722Вы пытаетесь выполнить оператор SQL который пытается преобразовать строку в число.STORAGE_ERRORORA-06500Вы исчерпали доступную память или память повреждена.PROGRAM_ERRORORA-06501Это общее сообщение Обратитесь в службу поддержки Oracle, возбуждается по причине обнаружения внутренней ошибки.VALUE_ERRORORA-06502Вы пытались выполнить операцию и была ошибка преобразования, усечения, или ограничения числовых или символьных данных.CURSOR_ALREADY_OPENORA-06511Вы попытались открыть курсор, который уже открыт.

Синтаксис

Рассмотри синтаксис встроенных исключительных ситуаций в процедуре и функции.

Синтаксис для процедуры

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

Синтаксис для функции

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

Пример использования исключительных ситуаций в процедуре.