Особенности обработки ошибок сервера базы данных Oracle. Исключения (ошибки) в Oracle Коды ошибок oracle

Exception Handling in PL/SQL

In any procedural language, it is critical to remember that the programs are a complete and separate entity from the database. Hence, whenever the program requests rows from the database, the programmer must make sure that the request completed successfully.

In relational databases, the database will always pass a special variable called SQLCODE back to any calling program. The SQLCODE returned from reach call from the program to the database is translated by Oracle into a named Boolean variable (See table below).

PL/SQL Exception Variable

ACCESS_INTO_NULL

COLLECTION_IS_NULL

CURSOR_ALREADY_OPEN

DUP_VAL_ON_INDEX

ROWTYPE_MISMATCH

SUBSCRIPT_BEYOND_COUNT

SUBSCRIPT_OUTSIDE_LIMIT

SYS_INVALID_ROWID

TIMEOUT_ON_RESOURCE

For example, if the database returns a SQLCODE=100, the PL/SQL variable NO_DATA_FOUND will be set to TRUE.

Without exception, all PL/SQL programs should be made to abort whenever an unexpected SQLCODE is returned by the Oracle database.

This can have a disastrous effect on the database, especially when the PL/SQL loads data into tables based upon false premises. To prevent this tragedy, Oracle provides a WHEN OTHERS variable, which is set to TRUE if any unexpected SQLCODE is returned from the Oracle database.

For example, consider the following code:

DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
err_num:= SQLCODE;
err_msg:= SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;

Here we see that our exception handling has an EXCEPTIONS area testing WHEN OTHERS. If the WHEN OTHERS Boolean variable is TRUE, the PL/SQL code captures the SQLCODE and the associated error message (SQLERRM), and stores these values into a special Oracle errors table.

Oracle Exception Handling

Developers often flag error conditions and handle them using Oracle exception handling and the use of IF-THEN logic.

Oracle exception handling u sing IF-THEN logic to flag errors

The above example illustrates Oracle exception handling using the boolean variable bAidAmountOk to keep track of a condition throughout the processing of each student record.

This use of Oracle exception handling has an impact on performance. Oracle exception handling uses multiple instructions to test for the error condition. Each Oracle exception handling instruction requires CPU cycles to complete. A much better approach involves the use of Oracle exception handling to avoid wasting CPU cycles, as seen below:

Using Oracle exception handlers to improve performance.

In this example of Oracle exception handling, the xAID_AMOUNT_OK exception is explicitly raised inside the loop. This allows execution to skip the instructions that occur after the student"s GPA is checked, cutting down on the cpu used in the Oracle exception handling.

Oracle exception handling is highly performance efficient. When an Oracle exception is raised, all subsequent instructions within the block are bypassed so the exception can be handled by an Oracle exception handler. Oracle exception handling can be utilized to significantly boost performance.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their .

Errata? Performance Tuning


Copyright 1996 - 2017

All rights reserved by Burleson

® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

При возникновении исключительных ситуаций важно выдавать дружест­венные к пользователю сообщения об ошибках. Об исключениях уже упоминалось в разделе, посвященном базовым блокам PL/SQL. Теперь настало время рассмотреть их подробнее.

Исключения

Исключение – это состояние ошибки, которое активизируется – или возбуждается – при возникновении некоторой проблемы. Существует много разных исключений, каждое из которых связано с определенным типом проблем. При возникновении исключительной ситуации выполнение кода останавливается на операторе, который возбудил исключение, и управление передается той части блока, которая обрабатывает это исключение. Если блок не содержит выполняемой секции, PL/SQL пытается найти выполняемую секцию во включающем базовом блоке (enclosing basic block), т.е. в блоке, который является внешним по отношению к коду, возбудившему исключение. Если в непосредственном включающем блоке отсутствует обработчик данного исключения, то поиск продолжается в блоках следующих уровней, пока не будет найден подходящий обработчик, а если его найти не удается, то выполнение программы прекращается с выдачей сообщения о необрабатываемой ошибке.

Часть блока, предназначенная для обработки исключений, – это идеальное место для выдачи информативных сообщений об ошибках и выполнения очистки (cleanup), позволяющей избавиться от всего, что могло бы в дальнейшем вызвать путаницу или проблемы. Если исключение было возбуждено в ходе выполнения процедуры вставляющей строки в таблицу, то типичная процедура очистки может включать в себя оператор ROLLBACK.

После того как управление было передано обработчику исключения, оно уже не возвращается оператору, ставшему причиной этого исключения. Вместо этого управление передается оператору включающего базового блока, который следует сразу за вызовом вложенного блока или процедуры/функции.

Системные исключения

Вы уже знакомы с исключением ZERO_DIVIDE, предопределенным в PL/SQL. Существует довольно много других системных исключений, которые распознаются и возбуждаются PL/SQL или Oracle. В таблице 1 приведен более полный список системных исключений.

В PL/SQL можно выдавать пользователям информацию об ошибке двумя способами. Первый способ – использовать команду SQLCODE, которая возвращает код ошибки. Этот код представляет собой отрицательное число, обычно равное номеру ошибки ORA, которая выводится при завершении приложения, если исключение осталось необработанным. Второй способ – возвращать текстовое сообщение, описывающее ошибку. Неудивительно, что соответствующая команда называется SQLERRM. В обработчике исключения можно использовать как SQLCODE, так и SQLERRM. Замечание: не у всех системных исключений есть имена.

Таблица 1 . Системные исключения

Системное исключение, код ошибки

Причина возбуждения

CURSOR _ ALREADY _ OPEN

ORA -06511

Попытка открыть уже открытый курсор

DUP_VAL_ON_INDEX

ORA- 00001

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

INVALID _ CURSOR

ORA-01001

Попытка применить команду FETCH к неоткрытому курсору или попытка закрыть курсор, который не открывался

NO_DATA_FOUND

ORA-01403

Попытка выполнить SELECT INTO, когда SELECT возвращает нулевое количество строк (а также другие причины, описание которых выходит за рамки этой книги)

PROGRAM _ ERROR

ORA-06501

Внутренняя ошибка. Обычно означает, что вам нужно обратиться в службу поддержки Oracle

STORAGE _ ERROR

ORA-06500

Программе не хватает системной памяти

TIMEOUT_ON_RESOURCE

ORA-00051

Программа слишком долго ожидала доступности некоторого ресурса

TOO_MANY_ROWS

ORA-01422

SELECT INTO в PL/SQL вернул более одной строки

VALUE _ ERROR

ORA-06502

PL/SOL встретил неправильное преобразование или усечение данных, или неправильное ограничение на данные

ZERO _ DIVIDE

ORA-01476

Попытка деления на нуль

Все прочие исключения и внутренние ошибки, которые не охватываются исключениями, опреде­ленными в базовом блоке. Используется в тех слу­чаях, когда вы точно не знаете, какое именованное исключение предстоит обрабатывать, и хотите обрабатывать любые возбуждаемые исключения

Теперь вернемся к самому первому примеру этой главы и используем в нем SQLCODE и SQLERRM. Ниже приведен исходный код примера и результаты его запуска (рис.1).

Num_a NUMBER:= 6;

Num_b NUMBER;

Num_b:= 0;

Num_a:= Num_a / Num_b;

Num_b:= 7;

dbms_output.put_line(" Value of Num_b "|| Num_b);

EXCEPTION

WHEN ZERO_DIVIDE THEN

err_num NUMBER:= SQLCODE;

err_msg VARCHAR2(512) := SQLERRM;

dbms_output.put_line("ORA Error Number " || err_num);

dbms_output.put_line("ORA Error message " || err_msg);

dbms_output.put_line("Value of Num_a " || Num_a);

dbms_output.put_line("Value of Num_b " || Num_b);

SQL> set serveroutput on

SQL> DECLARE

2 num_a NUMBER:= 6;

3 num_b NUMBER;

4 BEGIN

5 num_b:= 0;

6 num_a:= num_a / num_b;

7 num_b:= 7;

8 dbms_output.put_line(" Value of num_b "|| num_b);

9 EXCEPTION

10 WHEN ZERO_DIVIDE

11 THEN

13 err_num NUMBER:= SQLCODE;

14 err_msg VARCHAR2(512) := SQLERRM;

15 BEGIN

16 dbms_output.put_line("ORA Error Number "|| err_num);

17 dbms_output.put_line("ORA Error message " || err_msg);

18 dbms_output.put_line("Value of num_a " || num_a);

19 dbms_output.put_line("Value of num_b " || num_b);

20 END;

21 END;

ORA Error Number -1476

ORA Error Message ORA-01476: divisor is equal to zero

Value of num_а 6

Value of num_b 0

PL/SQL procedure successfully completed.

Рис. 1. Использование SQLCODE и SQLERRM при обработке системных исключений

In this chapter, we will discuss Exceptions in PL/SQL. An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions −

  • System-defined exceptions
  • User-defined exceptions

Syntax for Exception Handling

The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN −

DECLARE BEGIN EXCEPTION WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements ........ WHEN others THEN exception3-handling-statements END;

Example

Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters −

DECLARE c_id customers.id%type:= 8; c_name customerS.Name%type; c_addr customers.address%type; BEGIN 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); EXCEPTION WHEN no_data_found THEN dbms_output.put_line("No such customer!"); WHEN others THEN dbms_output.put_line("Error!"); END; /

No such customer! PL/SQL procedure successfully completed.

The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND , which is captured in the EXCEPTION block .

Raising Exceptions

Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE . Following is the simple syntax for raising an exception −

DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; END;

You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way.

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;

Example

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 customers.id%type:= &cc_id; c_name customerS.Name%type; c_addr customers.address%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 customers.id%type:= &cc_id; new 2: c_id customers.id%type:= -6; ID must be greater than zero! PL/SQL procedure successfully completed.

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions −

Exception Oracle Error SQLCODE Description
ACCESS_INTO_NULL 06530 -6530 It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND 06592 -6592 It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL 06531 -6531 It is raised when 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.
DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR 01001 -1001 It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER 01722 -1722 It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED 01017 -1017 It is raised when a program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND 01403 +100 It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON 01012 -1012 It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR 06501 -6501 It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL 30625 -30625 It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR 06500 -6500 It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS 01422 -1422 It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR 06502 -6502 It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE 01476 1476 It is raised when an attempt is made to divide a number by zero.

Рассмотрим конструкцию по отлову исключений(ошибок) в Oracle

when others then - отлавливает все ошибки, которые могут возникнуть, если до нее не отловили ранее

set serveroutput on;

declare
a INTEGER:= 1;
b INTEGER:= 0;
c CHAR(1) := "c";
begin
--Не вызовет ошибки
DBMS_OUTPUT.PUT_LINE(a / a);
--Вызовет деление на 0
--DBMS_OUTPUT.PUT_LINE(a / b);
--Вызовет все остальные ошибки
--DBMS_OUTPUT.PUT_LINE(a / c);
exception
when zero_divide then
DBMS_OUTPUT.PUT_LINE("zero_divide!");
when others then
DBMS_OUTPUT.PUT_LINE("when others then!");
end;

Перевели табличку с описанием ошибок: http://oracleplsql.ru/named-system-exceptions.html

/*
DUP_VAL_ON_INDEX Вы пытались выполнить операторы insert или update поля, изменение значения которого нарушит ограничение уникальности поля.
TIMEOUT_ON_RESOURCE Возбуждается при возникновении таймаута, когда ORACLE ожидает ресурса.
TRANSACTION_BACKED_OUT Откат удаленной части транзакции.
INVALID_CURSOR Вы пытаетесь сослаться на курсор, который еще не существует. Это могло произойти потому, что вы выполняете выборку (fetch) курсора, который был закрыт (close) или не был открыт (open).
NOT_LOGGED_ON Вы пытаетесь выполнить вызов в Oracle, не подключившись к Oracle.
LOGIN_DENIED Вы пытаетесь войти в Oracle с неверными имя пользователя / пароль.
NO_DATA_FOUND Вы пробовали один из следующих вариантов:
1. Вы выполнили SELECT INTO и запрос не вернул ни одной строки.
2. Вы ссылаетесь на неинициализированную строку в таблице.
3. Вы читаете после конца файла пакета UTL_FILE.
TOO_MANY_ROWS Вы пытались выполнить SELECT INTO и запрос вернул более одной строки.
ZERO_DIVIDE Вы пытались поделить число на ноль.
INVALID_NUMBER Вы пытаетесь выполнить оператор SQL который пытается преобразовать строку в число.
STORAGE_ERROR Вы исчерпали доступную память или память повреждена.
PROGRAM_ERROR Это общее сообщение Обратитесь в службу поддержки Oracle, возбуждается по причине обнаружения внутренней ошибки.
VALUE_ERROR Вы пытались выполнить операцию и была ошибка преобразования, усечения, или ограничения числовых или символьных данных.
CURSOR_ALREADY_OPEN Вы попытались открыть курсор, который уже открыт.
*/

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

Описание

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

Исключительные ситуации ORACLE Ошибки Oracle Пояснения
DUP_VAL_ON_INDEX Вы пытались выполнить операторы insert или update поля, изменение значения которого нарушит ограничение уникальности поля.
TIMEOUT_ON_RESOURCE Возбуждается при возникновении таймаута, когда ORACLE ожидает ресурса.
TRANSACTION_BACKED_OUT Откат удаленной части транзакции.
INVALID_CURSOR Вы пытаетесь сослаться на курсор, который еще не существует. Это могло произойти потому, что вы выполняете выборку (fetch) курсора, который был закрыт (close) или не был открыт (open).
NOT_LOGGED_ON Вы пытаетесь выполнить вызов в Oracle, не подключившись к Oracle.
LOGIN_DENIED Вы пытаетесь войти в Oracle с неверными имя пользователя / пароль.
NO_DATA_FOUND ORA-01403 Вы пробовали один из следующих вариантов:
  1. 1. Вы выполнили SELECT INTO и запрос не вернул ни одной строки.
  2. 2. Вы ссылаетесь на неинициализированную строку в таблице.
  3. 3. Вы читаете после конца файла пакета UTL_FILE.
TOO_MANY_ROWS ORA-01422 Вы пытались выполнить SELECT INTO и запрос вернул более одной строки.
ZERO_DIVIDE Вы пытались поделить число на ноль.
INVALID_NUMBER Вы пытаетесь выполнить оператор SQL который пытается преобразовать строку в число.
STORAGE_ERROR ORA-06500 Вы исчерпали доступную память или память повреждена.
PROGRAM_ERROR ORA-06501 Это общее сообщение Обратитесь в службу поддержки Oracle, возбуждается по причине обнаружения внутренней ошибки.
VALUE_ERROR ORA-06502 Вы пытались выполнить операцию и была ошибка преобразования, усечения, или ограничения числовых или символьных данных.
CURSOR_ALREADY_OPEN ORA-06511 Вы попытались открыть курсор, который уже открыт.

Синтаксис

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

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

CREATE PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
BEGIN
executable_sectionEXCEPTION
WHEN exception_name1 THEN

WHEN exception_name2 THEN

WHEN exception_name_n THEN

WHEN OTHERS THEN

END ;

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

CREATE FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
BEGIN
executable_sectionEXCEPTION
WHEN exception_name1 THEN

WHEN exception_name2 THEN

WHEN exception_name_n THEN

WHEN OTHERS THEN

END ;

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

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE add_new_supplier (supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2) IS BEGIN INSERT INTO suppliers (supplier_id, supplier_name) VALUES (supplier_id_in, supplier_name_in); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN raise_application_error (-20001,"You have tried to insert a duplicate supplier_id."); WHEN OTHERS THEN raise_application_error (-20002,"An error has occurred inserting a supplier."); END;

CREATE OR REPLACE PROCEDURE add_new_supplier

(supplier_id_inIN NUMBER ,supplier_name_inIN VARCHAR2 )

BEGIN

INSERT INTO suppliers(supplier_id,supplier_name)