Пакет глобальной обработки ошибок oracle plsql. Особенности обработки ошибок сервера базы данных Oracle

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 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).

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.

При возникновении исключительных ситуаций важно выдавать дружест­венные к пользователю сообщения об ошибках. Об исключениях уже упоминалось в разделе, посвященном базовым блокам 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 . Системные исключения

Теперь вернемся к самому первому примеру этой главы и используем в нем 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 при обработке системных исключений

Рассмотрим конструкцию по отлову исключений(ошибок) в 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 Вы попытались открыть курсор, который уже открыт.
*/

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

Исключение – переменная PL/SQL, возбуждаемая во время выполнения блока и прекращающая выполнение действий в теле блока. Если Ваш блок PL/SQL содержит секцию обработки исключений, Вы можете определить действия, которые должны быть выполнены для того или иного исключения перед завершением блока.

Исключение возбуждается автоматически сервером Oracle в случае возникновения ошибки Oracle (TOO_MANY_ROWS, NO_DATA_FOUND). Однако Вы можете определить свое собственное исключение в декларативной секции блока PL/SQL и, затем, явно возбудить его в исполняемой секции блока.

Если в исполняемой секции блока возбуждается исключение, управление передается секции обработки исключений (секции EXCEPTION). В том случае, если исключение будет успешно обработано, блок PL/SQL завершится без ошибок. Если же обработчика для этого исключения нет, выполнение блока PL/SQL прекратится в аварийном порядке.

Имеется три типа исключений:

Перехват исключений

Перехват исключений осуществляется в секции обработки исключений блока PL/SQL.

EXCEPTION

WHEN исключение_1 [ OR исключение_2 ...] THEN

операторы ;

WHEN исключение_3 [ OR исключение_4 ...] THEN

операторы ;

исключение – имя предопределенного исключения или исключения, описанного в декларативной секции

WHEN OTHERS – определяет действия по обработке всех исключений, обработка для которых не задана явно

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

Вы можете определить действия, которые должны быть выполнены в случае возбуждения любых исключений, обработчик которых не определен явно. Для этого используется предложение WHEN OTHERS. Предложение WHEN OTHERS может быть только одно и помещается после всех остальных обработчиков исключений.

Перехват предопределенных исключений сервера Oracle

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

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

v_sal emp.sal%TYPE;

SELECT sal INTO v_sal FROM emp WHERE LOWER(job)=LOWER(:v_job);

DBMS_OUTPUT.put_line("Salary of " || :v_job || " is " || TO_CHAR(v_sal));

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.put_line(:v_job || " is not a title of employees");

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.put_line(:v_job || " is a title of many employees");

WHEN OTHERS THEN

DBMS_OUTPUT.put_line("Other error occurred");

Теперь рассмотрим различные варианты, присваивая разные значения хост-переменной v_ job .

SQL> VARIABLE v_job VARCHAR2(20)

SQL> EXECUTE:v_job:= "President"

SQL> @d:\users\except

Salary of President is 5000

PL/SQL procedure successfully completed.

SQL> EXECUTE:v_job:= "Clerk"

PL/SQL procedure successfully completed.

SQL> @d:\users\except

Clerk is a title of many employees

PL/SQL procedure successfully completed.

SQL> EXECUTE:v_job:= "Engineer"

PL/SQL procedure successfully completed.

SQL> @d:\users\except

Engineer is not a title of employees

PL/SQL procedure successfully completed.

Перехват неопределенных исключений сервера Oracle

Если Вы хотите обработать стандартную ошибку сервера Oracle, которая не является предопределенной ошибкой, необходимо сначала объявить это исключение в декларативной секции, связать его со стандартным номером ошибки и сослаться на исключение в секции обработки исключений.