Иван Задворьев - Язык PL/SQL

Передача исключений в вызывающую среду

При разработке клиентских приложений при любом обращении к базе данных нужно предусмотреть обработку ошибок, которые могут произойти как при вызове хранимых программ PL/SQL, так и при выполнении предложений SQL. В коде клиентских программ для этого следует использовать конструкции try/catch, имеющиеся в Java и C, или try/except – в Python.

Если в ходе работы программы PL/SQL произошло так никем и не обработанное исключение, то оно вылетит «наружу», то есть будет передано вызывавшей среде. Например, в SQL*Plus или в прикладное клиентское приложение. В SQL*Plus это выглядит вот так:

Без вылета исключения «наружу»

С вылетом исключения «наружу»

SQL> DECLARE

2 i int;

3 BEGIN

4 i := 1/0;

5 EXCEPTION

6 WHEN OTHERS THEN

7 DBMS_OUTPUT.PUT_LINE('/0');

8 END;

9 /

/0

PL/SQL procedure successfully

completed.

SQL> DECLARE

2 i int;

3 BEGIN

4 i := 1/0;

5 END;

6 /

DECLARE

*

ERROR at line 1:

ORA-01476: divisor is equal to zero

ORA-06512: at line 4

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

En error was encountered performing the requested operation

ORA-00604: error occurred at recursive SQL level 1

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 7

View program sources of error stack?

Как-то раз один из авторов книги был свидетелем того, как характерная «оракловая» ошибка вида ORA-123456 выскочила в сообщении приложения банковской информационной системы, когда операционистка оформляла вклад. Девушка сначала некоторое время пыталась понять, что же ей программа хочет сказать на английском языке, потом подозвала более опытного коллегу, который со словами «Это нормально, бывает» закрыл сообщение, и оформление продолжилось.

Это не нормально. Все возможные исключения в программах PL/SQL должны обрабатываться, причем продуманным унифицированным способом. В книге Стивена Фейерштейна «PL/SQL для профессионалов» теме обработки исключений посвящена отдельная глава объемом 34 страницы, что больше, чем написано в этой книге про условные команды и циклы вместе взятые.

Диагностические функции

В PL/SQL имеется несколько диагностических функций для получения информации об исключениях:

SQLCODE – возвращает код ошибки последнего исключения, инициированного в блоке PL/SQL;

SQLERRM – возвращает сообщение об ошибке последнего исключения, инициированного в блоке PL/SQL;

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE – возвращает отформатированную строку с содержимым стека программ и номеров строк кода.

Максимальная длина строки, возвращаемой функцией SQLERRM, составляет 512 байт. Из-за этого ограничения рекомендуется использовать вместо SQLERRM функцию встроенного пакета DBMS_UTILITY.FORMAT_ERROR_STACK, которая выводит строку с отформатированным стеком сообщений. Приведем несколько примеров использования диагностических функций:

SQL> CREATE OR REPLACE PROCEDURE error_proc IS

2 i INTEGER;

3 BEGIN

4 i := 1/0;

5 i := 15;

6 END;

7 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE parent_proc IS

2 BEGIN

3 error_proc;

4 END;

5 /

Procedure created.

SQL> BEGIN

2 parent_proc;

3 EXCEPTION

4 WHEN OTHERS THEN

5 DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);

6 DBMS_OUTPUT.PUT_LINE('SQLERRM: '||SQLERRM);

7 DBMS_OUTPUT.PUT_LINE('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:');

8 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

9 END;

10 /

SQLCODE: -1476

SQLERRM: ORA-01476: divisor is equal to zero

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:

ORA-06512: at "U1.ERROR_PROC", line 4

ORA-06512: at "U2.PARENT_PROC", line 3

ORA-06512: at line 2

PL/SQL procedure successfully completed.

По строке, которую вернула FORMAT_ERROR_BACKTRACE, видно, как пролетало системное исключение по строкам кода: сначала она возникла в процедуре error_proc на четвертой строке, управление из error_proc сразу вернулось в родительский для error_proc блок – процедуру parent_proc (на третью строку, где вызывалась error_proc). Далее выводятся сведения о второй строке анонимного блока, в котором вызывалась parent_proc. При этом в стеке появились три ошибки ORA-06512.

Также видно, что функция DBMS_UTILITY.FORMAT_ERROR_BACKTRACE не выдает сообщение о самой исходной ошибке, поэтому совместно с этой функцией следует использовать функции SQLERRM или DBMS_UTILITY.FORMAT_ERROR_STACK.

Пользовательские исключения

Пользовательские исключения объявляются следующим образом:

имя исключения EXCEPTION;

Пользовательские исключения инициируются командой RAISE, у которой есть две формы:

RAISE имя исключения (исключение должно быть предопределенным в пакете STANDARD или объявленным в области видимости);

RAISE (может быть вызвана только внутри обработчиков исключений, когда в обработчике нужно повторно инициировать то же самое исключение).

Приведем пример работы с пользовательскими исключениями:

DECLARE

l_amount INTEGER := -100;

l_crncy VARCHAR2(3) := 'RUR';

ex_negative_payment_amount EXCEPTION;

ex_non_rur_payment EXCEPTION;

BEGIN

IF l_amount < 0 THEN

RAISE ex_negative_payment_amount;

END IF;

IF l_crncy <> 'RUR' THEN

RAISE ex_non_rur_payment;

END IF;

… все проверки пройдены, обрабатываем платеж

EXCEPTION

WHEN ex_negative_payment_amount THEN

DBMS_OUTPUT.PUT_LINE('Ошибка: сумма отрицательная: '||l_amount);

WHEN ex_non_rur_payment THEN

DBMS_OUTPUT.PUT_LINE('Ошибка: платеж не в рублях');

END;

Видно, что код имеет линейный вид: проверки записаны одна за одной и если платеж не проходит проверку, то управление сразу переходит в раздел обработки исключений. Без исключений код выглядит нелинейно – как несколько ветвей команды IF:

DECLARE

l_amount INTEGER := -100;

l_crncy VARCHAR2(3) := 'RUR';

ex_negative_payment_amount EXCEPTION;

ex_non_rur_payment EXCEPTION;

BEGIN

IF l_amount < 0 THEN

DBMS_OUTPUT.PUT_LINE('Ошибка: сумма отрицательная: '||l_amount);

ELSE

– второй уровень вложенности IF

IF l_crncy <> 'RUR' THEN

DBMS_OUTPUT.PUT_LINE('Ошибка: платеж не в рублях');

ELSE

– потом будет третий уровень вложенности IF

… наконец все проверки пройдены, обрабатываем платеж

END IF;

END IF;

END;

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

Посмотрим на поведение диагностических функций при инициировании пользовательских исключений:

SQL> DECLARE

2 exception1 EXCEPTION;

3 BEGIN

4 RAISE exception1;

5 EXCEPTION

6 WHEN OTHERS THEN

7 DBMS_OUTPUT.PUT_LINE('SQLCODE print: '||SQLCODE);

8 DBMS_OUTPUT.PUT_LINE('SQLERRM print: '||SQLERRM);

9 END;

10 /

SQLCODE print: 1

SQLERRM print: User-Defined Exception

PL/SQL procedure successfully completed.

Видно, что пользовательские исключения имеют код ошибки 1, а сообщение об ошибке малоинформативно. Поэтому при работе над кодом нужно следить, чтобы пользовательские исключения обрабатывались соответствующими им обработчиками, а не OTHERS-обработчиком, внутри которого нельзя узнать, какое именно пользовательское исключение прилетело. И уж тем более пользовательские исключения PL/SQL не должны вылетать «наружу» в вызывающую среду.