PL/SQL блок:
DECLARE
... -- объявляющая секция
BEGIN
... -- выполняющая секция
EXCEPTION
... -- секция обработки исключительных ситуаций
END;
/
При установлении исключительной ситуации управление программой сразу же передается
в секцию исключительных ситуаций блока.
Если такой секции в блоке нет, то исключение передается в объемлющий блок.
После передачи управления обработчику, вернуться в выполняющую секцию блока невозможно.
Исключения бывают:
- стандартные
- определенные пользователем
Стандартные исключительные ситуации инициируются автоматически при возникновении
соответствующей ошибки Oracle.
Исключительные ситуации, определяемые пользователем,
устанавливаются явно при помощи оператора RAISE.
Обрабатываются исключения так:
EXCEPTION
WHEN имя_ex1 THEN
...; -- обработать
WHEN имя_ex2 THEN
...; -- обработать
WHEN OTHERS THEN
...; -- обработать
END;
/
Имена исключений не должны повторяться т.е. каждое исключение может
обрабатываться максимум только одним обработчиком в секции EXCEPTION
Один обработчик может обслуживать несколько исключительных ситуаций
и их нужно перечислить в условии WHEN через OR
EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
INSERT INTO log_table(info) VALUES ('A select error occurred.');
END;
/
Два исключения одновременно один обработчик обработать не может:
WHEN имя_ex1 AND имя_ex2 - > ERR
Пользовательское исключение должно быть определено:
DECLARE
e_my_ex EXCEPTION;
...
BEGIN
IF (...) THEN
RAISE e_my_ex;
END IF;
...
EXCEPTION
WHEN e_my_ex THEN
...
END;
/
После перехвата более специализированных исключений:
WHEN ... THEN
...
WHEN ... THEN
мы можем перехватить все остальные исключения с помощью:
WHEN OTHERS THEN
...
Обработчик OTHERS рекомендуется помещать на самом высоком уровне программы:
(В самом высшем блоке)
для обеспечения распознавания всех возможных ошибок.
Иначе ошибки будут распространяться в вызывающую среду и возможны
нежелательные последствия, такие как откат на сервере текущей транзакции.
Не используйте в промышленном коде такое:
WHEN OTHERS THEN NULL;
т.к. оно будет молчаливо перехватывать все неожиданные ошибки не сообщая,
что они произошли.
Обработчик OTHERS должен регистрировать ошибку и возможно предоставлять
дополнительную информацию для дальнейшего исследования.
WHEN OTHERS THEN
INSERT INTO log_table(info) VALUES ('Another error occurred.');
END;
/
Информацию об ошибках можно получить при помощи двух встроенных функций:
- SQLCODE
- SQLERRM
первая возвращает код текущей ошибки а вторая текст сообщения об ошибке
Для исключений определенных пользователем:
SQLCODE возвращает 1
а
SQLERRM "User-defined Exception"
WHEN OTHERS THEN
v_ErrorCode := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM, 1, 200);
INSERT INTO log_tab(code, message, info) VALUES (v_ErrorCode, v_ErrorText, 'Oracle error.');
END;
/
В таблице log_tab поле message ограничено 200 символами
и чтобы не произошло ошибки при вставке, мы урезаем длину
сообщения до 200 символов с помощью SUBSTR
А то максимальная длина сообщения может достигать 512 символов.
Функция SQLERRM может принимать один числовой аргумент.
При этом она возвратит текст сообщения об ошибке, код которой равен заданному числу.
Аргумент должен быть всегда отрицательным числом.
Если аргумент равен 0, то будет возвращено сообщение:
ORA-0000: normal, succesful completion
При положительном аргументе не равном 100 будет возвращено сообщение:
non-ORACLE Exception
А при
SQLERRM(100) - > ORA-1403: no data found
Это исключение ANSI
Остальные коды ошибок Oracle все отрицательные.
Для получения информации об ошибке можно также использовать функцию
FORMAT_ERROR_STACK из пакета DBMS_UTILITY
Её можно непосредственно использовать в операторах SQL:
WHEN OTHERS THEN
INSERT INTO log_tab(code, message, info) VALUES (NULL,
SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 200),
'Oracle error occurred.');
END;
/
Ещё одна функция.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
она аналогична FORMAT_ERROR_STACK
но не подвержена ограничению длины сообщения в 2000 байт.
Она возвращает полностью весь стек ошибок на момент инициирования исключительной ситуации.
Любое именованное исключение можно связать с конкретной ошибкой ORACLE.
Например, в ORACLE есть стандартная ошибка ORA-1400, которая возникает при пропуске значения
или вставке значения NULL в столбец с ограничением NOT NULL.
ORA-1400: mandatory NOT NULL column missing or NULL during insert.
Мы хотим создать свое пользовательское именованное исключение и связать его с этой стандартной ошибкой ORA-1400
DECLARE
e_my_ex EXCEPTION;
PRAGMA EXCEPTION_INIT(e_my_ex, -1400);
BEGIN
WHEN e_my_ex THEN
INSERT INTO log_tab(info) VALUES ('ORA-1400 occurred.');
END;
/
Теперь мы перехватываем её по имени с помощъю WHEN или THEN
Все стандартные исключительные ситуации также ассоциируются с соответствующими им ошибками Oracle
при помощи прагмы EXCEPTION_INIT в пакете STANDARD
VALUE_ERROR - > ORA-6501
TO_MANY_ROWS - > ORA-1422
ZERO_DIVIDE - > ORA-1476
..........
и т.д.
Так что если вам не хватает некоего имени конкретной ошибки ORA-NNNN,
то придумайте свое имя и свяжите его с ошибкой с помощью прагмы : EXCEPTION_INIT
Для собственных пользовательских исключений можно придумать свои коды ошибок, которые разрешено брать из диапазона:
-20000 до -20999
и придумать свой текст сообщения
RAISE_APPLICATION_ERROR(номер, текст, [флаг]);
TRUE - пополнить список ранее произошедших ошибок
FALSE - новая ошибка заместит текущий список ошибок (по умолчанию)
set serveroutput on
variable a NUMBER;
variable b NUMBER;
exec :a := 0;
exec :b := 10;
DECLARE
l_a NUMBER := :a;
l_b NUMBER := :b;
l_c NUMBER;
BEGIN
IF l_a = 0 THEN
raise_application_error(-20005, 'Divizor is 0');
END IF;
l_c := l_b / l_a;
dbms_output.put_line('The result: '||l_c);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/
Поскольку у исключения нет имени, то его может обработать только обработчик OTHERS
Но такое исключение можно и поименовать
и с помощью прагмы связать с нашим кодом.
DECLARE
my_ex EXCEPTION;
.....
.....
PRAGMA EXCEPTION_INIT(my_ex, -20005);
BEGIN
IF (...) THEN
raise_application_error(-20005, 'Divizor is 0');
.....
.....
EXCEPTION
WHEN my_ex THEN
dbms_output.put_line(SQLERRM);
END;
/
Теперь это исключение можно обработать по имени с помощью:
WHEN my_ex THEN
EXCEPTION PROPAGATION
enclosing block - обьемлющий блок
Если в текущем блоке имеется обработчик данной исключительной ситуации,
то он выполняется и блок успешно завершается.
Управление передаётся вышестоящему блоку.
Если обработчик отсутствует, исключительная ситуация передается в обьемлющий блок и инициируется там.
Если обьемлющего блока не существует, то исключение будет передано вызывающей среды (например SQL*Plus).
При вызове процедуры также может создаваться обьемлющий блок:
BEGIN
p(...); -- вызов процедуры
EXCEPTION
WHEN OTHERS THEN
-- исключение инициированное p()
-- будет обработано здесь
END;
/
Исключения инициируемые в секции обьявлений (DECLARE) не обрабатываются секцией EXCEPTION
текущего блока, а передаются в EXCEPTION обьемлющего блока.
Тоже самое, если исключение инициируется в секции EXCEPTION,
то обработка данного исключения передается в обьемлющий блок.
Исключительную ситуацию можно обработать в текущем блоке и сразу снова установить
то же самое исключение, которое будет передано в обьемлющую область:
DECLARE
A EXCEPTION;
BEGIN
RAISE A;
EXCEPTION
WHEN A THEN
INSERT INTO log_tab(info) VALUES ('Exception A occurred.');
COMMIT;
RAISE;
END;
/
Тут commit гарантирует, что результаты insert будут зафиксированы
в базе данных в случае отката транзакции.
С помощью пакета UTL_FILE можно избежать необходимости commit
или используйте автономные транзакции.
Область действия исключительной ситуации
BEGIN
DECLARE
e_ex EXCEPTION; -- видно по имени только внутри блока
BEGIN
RAISE e_ex;
END;
EXCEPTION
-- тут исключение не видно по имени e_ex
-- и его можно обработать с помощью обработчика OTHERS
WHEN OTHERS THEN
-- инициируем это исключение повторно
RAISE; -- Теперь это исключение передается вызывающей среде
END;
/
Если сообщение об ошибке, определяемой пользователем, нужно передать из блока,
рекомендуется описывать исключительную ситуацию и модуле так,
чтобы она была видима вне этого блока.
Или воспользуйтесь функцией : RAISE_APPLICATION_ERROR
Как описать исключение, которое будет видно вне блока?
Нужно создать пакет Globals и описать в нем пользовательское исключение.
Такая исключительная ситуация будет видима и во внешнем блоке.
CREATE OR REPLACE PACKAGE Globals AS
e_ex EXCEPTION;
END Globals;
BEGIN
BEGIN
RAISE Globals.e_ex;
END;
EXCEPTION
WHEN Globals.e_ex THEN
-- инициируем повторно
-- для передачи в вызывающую среду
RAISE;
END;
/
В пакете Globals можно также объявлять:
- таблицы
- переменные
- типы
Избегайте необработанных исключений
Нельзя допускать завершение программ, пока в них остаются необработанные исключения
Используйте обработчик OTHERS на самом верхнем уровне программы.
И пусть он регистрирует факт и время возникновения ошибки.
И ни одна ошибка не останется без внимания.
DECLARE
v_ErrorNumber NUMBER;
v_ErrorText VARCHAR2(200);
BEGIN
...
...
EXCEPTION
WHEN OTHERS THEN
...
v_ErrorNumber := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM, 1, 200);
INSERT INTO log_tab(code, message, info)
VALUES (v_ErrorNumber, v_ErrorText,
'Oracle error ...at ' || to_char(sysdate, 'DD-MON-YYHH24:MI:SS'));
END;
/
Можно использовать и утилиту DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
она регистрирует первоначальное местовозникновения исключения.
Как определить, где произошла ошибка?
BEGIN
SELECT ...
SELECT ...
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- какой select инициировал ошибку?
END;
/
Можно создать счетчик, указывающий на sql - оператор:
DECLARE
v_sel_count NUMBER := 1;
BEGIN
SELECT ...
v_sel_count := 2;
SELECT ...
v_sel_count := 3;
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO log_tab(info)
VALUES ('no data found in select '||v_sel_count);
END;
/
Можно разместить каждый select в собственном врутреннем блоке
BEGIN
BEGIN
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO log_tab(info)
VALUES ('no data found in select 1');
END;
BEGIN
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO log_tab(info)
VALUES ('no data found in select 2');
END;
BEGIN
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO log_tab(info)
VALUES ('no data found in select 3');
END;
END;
/
Или использовать : DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
и потом анализировать файл трассировки.
Пусть в нашей программе Oracle выдает ошибку ORA-01844: not f valid month
перехватить его можно так:
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1843 THEN
Да, код плохо читаем.
Сделаем его более лучшим:
PROCEDURE my_procedure
IS
invalid_month EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_month, -1843);
BEGIN
....
EXCEPTION
WHEN invalid_month THEN
так уже более понятней.
