К сожалению, многие программисты не склонны тратить время на то, чтобы застраховать свой код PL/SQL от всех возможных неожиданностей. У большинства из нас хватает проблем с написанием кода, реализующего положительные аспекты приложения: управление данными клиентов, построение счетов и т. д.; вдобавок это увеличивает объем работы. Всегда бывает дьявольски сложно — как с психологической точки зрения, так и в отношении расходования ресурсов — сосредоточиться на негативных аспектах работы системы: что, если пользователь нажмет не ту клавишу? А что делать, если база данных Oracle недоступна?
В результате мы пишем приложения PL/SQL, предназначенные для работы в «идеальном мире», где в программах не бывает ошибок, пользователи вводят лишь правильные данные, а все системы — и аппаратные и программные — всегда в полном порядке.
Конечно, жестокая реальность устанавливает свои правила: как бы вы ни старались, в приложении все равно отыщется еще одна ошибка. А ваши пользователи всегда постараются отыскать последовательность нажатий клавиш, от которых форма перестанет работать. Проблема проста: либо вы выделяете время на отладку и защиту своих программ, либо вам придется вести бесконечные бои в отступлении, принимая отчаянные звонки от пользователей и пытаясь потушить разгорающееся пламя.
К счастью, PL/SQL предоставляет достаточно мощный и гибкий механизм перехвата и обработки ошибок. И вполне возможно написать на языке PL/SQL такое приложение, которое полностью защитит от ошибок и всех пользователей, и базу данных Oracle.
Основные концепции и терминология обработки исключений
В языке PL/SQL ошибки всех видов интерпретируются как исключения — ситуации, которые не должны возникать при нормальном выполнении программы.
К числу исключений относятся:
- ошибки, генерируемые системой (например, нехватка памяти или повторяющееся значение индекса);
- ошибки, вызванные действиями пользователя;
- предупреждения, выдаваемые приложением пользователю.
PL/SQL перехватывает ошибки и реагирует на них при помощи так называемых обработчиков исключений. Механизм обработчиков исключений позволяет четко отделить код обработки ошибок от основной логики программы, а также дает возможность реализовать обработку ошибок, управляемую событиями (в отличие от старой линейной модели). Независимо от того, как и по какой причине возникло конкретное исключение, оно всегда обрабатывается одним и тем же обработчиком в разделе исключений.
При возникновении ошибки — как системной, так и ошибки в приложении — в PL/SQL инициируется исключение. В результате выполнение блока прерывается, и управление передается для обработки в раздел исключений текущего блока, если он имеется. После обработки исключения возврат в тот блок, где исключение было инициировано, невозможен, поэтому управление передается во внешний блок.
Схема передачи управления при возникновении исключения показана на рис. 1.
Рис. 1. Архитектура обработки исключений
Существует два типа исключений:
- Системное исключение определяется в Oracle и обычно инициируется исполняемым ядром PL/SQL, обнаружившим ошибку. Одним системным исключениям присваиваются имена (например,
NO_DATA_FOUND
), другие ограничиваются номерами и описаниями. - Исключение, определяемое программистом, актуально только для конкретного приложения. Имя исключения можно связать с конкретной ошибкой Oracle с помощью директивы компилятора
EXCEPTION_INIT
или же назначить ошибке номер и описание процедуройRAISE_APPLICATION_ERROR
.
В этом блоге будут использоваться следующие термины:
- Раздел исключений — необязательный раздел блока PL/SQL (анонимного блока, процедуры, функции, триггера или инициализационного раздела пакета), содержащий один или несколько обработчиков исключений. Структура раздела исключений очень похожа на структуру команды
CASE
, о которой рассказывалось в этом блоге. - Инициировать исключение — значит остановить выполнение текущего блока PL/SQL, оповещая исполняемое ядро об ошибке. Исключение может инициировать либо Oracle, либо ваш собственный программный код при помощи команды
RAISE
или процедурыRAISE_APPLICATION_ERROR
. - Обработать исключение — значит перехватить ошибку, передав управление обработчику исключения. Написанный программистом обработчик может содержать код, который в ответ на исключение выполняет определенные действия (например, записывает информацию об ошибке в журнал, выводит сообщение для пользователя или передает исключение во внешний блок).
- Область действия — часть кода (конкретный блок или весь раздел), в котором может инициироваться исключение, а также часть кода, инициируемые исключения которого могут перехватываться и обрабатываться соответствующим разделом исключений.
- Передача исключения — процесс передачи исключения во внешний блок, если в текущем блоке это исключение не обработано.
- Необработанное исключение — исключение, которое передается без обработки из «самого внешнего» блока PL/SQL. После этого управление передается исполнительной среде, которая уже сама определяет, как отреагировать на исключение (выполнить откат транзакции, вывести сообщение об ошибке, проигнорировать ее и т. д.).
- Анонимное исключение — исключение, с которым связан код ошибки и описание. Такое исключение не имеет имени, которое можно было бы использовать в команде
RAISE
или секцииWHEN
обработчика исключений. - Именованное исключение — исключение, которому имя присвоено либо Oracle (в одном из встроенных пакетов), либо разработчиком. В частности, для этой цели можно использовать директиву компилятора
EXCEPTION_INIT
(в таком случае имя можно будет применять и для инициирования, и для обработки исключения).
Определение исключений
Прежде чем исключение можно будет инициировать и обрабатывать, его необходимо определить. В Oracle заранее определены тысячи исключений, большинство из которых имеют только номера и пояснительные сообщения. Имена присваиваются только самым распространенным исключениям.
Имена присваиваются в пакете STANDARD
(одном из двух пакетов по умолчанию PL/SQL; другой пакет — DBMS_STANDARD
), а также в других встроенных пакетах, таких как UTL_FILE
и DBMS_SQL
. Код, используемый Oracle для определения исключений (таких, как NO_DATA_FOUND
), не отличается от кода, который вы будете использовать для определения или объявления ваших собственных исключений.
Это можно сделать двумя способами, описанными ниже.
Объявление именованных исключений
Исключения PL/SQL, объявленные в пакете STANDARD
и в других встроенных пакетах, представляют внутренние (то есть системные) ошибки. Однако многие проблемы, с которыми будет сталкиваться пользователь приложения, актуальны только в этом конкретном приложении. Возможно, вашей программе придется перехватывать и обрабатывать такие ошибки, как «отрицательный баланс счета» или «дата обращения не может быть меньше текущей даты». Хотя эти ошибки имеют иную природу, нежели, скажем, ошибки «деления на нуль», они также относятся к разряду исключений, связанных с нормальной работой программы, и должны обрабатываться этой программой.
Одной из самых полезных особенностей обработки исключений PL/SQL является отсутствие структурных различий между внутренними ошибками и ошибками конкретных приложений. Любое исключение может и должно обрабатываться в разделе исключений независимо от типа ошибки.
Конечно, для обработки исключения необходимо знать его имя. Поскольку в PL/SQL имена пользовательским исключениям автоматически не назначаются, вы должны делать это самостоятельно, определяя исключения в разделе объявлений блока PL/SQL. При этом задается имя исключения, за которым следует ключевое слово EXCEPTION
:
имя_исключения EXCEPTION;
Следующий раздел объявлений процедуры calc_annual_sales
содержит два объявления исключений, определяемых программистом:
PROCEDURE calc_annual_sales(company_id_in IN company.company_id%TYPE)
IS
invalid_company_id EXCEPTION;
negative_balance EXCEPTION;
duplicate_company BOOLEAN;
BEGIN
... исполняемые команды ...
EXCEPTION
WHEN NO_DATA_FOUND -- системное исключение
THEN
...
WHEN invalid_company_id
THEN
WHEN negative_balance
THEN
...
END;
По своему формату имена исключений схожи с именами других переменных, но ссылаться на них можно только двумя способами:
- В команде RAISE, находящейся в исполняемом разделе программы (для инициирования исключения):
RAISE invalid_company_id;
- В секции WHEN раздела исключений (для обработки инициированного исключения):
WHEN invalid_company_id THEN
Связывание имени исключения с кодом ошибки
В Oracle, как уже было сказано, имена определены лишь для самых распространенных исключений. Тысячи других ошибок в СУБД имеют лишь номера и снабжены пояснительными сообщениями. Вдобавок инициировать исключение с номером ошибки (в диапазоне от –20 999 до –20 000) может и разработчик приложения, воспользовавшись для этой цели процедурой RAISE_APPLICATION_ERROR
(см. далее раздел «Инициирование исключений»).
Наличие в программном коде исключений без имен вполне допустимо, но такой код малопонятен и его трудно сопровождать. Допустим, вы написали программу, при выполнении которой Oracle выдает ошибку, связанную с данными, например ORA-01843: not a valid month
. Для перехвата этой ошибки в программу включается обработчик следующего вида:
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1843 THEN
Но код получается совершенно непонятным. Чтобы сделать смысл этого кода более очевидным, следует воспользоваться директивой EXCEPTION_INIT
.
Встроенная функция SQLCODE возвращает номер последней сгенерированной ошибки. Она будет рассмотрена далее в разделе «Обработка исключений» этой статьи.
Директива EXCEPTION_INIT
Директива компилятора EXCEPTION_INIT
(команда, выполняемая во время компиляции) связывает идентификатор, объявленный с ключевым словом EXCEPTION
, с внутренним кодом ошибки. Установив такую связь, можно инициировать исключение по имени и указать это имя в условии WHEN
обработчика ошибок.
С директивой EXCEPTION_INIT
условие WHEN
, использованное в предыдущем примере, приводится к следующему виду:
PROCEDURE my_procedure
IS
invalid_month EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_month, −1843);
BEGIN
...
EXCEPTION
WHEN invalid_month THEN
Жесткое кодирование номера ошибки становится излишним; имя ошибки говорит само за себя.
Директива EXCEPTION_INIT
должна располагаться в разделе объявлений блока. Указанное в ней исключение должно быть объявлено либо в том же блоке, либо во внешнем, либо в спецификации пакета. Синтаксис директивы в анонимном блоке:
DECLARE
имя_исключения EXCEPTION;
PRAGMA EXCEPTION_INIT (имя_исключения, целое_число);
Здесь имя_исключения
— имя исключения, объявляемого программистом, а целое_число
— номер ошибки Oracle, которую следует связать с данным исключением. Номером ошибки может служить любое число со следующими ограничениями:
- Номер ошибки не может быть равен –1403 (один из двух кодов ошибок
NO_DATA_FOUND
). Если вы по какой-либо причине захотите связать свое именованное исключение с этой ошибкой, передайте директивеEXCEPTION_INIT
значение 100. - Номер ошибки не может быть равен 0 или любому положительному числу, кроме 100.
- Номер ошибки не может быть отрицательным числом, меньшим –1 000 000.
Рассмотрим пример возможного объявления исключения. В приведенном ниже программном коде я объявляю и связываю исключение со следующим номером:
ORA-2292 integrity constraint (OWNER.CONSTRAINT) violated -
child record found.
Ошибка происходит при попытке удаления родительской записи, у которой в таблице имеются дочерние записи (то есть записи с внешним ключом, ссылающимся на родительскую запись):
PROCEDURE delete_company (company_id_in IN NUMBER)
IS
/* Объявление исключения. */
still_have_employees EXCEPTION;
/* Имя исключения связывается с номером ошибки. */
PRAGMA EXCEPTION_INIT (still_have_employees, 2292);
BEGIN
/* Попытка удаления информации о компании. */
DELETE FROM company
WHERE company_id = company_id_in;
EXCEPTION
/* При обнаружении дочерних записей инициируется это исключение! */
WHEN still_have_employees
THEN
DBMS_OUTPUT.PUT_LINE
('Пожалуйста, сначала удалите данные о служащих компании.');
END;
Рекомендации по использованию EXCEPTION_INIT
Директиву EXCEPTION_INIT
целесообразно использовать в двух ситуациях:
- при необходимости присвоить имя безымянному системному исключению, задействованному в программе (следовательно, если в Oracle не определено имя для некоторой ошибки, это еще не означает, что с ней можно работать только по номеру);
- когда нужно присвоить имя специфическому для приложения исключению, инициируемому процедурой
RAISE_APPLICATION_ERROR
(см. далее раздел «Инициирование исключений»). Это позволяет обрабатывать данное исключение по имени, а не по номеру.
В обоих случаях все директивы EXCEPTION_INIT
желательно объединить в пакет, чтобы определения исключений не были разбросаны по всему коду приложения. Допустим, вы интенсивно используете динамический SQL, и при выполнении запросов часто возникает ошибка «invalid column name» (неверное имя столбца). Запоминать код ошибки не хочется, но и определять директивы имя для исключения в 20 разных программах тоже неразумно. Поэтому имеет смысл определить собственные «системные исключения» в отдельном пакете для работы с динамическим SQL:
CREATE OR REPLACE PACKAGE dynsql
IS
invalid_table_name EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_table_name, -903);
invalid_identifier EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_identifier, -904);
Теперь перехват этих ошибок в программе может производиться следующим образом:
WHEN dynsql.invalid identifier THEN ...
Аналогичный подход рекомендуется использовать при работе с кодами ошибок –20NNN, передаваемыми процедуре RAISE_APPLICATION_ERROR
(см. далее в этой заметке моего блога). Создайте пакет, в котором этим кодам будут присваиваться имена. Он может выглядеть примерно так:
PACKAGE errnums
IS
en_too_young CONSTANT NUMBER := -20001;
exc_too_young EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_too_young, -20001);
en_sal_too_low CONSTANT NUMBER := -20002;
exc_sal_too_low EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_sal_too_low , -20002);
END errnums;
При наличии такого пакета можно использовать код следующего вида, не указывая номер ошибки в коде:
PROCEDURE validate_emp (birthdate_in IN DATE)
IS
min_years CONSTANT PLS_INTEGER := 18;
BEGIN
IF ADD_MONTHS (SYSDATE, min_years * 12 * -1) < birthdate_in
THEN
RAISE_APPLICATION_ERROR
(errnums.en_too_young,
'Возраст работника должен быть не менее ' || min_years || ' лет.');
END IF;
END;
Именованные системные исключения
В Oracle для относительно небольшого количества исключений определены стандартные имена, задаваемые директивой компилятора EXCEPTION_INIT
во встроенных пакетах. Самые важные и часто применяемые из них определены в пакете STANDARD
. Так как это один из двух используемых по умолчанию пакетов PL/SQL, на определенные в нем исключения можно ссылаться без префикса с именем пакета. Например, если потребуется инициировать в программе исключение NO_DATA_FOUND
, это можно сделать любой из следующих команд:
WHEN NO_DATA_FOUND THEN
WHEN STANDARD.NO_DATA_FOUND THEN
Определения стандартных именованных исключений встречаются и в других встроенных пакетах — например, в пакете DBMS_LOB
, предназначенном для работы с большими объектами. Пример одного такого определения из указанного пакета:
invalid_argval EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_argval, -21560);
Поскольку пакет DBMS_LOB
не используется по умолчанию, перед ссылкой на это исключение необходимо указать имя пакета:
WHEN DBMS_LOB.invalid_argval THEN...
Многие исключения, определенные в пакете STANDARD
, перечислены в табл. 1. Для каждого из них приводится номер ошибки Oracle, значение, возвращаемое при вызове SQLCODE
(встроенная функция SQLCODE
, которая возвращает текущий код ошибки — см. раздел «Встроенные функции ошибок»), и краткое описание. Значение, возвращаемое SQLCODE
, совпадает с кодом ошибки Oracle, с одним исключением: определяемый стандартом ANSI код ошибки NO_DATA_FOUND
равен 100.
Имя исключения/Ошибка Oracle/SQLCODE | Описание |
CURSOR_ALREADY_OPEN ORA-6511 SQLCODE = –6511 | Попытка открытия курсора, который был открыт ранее. Перед повторным открытием курсор необходимо сначала закрыть |
DUP_VAL_ON_INDEX ORA-00001 SQLCODE = −1 | Команда INSERT или UPDATE пытается сохранить повторяющиеся значения в столбцах, объявленных с ограничением UNIQUE |
INVALID_CURSOR ORA-01001 SQLCODE = −1001 | Ссылка на несуществующий курсор. Обычно ошибка встречается при попытке выборки данных из неоткрытого курсора или закрытия курсора до его открытия |
INVALID_NUMBER ORA-01722 SQLCODE = −1722 | Выполняемая SQL-команда не может преобразовать символьную строку в число. Это исключение отличается от VALUE_ERROR тем, что оно инициируется только из SQL-команд |
LOGIN_DENIED ORA-01017 SQLCODE = −1017 | Попытка программы подключиться к СУБД Oracle с неверным именем пользователя или паролем. Исключение обычно встречается при внедрении кода PL/SQL в язык 3GL |
NO_DATA_FOUND ORA-01403 SQLCODE = +100 | Исключение инициируется в трех случаях: (1) при выполнении инструкции SELECT INTO (неявный курсор), которая не возвращает ни одной записи; (2) при ссылке на неинициализированную запись локальной таблицы PL/SQL; (3) при попытке выполнить операцию чтения после достижения конца файла при использовании пакета UTL_FILE |
NOT_LOGGED ON ORA-01012 SQLCODE = −1012 | Программа пытается обратиться к базе данных (обычно из инструкции DML) до подключения к СУБД Oracle |
PROGRAM_ERROR ORA-06501 SQLCODE = −6501 | Внутренняя программная ошибка PL/SQL. В сообщении об ошибке обычно предлагается обратиться в службу поддержки Oracle |
STORAGE_ERROR ORA-06500 SQLCODE = −6500 | Программе PL/SQL не хватает памяти или память по какой-то причине повреждена |
TIMEOUT_ON_RESOURCE ORA-00051 SQLCODE = −51 | Тайм-аут СУБД при ожидании ресурса |
TOO_MANY_ROWS ORA-01422 SQLCODE = −1422 | Команда SELECT INTO возвращает несколько записей, хотя должна возвращать лишь одну (в таких случаях инструкция SELECT включается в явное определение курсора, а записи выбираются по одной) |
TRANSACTION_BACKED_OUT ORA-00061 SQLCODE = −61 | Удаленная часть транзакции отменена либо при помощи явной инструкции ROLLBACK , либо в результате какого-то другого действия (например, неудачного выполнения команды SQL или DML в удаленной базе данных) |
VALUE_ERROR ORA-06502 SQLCODE = −6502 | Ошибка связана с преобразованием, усечением или проверкой ограничений числовых или символьных данных. Это общее и очень распространенное исключение. Если подобная ошибка содержится в инструкции SQL или DML, то в блоке PL/SQL инициируется исключение INVALID_NUMBER |
ZERO_DIVIDE ORA-01476 SQLCODE = −1476 | Попытка деления на ноль |
Рассмотрим пример использования этой таблицы исключений. Предположим, ваша программа инициирует необрабатываемое исключение для ошибки ORA-6511. Заглянув в таблицу, вы видите, что она связана с исключением CURSOR_ALREADY_OPEN
. Найдите блок PL/SQL, в котором произошла ошибка, и добавьте в него обработчик исключения
CURSOR_ALREADY_OPEN:
EXCEPTION
WHEN CURSOR_ALREADY_OPEN
THEN
CLOSE my_cursor;
END;
Конечно, еще лучше было бы проанализировать весь программный код и заранее определить, какие из стандартных исключений в нем могут инициироваться. В таком случае вы сможете решить, какие исключения следует обрабатывать конкретно, какие следует включить в конструкцию WHEN OTHERS
(см. далее), а какие оставить необработанными.
Область действия исключения
Областью действия исключения называется та часть программного кода, к которой оно относится, то есть блок, где данное исключение может быть инициировано. В следующей таблице указаны области действия исключений четырех разных типов.
Тип исключения | Область действия |
Именованное системное исключение | Исключение является глобальным, то есть не ограничивается каким-то конкретным блоком кода. Системные исключения могут инициироваться и обрабатываться в любом блоке |
Именованное исключение, определяемое программистом | Исключение может инициироваться и обрабатываться только в исполнительном разделе и разделе исключений, входящих в состав блока, где объявлено данное исключение (или в состав любого из вложенных в него блоков). Если исключение определено в спецификации пакета, то его областью действия являются все те программы, владельцы которых обладают для этого пакета привилегией EXECUTE |
Анонимное системное исключение | Исключение может обрабатываться в секции WHEN OTHERS любого раздела исключений PL/SQL. Если присвоить ему имя, то его область действия будет такой же, как у именованного исключения, определяемого программистом |
Анонимное исключение, определяемое программистом | Исключение определяется в вызове процедуры RAISE_APPLICATION_ERROR , а затем передается в вызывающую программу |
Рассмотрим пример исключения overdue_balance
, объявленного в процедуре check_account
(таким образом, область его действия ограничивается указанной процедурой):
PROCEDURE check_account (company_id_in IN NUMBER)
IS
overdue_balance EXCEPTION;
BEGIN
... исполняемые команды ...
LOOP
...
IF ... THEN
RAISE overdue_balance;
END IF;
END LOOP;
EXCEPTION
WHEN overdue_balance THEN ...
END;
С помощью команды RAISE
исключение overdue_balance
можно инициировать в процедуре check_account
, но не в программе, которая ее вызывает. Например, для следующего анонимного блока компилятор выдает ошибку:
DECLARE
company_id NUMBER := 100;
BEGIN
check_account (100);
EXCEPTION
WHEN overdue_balance /* В PL/SQL такая ссылка недопустима. */
THEN ...
END;
PLS-00201: identifier "OVERDUE_BALANCE" must be declared
Для приведенного выше анонимного блока процедура check_account
является «черным ящиком». Все объявленные в ней идентификаторы, в том числе идентификаторы исключения, не видны для внешнего программного кода.
Инициирование исключений
Исключение может быть инициировано приложением в трех случаях:
- Oracle инициирует исключение при обнаружении ошибки;
- приложение инициирует исключение командой
RAISE
; - исключение инициируется встроенной процедурой
RAISE_APPLICATION_ERROR
.
Как Oracle инициирует исключения, вы уже знаете. Теперь давайте посмотрим, как это может сделать программист.
Команда RAISE
Чтобы программист имел возможность самостоятельно инициировать именованные исключения, в Oracle поддерживается команда RAISE
. С ее помощью можно инициировать как собственные, так и системные исключения. Команда имеет три формы:
RAISE имя_исключения;
RAISE имя_пакета.имя_исключения;
RAISE;
Первая форма (без имени пакета) может инициировать исключения, определенные в текущем блоке (или в содержащем его блоке), а также системные исключения, объявленные в пакете STANDARD
. Далее приводятся два примера, в первом из которых инициируется исключение, определенное программистом:
DECLARE
invalid_id EXCEPTION; -- Все идентификаторы должны начинаться с буквы 'X'.
id_value VARCHAR2(30);
BEGIN
id_value := id_for ('SMITH');
IF SUBSTR (id_value, 1, 1) != 'X'
THEN
RAISE invalid_id;
END IF;
...
END;
При необходимости вы всегда можете инициировать системное исключение:
BEGIN
IF total_sales = 0
THEN
RAISE ZERO_DIVIDE; -- Определено в пакете STANDARD
ELSE
RETURN (sales_percentage_calculation (my_sales, total_sales));
END IF;
END;
Если исключение объявлено в пакете (но не в STANDARD
) и инициируется извне, имя исключения необходимо уточнить именем пакета:
IF days_overdue (isbn_in, borrower_in) > 365
THEN
RAISE overdue_pkg.book_is_lost;
END IF;
Третья форма RAISE
не требует указывать имя исключения, но используется только в условии WHEN
раздела исключений. Ее синтаксис предельно прост:
RAISE;
Используйте эту форму для повторного инициирования (передачи) перехваченного исключения:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- Используем общий пакет для сохранений всей контекстной
-- информации: код ошибки, имя программы и т. д.
errlog.putline (company_id_in);
-- А теперь исключение NO_DATA_FOUND передается
-- в родительский блок без обработки.
RAISE;
Эта возможность особенно полезна в тех случаях, когда информацию об ошибке нужно записать в журнал, а сам процесс обработки возложить на родительский блок. Таким образом выполнение родительских блоков завершается без потери информации об ошибке.
Процедура RAISE_APPLICATION_ERROR
Для инициирования исключений, специфических для приложения, Oracle предоставляет процедуру RAISE_APPLICATION_ERROR
(определенную в используемом по умолчанию пакете DBMS_STANDARD
). Ее преимущество перед командой RAISE (которая тоже может инициировать специфические для приложения явно объявленные исключения) заключается в том, что она позволяет связать с исключением сообщение об ошибке.
При вызове этой процедуры выполнение текущего блока PL/SQL прекращается, а любые изменения аргументов OUT
и IN OUT
(если таковые имеются) отменяются. Изменения, внесенные в глобальные структуры данных (с помощью команды INSERT, UPDATE, MERGE
или DELETE
), такие как переменные пакетов и объекты баз данных, не отменяются. Для отката DML-команд необходимо явно указать в разделе обработки исключений команду ROLLBACK
.
Заголовок этой процедуры (определяемый в пакете DBMS_STANDARD
) выглядит так:
PROCEDURE RAISE_APPLICATION_ERROR (
num binary_integer,
msg varchar2,
keeperrorstack boolean default FALSE);
Здесь num
— номер ошибки из диапазона от –20 999 до –20 000 (только представьте: все остальные отрицательные числа Oracle резервирует для собственных исключений!); msg
— сообщение об ошибке, длина которого не должна превышать 2048 символов (символы, выходящие за эту границу, игнорируются); аргумент keeperrorstack
указывает, хотите ли вы добавить ошибку к уже имеющимся в стеке (TRUE
), или заменить существующую ошибку (значение по умолчанию — FALSE
).
Oracle выделяет диапазон номеров от –20 999 до –20 000 для пользовательских ошибок, но учтите, что в некоторых встроенных пакетах, в том числе в DBMS_OUTPUT
и DBMS_DESCRIBE
, номера от –20 005 до –20 000 все равно присваиваются системным ошибкам. За дополнительной информацией обращайтесь к документации пакетов.
Рассмотрим пример полезного применения этой встроенной процедуры. Допустим, мы хотим, чтобы сообщения об ошибках выдавались пользователям на разных языках. Создадим для них таблицу error_table
и определим в ней язык каждого сообщения значением столбца string_language
. Затем создается процедура, которая генерирует заданную ошибку, загружая соответствующее сообщение из таблицы с учетом языка текущего сеанса:
PROCEDURE raise_by_language (code_in IN PLS_INTEGER)
IS
l_message error_table.error_string%TYPE;
BEGIN
SELECT error_string
INTO l_message
FROM error_table
WHERE error_number = code_in
AND string_language = USERENV ('LANG');
RAISE_APPLICATION_ERROR (code_in, l_message);
END;
Обработка исключений
Как только в программе возникает исключение, нормальное выполнение блока PL/SQL останавливается, и управление передается в раздел исключений. Затем исключение либо обрабатывается обработчиком исключений в текущем блоке PL/SQL, либо передается в родительский блок.
Чтобы обработать или перехватить исключение, нужно написать для него обработчик. Обработчики исключений располагаются после всех исполняемых команд блока, но перед завершающим ключевым словом END
. Начало раздела исключений отмечает ключевое слово EXCEPTION
:
DECLARE
... объявления ...
BEGIN
... исполняемые команды ...
[ EXCEPTION
... обработчики исключений ... ]
END;
Синтаксис обработчика исключений может быть таким:
WHEN имя_исключения [ OR имя_исключения ... ]
THEN
исполняемые команды
или таким:
WHEN OTHERS
THEN
исполняемые команды
В одном разделе исключений может быть несколько их обработчиков. Структура обработчиков напоминает структуру условной команды CASE
.
Свойство | Описание |
EXCEPTION WHEN NO_DATA_FOUND THEN исполняемые_команды1; | Если инициировано исключение NO_DATA_FOUND , выполнить первый набор команд |
WHEN payment_overdue THEN исполняемые_команды2; | Если просрочена оплата, выполнить второй набор команд |
WHEN OTHERS THEN исполняемые_команды3; END; | Если инициировано иное исключение, выполнить третий набор команд |
Если имя, заданное в условии WHEN
, совпадает с инициированным исключением, то это исключение обрабатывается соответствующим набором команд. Обратите внимание: исключения перехватываются по именам, а не по кодам ошибок. Но если инициированное исключение не имеет имени или его имя не соответствует ни одному из имен, указанных в условиях WHEN
, тогда оно обрабатывается командами, заданными в секции WHEN OTHERS
(если она имеется). Любая ошибка может быть перехвачена только одним обработчиком исключений. После выполнения команд обработчика управление сразу же передается из текущего блока в родительский или вызывающий блок.
Секция WHEN OTHERS
не является обязательной. Когда она отсутствует, все необработанные исключения немедленно передаются в родительский блок, если таковой имеется. Секция WHEN OTHERS
должна быть последним обработчиком исключений в блоке. Если разместить после нее еще одну секцию WHEN
, компилятор выдаст сообщение об ошибке.
Встроенные функции ошибок
Прежде чем переходить к изучению тонкостей обработки ошибок, мы сначала вкратце познакомимся со встроенными функциями Oracle, предназначенными для идентификации, анализа и реагирования на ошибки, возникающие в приложениях PL/SQL.
SQLCODE
Функция SQLCODE
возвращает код ошибки последнего исключения, инициированного в блоке. При отсутствии ошибок SQLCODE
возвращает 0. Кроме того, SQLCODE
возвращает 0 при вызове за пределами обработчика исключений.
База данных Oracle поддерживает стек значений SQLCODE
. Допустим, к примеру, что функция FUNC инициирует исключение VALUE_ERROR
(–6502). В разделе исключений FUNC
вызывается процедура PROC
, которая инициирует исключение DUP_VAL_ON_INDEX
(–1). В разделе исключений PROC
функция SQLCODE
возвращает значение –1. Но когда управление передается в раздел исключений FUNC
, SQLCODE
будет возвращать –6502.
SQLERRM
Функция SQLERRM
возвращает сообщение об ошибке для заданного кода ошибки. Если вызвать SQLERRM
без указания кода ошибки, функция вернет сообщение, связанное со значением, возвращаемым SQLCODE
. Например, если SQLCODE
возвращает 0, функция SQLERRM
вернет следующую строку:
ORA-0000: normal, successful completion
Если же SQLCODE
возвращает 1 (обобщенный код ошибки для исключения, определяемого пользователем), SQLERRM
вернет строку:
User-Defined Exception
Пример вызова SQLERRM
для получения сообщения об ошибке для конкретного кода:
1 BEGIN
2 DBMS_OUTPUT.put_line (SQLERRM (-1403));
3* END;
SQL> /
ORA-01403: no data found
Максимальная длина строки, возвращаемой SQLERRM
, составляет 512 байт (в некоторых ранних версиях Oracle — 255 байт). Из-за этого ограничения Oracle Corporation рекомендует вызывать функцию DBMS_UTILITY
.FORMAT_ERROR_STACK
, чтобы гарантировать вывод полной строки (эта встроенная функция не усекает текст до 2000 байт).
DBMS_UTILITY.FORMAT_ERROR_STACK
Эта встроенная функция, как и SQLERRM
, возвращает сообщение, связанное с текущей ошибкой (то есть значение, возвращаемое SQLCODE
). Ее отличия от SQLERRM
:
- Она возвращает до 1899 символов сообщения, что позволяет избежать проблем с усечением.
- Этой функции не может передаваться код ошибки; соответственно, она не может использоваться для получения сообщения, соответствующего произвольному коду.
Как правило, эта функция вызывается в логике обработчика исключения для получения полного сообщения об ошибке.
Хотя в имя функции входит слово «stack», она не возвращает информацию о стеке ошибок, приведшем к строке, в которой изначально была инициирована ошибка. Эту задачу решает DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Эта функция, появившаяся в Oracle10g, возвращает отформатированную строку с содержимым стека программ и номеров строк. Ее выходные данные позволяют отследить строку, в которой изначально была инициирована ошибка.
Тем самым заполняется весьма существенный пробел в функциональности PL/SQL. В Oracle9i и предшествующих версиях после обработки исключения в блоке PL/ SQL было невозможно определить строку, в которой произошла ошибка (возможно, самая важная информация для разработчика). Если программист хотел получить эту информацию, он должен был разрешить прохождение необработанного исключения, чтобы полная трассировочная информация ошибки была выведена на экран. Ситуация более подробно описана в следующем разделе.
DBMS_UTILITY.FORMAT_CALL_STACK
Функция возвращает отформатированную строку со стеком вызовов в приложении PL/SQL. Практическая полезность функции не ограничивается обработкой ошибок; она также пригодится для трассировки выполнения вашего кода.
В Oracle Database 12c появился пакет UTL_CALL_STACK
, который также предоставляет доступ к стеку вызовов, стеку ошибок и информации обратной трассировки.
Подробнее о DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Функцию DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
следует вызывать в обработчике исключения. Она выводит содержимое стека выполнения в точке инициирования исключения. Таким образом, вызов DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
в разделе исключений на верхнем уровне стека позволит узнать, где именно в стеке вызовов произошла ошибка. Рассмотрим следующий сценарий: мы определяем процедуру proc3
, которая вызывает процедуру proc2
, а последняя, в свою очередь, вызывает proc1
. Процедура proc1 инициирует исключение:
CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
DBMS_OUTPUT.put_line ('выполнение proc1');
RAISE NO_DATA_FOUND;
END;
/
CREATE OR REPLACE PROCEDURE proc2 IS
l_str VARCHAR2 (30) := 'вызов proc1';
BEGIN
DBMS_OUTPUT.put_line (l_str);
proc1;
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
DBMS_OUTPUT.put_line ('вызов proc2');
proc2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Стек ошибок верхнего уровня:');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/
Единственной программой с обработчиком ошибок является внешняя процедура proc3
. Вызов функции трассировки включен в обработчик WHEN OTHERS
процедуры proc3
. При выполнении этой процедуры будет получен следующий результат:
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace');
3 proc3;
4 END;
5 /
Proc3 -> Proc2 -> Proc1 backtrace
вызов proc2
вызов proc1
выполнение proc1
Error stack at top level:
ORA-06512: at "SCOTT.PROC1", line 4
ORA-06512: at "SCOTT.PROC2", line 5
ORA-06512: at "SCOTT.PROC3", line 4
Как видите, функция трассировки выводит в начале стека номер строки proc1
, в которой произошла исходная ошибка.
Часто исключение происходит где-то в глубине стека вызовов. Если вы хотите, чтобы оно было передано во внешний блок PL/SQL, вероятно, вам придется заново инициировать его в каждом обработчике стека блоков. Функция DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
выдает трассировку исполнения вплоть до последней команды RAISE
в сеансе пользователя. Учтите, что вызов RAISE
для конкретного исключения или повторное инициирование текущего исключения приводит к инициализации стека, выдаваемого DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
. Таким образом, если вы хотите использовать эту функцию, возможны два пути:
- Вызовите функцию в разделе исключений блока, в котором была инициирована ошибка. Это позволит вам получить (и сохранить в журнале) номер ошибки, даже если исключение было заново инициировано в дальнейшей позиции стека.
- Обойдите обработчики исключений в промежуточных программах вашего стека и вызовите функцию в разделе исключений внешней программы в стеке.
Только номер строки, пожалуйста
В реальном приложении трассировка ошибок может быть очень длинной. Как правило, специалиста, занимающегося отладкой или поддержкой, не интересует весь стек — достаточно только последнего элемента. Возможно, разработчику приложения стоит вывести эту важную информацию, чтобы пользователь мог немедленно и точно описать суть проблемы группе поддержки.
В такой ситуации необходимо разобрать строку с данными трассировки и извлечь из нее последний элемент. Я написал для этого специальную программу и оформил ее в пакет BT
. В этом пакете реализован простой, понятный интерфейс:
PACKAGE bt
IS
TYPE error_rt IS RECORD (
program_owner all_objects.owner%TYPE
, program_name all_objects.object_name%TYPE
, line_number PLS_INTEGER
);
FUNCTION info (backtrace_in IN VARCHAR2)
RETURN error_rt;
PROCEDURE show_info (backtrace_in IN VARCHAR2);
END bt;
Тип записи error_rt
содержит отдельное поле для каждого возвращаемого элемента трассировки (владелец программного модуля, имя программного модуля и номер строки в программе). Затем вместо того, чтобы вызывать функцию трассировки в каждом разделе исключения и разбирать ее результаты, я вызываю функцию bt.info
и вывожу конкретную информацию об ошибке.
Полезные применения SQLERRM
Вы можете использовать DBMS_UTILITY
.FORMAT_ERROR_STACK
вместо SQLERRM
, но это не означает, что функция SQLERRM
совершенно неактуальна. В частности, она поможет вам получить ответ на следующие вопросы:
- Является ли заданное число действительным кодом ошибки Oracle?
- Какое сообщение соответствует коду ошибки?
Как упоминалось ранее в нашей статье, функция SQLERRM
возвращает сообщение об ошибке для заданного кода. Но если передать SQLERRM
недействительный код, исключение не инициируется. Вместо этого возвращается строка в одном из двух форматов:
- Если число отрицательно:
ORA-NNNNN: Message NNNNN not found; product=RDBMS; facility=ORA
- Если число положительно или меньше −65535:
-N: non-ORACLE exception
Этим обстоятельством можно воспользоваться для построения функций, возвращающих точную информацию о том коде, с которым вы работаете в настоящее время. Ниже приведена спецификация пакета с этими программами:
PACKAGE oracle_error_info
IS
FUNCTION is_app_error (code_in IN INTEGER)
RETURN BOOLEAN;
FUNCTION is_valid_oracle_error (
code_in IN INTEGER
, app_errors_ok_in IN BOOLEAN DEFAULT TRUE
, user_error_ok_in IN BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN;
PROCEDURE validate_oracle_error (
code_in IN INTEGER
, message_out OUT VARCHAR2
, is_valid_out OUT BOOLEAN
, app_errors_ok_in IN BOOLEAN DEFAULT TRUE
, user_error_ok_in IN BOOLEAN DEFAULT TRUE
);
END oracle_error_info;
Объединение нескольких исключений в одном обработчике
В одном условии WHEN
можно оператором OR
объединить несколько исключений — подобно тому, как этим оператором объединяются логические выражения:
WHEN invalid_company_id OR negative_balance
THEN
В одном обработчике также можно комбинировать имена пользовательских и системных исключений:
WHEN balance_too_low OR ZERO_DIVIDE OR DBMS_LDAP.INVALID_SESSION
THEN
Впрочем, применять оператор AND
в такой комбинации нельзя, потому что в любой момент времени может быть инициировано только одно исключение.
Необработанные исключения
Исключение, инициированное в программе, но не обработанное в соответствующем разделе текущего или родительского блока PL/SQL, называется необработанным. PL/ SQL возвращает сообщение об ошибке, вызвавшей необработанное исключение, в ту среду, где была запущена данная программа. Эта среда (ею может быть SQL*Plus. Oracle Forms, программа на языке Java и т. д.) действует по ситуации. В частности, SQL*Plus осуществляет откат всех DML-инструкций, выполненных в родительском блоке.
Одним из важнейших моментов, связанных с проектированием архитектуры приложения, является вопрос о том, разрешается ли в нем использовать необработанные исключения. Такие исключения разными средами обрабатываются по-разному, и не всегда это делается корректно. Если ваша программа PL/SQL вызывается не из PL/SQL-среды, в ее «самом внешнем» блоке можно запрограммировать следующие действия:
- перехват всех исключений, которые могли быть переданы до текущей точки;
- запись информации об ошибке в журнал, с тем чтобы впоследствии ее мог проанализировать разработчик;
- возврат кода состояния, описания и другой информации, необходимой управляющей среде для выбора оптимального варианта действий.
Передача необработанного исключения
Блок, в котором может быть инициировано исключение, определяется правилами области действия исключений. В программе инициированное исключение распространяется в соответствии с определенными правилами.
Сначала PL/SQL ищет обработчик исключения в текущем блоке (анонимном блоке, процедуре или функции). Если такового нет, исключение передается в родительский блок. Затем PL/SQL пытается обработать исключение, инициировав его еще раз в родительском блоке. И так происходит в каждом внешнем по отношению к другому блоке до тех пор, пока все они не будут исчерпаны (рис. 2). После этого PL/SQL возвращает необработанное исключение в среду приложения, выполнившего «самый внешний» блок PL/SQL. И только теперь исключение может прервать выполнение основной программы.
Рис. 2. Передача исключений во вложенных блоках PL/SQL
Потеря информации об исключении
Структура процесса обработки локальных, определяемых программистом исключений в PL/SQL такова, что можно легко потерять информацию об исключении (то есть о том, какая именно произошла ошибка). Пример:
BEGIN
<<local_block>>
DECLARE
case_is_not_made EXCEPTION;
BEGIN
...
END local_block;
Допустим, мы забыли включить в этот блок раздел исключений. Область действия исключения case_is_not_made
ограничена блоком local_block
. Если исключение не обрабатывается в данном блоке, оно передается в родительский, где нет никакой информации о нем. Известно только то, что произошла ошибка, а какая именно — неизвестно. Ведь все пользовательские исключения имеют один и тот же номер ошибки 1 и одно и то же сообщение «User Defined Exception» — если только вы не воспользуетесь директивой EXCEPTION_INIT
, чтобы связать с объявленным исключением другой номер, и не присвоите ему другое сообщение об ошибке при вызове RAISE_APPLICATION_ERROR
.
Таким образом, локально объявленные (и инициированные) исключения всегда следует обрабатывать по имени.
Примеры передачи исключения
Рассмотрим несколько примеров передачи исключений через внешние блоки. На рис. 3 показано, как исключение too_many_faults
, инициированное во внутреннем блоке, обрабатывается в следующем — внешнем — блоке. Внутренний блок содержит раздел исключений, так что PL/SQL сначала проверяет, обрабатывается ли в этом разделе инициированное исключение too_many_faults
.
Рис. 3. Передача исключений во вложенных блоках PL/SQL
А поскольку оно не обрабатывается, PL/SQL закрывает этот блок и инициирует исключение too_many_faults
во внешнем блоке, обозначенном на рисунке как вложенный блок 1. (Используемые команды, расположенные после вложенного блока 2, не выполняются.) Затем просматривается раздел исключений этого блока с целью поиска обработчика исключения too_many_faults
, который обрабатывает его и передает управление процедуре list_my_faults
.
Обратите внимание: если исключение NO_DATA_FOUND
будет инициировано в «самом внутреннем» блоке, то оно будет обработано в разделе исключений этого же блока. Затем управление передается во вложенный блок 1 и будут выполнены исполняемые команды, расположенные после вложенного блока 2.
На рис. 4 представлен пример обработки в «самом внешнем» блоке исключения, инициированного во внутреннем блоке. В изображенной ситуации раздел исключений присутствует только во внешнем блоке, поэтому когда во вложенном блоке 2 инициируется исключение too_many_faults
, PL/SQL прекращает выполнение этого блока и инициирует данное исключение в его родительском блоке, то есть вложенном блоке 1. Но поскольку и у него нет раздела исключений, управление передается «самому внешнему» блоку, процедуре list_my_faults
. В этой процедуре имеется раздел исключений, поэтому PL/ SQL проверяет его, находит обработчик исключения too_many_faults
, выполняет имеющийся там код и передает управление программе, вызвавшей процедуру list_my_faults
.
Рис. 4. Исключение, инициированное во вложенном блоке,
обрабатывается в «самом внешнем» блоке
Продолжение выполнения после исключений
Когда в блоке PL/SQL инициируется исключение, нормальная последовательность выполнения программы прерывается, а управление передается в раздел исключений. Вернуться к исполняемому разделу блока после возникновения в нем исключения уже не удастся. Впрочем, в некоторых ситуациях требуется именно это — продолжить выполнение программы после обработки исключения.
Рассмотрим следующий сценарий: требуется написать процедуру, которая применяет серию операций DML к разным таблицам (удаление из одной таблицы, обновление другой, вставка в последнюю таблицу). На первый взгляд код мог бы выглядеть примерно так:
PROCEDURE change_data IS
BEGIN
DELETE FROM employees WHERE ... ;
UPDATE company SET ... ;
INSERT INTO company_history SELECT * FROM company WHERE ... ;
END;
Безусловно, процедура содержит все необходимые команды DML. Однако одно из требований к программе заключается в том, что при последовательном выполнении этих команд они должны быть логически независимы друг от друга. Другими словами, даже если при выполнении DELETE
произойдет сбой, программа должна выполнить UPDATE
и INSERT
.
В текущей версии change_data
ничто не гарантирует, что программа хотя бы попытается выполнить все три операции DML. Если при выполнении DELETE
произойдет исключение, например, то выполнение всей программы прервется, а управление будет передано в раздел исключений (если он имеется). Остальные команды SQL при этом выполняться не будут.
Как обеспечить обработку исключения без прерывания программы? Для этого DELETE
следует поместить в собственный блок PL/SQL. Рассмотрим следующую версию программы change_data
:
PROCEDURE change_data
IS
BEGIN
BEGIN
DELETE FROM employees WHERE ... ;
EXCEPTION
WHEN OTHERS THEN log_error;
END;
BEGIN
UPDATE company SET ... ;
EXCEPTION
WHEN OTHERS THEN log_error;
END;
BEGIN
INSERT INTO company_history SELECT * FROM company WHERE ... ;
EXCEPTION
WHEN OTHERS THEN log_error;
END;
END;
В новом варианте программы, если при выполнении DELETE
произойдет исключение, управление немедленно передается в раздел исключений. Но поскольку команда DELETE
теперь находится в собственном блоке, она может иметь собственный раздел исключений. Условие WHEN OTHERS
этого раздела обрабатывает ошибку без повторного инициирования этой или другой ошибки, после чего управление возвращается за пределы блока DELETE
внешней процедуре change_data
. Так как «активное» исключение отсутствует, выполнение продолжается во внешнем блоке со следующей команды процедуры. Программа входит в новый анонимный блок для команды UPDATE
. Если при выполнении UPDATE
произойдет ошибка, она будет перехвачена условием WHEN OTHERS
раздела исключений UPDATE
. Далее управление будет возвращено процедуре change_data
, которая перейдет к выполнению команды INSERT
(также содержащейся в собственном блоке).
На рис. 5 показано, как выполняется этот процесс для двух последовательно выполняемых команд DELETE
.
Рис. 5. Последовательное выполнение DELETE с разными областями действия
Подведем итог: исключение, инициированное в исполняемом разделе, всегда обрабатывается в текущем блоке (при наличии подходящего обработчика). Любую команду можно заключить в «виртуальный блок», заключив ее между ключевыми словами BEGIN
и END
с определением раздела EXCEPTION
. Это позволяет ограничить область действия сбоев в программе посредством определения «буферных» анонимных блоков.
Эту стратегию можно развить с выделением изолируемого кода в отдельные процедуры и функции. Конечно, именованные блоки PL/SQL тоже могут иметь собственные разделы исключений и предоставлять ту же защиту от общих сбоев. Важнейшее преимущество процедур и функций заключается в том, что они скрывают все команды BEGIN-EXCEPTION-END
от основной программы. Программа лучше читается, код проще сопровождать и повторно использовать в других контекстах.
Существуют и другие способы продолжить выполнение после исключения DML — например, можно использовать конструкцию SAVE EXCEPTIONS
с FORALL
и LOG ERRORS
в сочетании с DBMS_ERRORLOG
.
Написание раздела WHEN OTHERS
Условие WHEN OTHERS
включается в раздел исключений для перехвата всех исключений, не обработанных предшествующими обработчиками. Так как конкретный тип исключения изначально неизвестен, в WHEN OTHERS
очень часто используются встроенные функции для получения информации о возникшей ошибке (такие, как SQLCODE
и DBMS_UTILITY
. FORMAT_ERROR_STACK
).
В сочетании с WHEN OTHERS
функция SQLCODE
представляет средства для обработки разных видов исключений без применения директивы EXCEPTION_INIT
. В следующем примере перехватываются два исключения категории «родитель/потомок», −1 и −2292, и для каждой ситуации выполняется подходящее действие:
PROCEDURE add_company (
id_in IN company.ID%TYPE
, name_in IN company.name%TYPE
, type_id_in IN company.type_id%TYPE
)
IS
BEGIN
INSERT INTO company (ID, name, type_id)
VALUES (id_in, name_in, type_id_in);
EXCEPTION
WHEN OTHERS
THEN
/*
|| Анонимный блок в обработчике исключения позволяет объявить
|| локальные переменные для хранения информации о кодах ошибок.
*/
DECLARE
l_errcode PLS_INTEGER := SQLCODE;
BEGIN
CASE l_errcode
WHEN −1 THEN
-- Дублирующееся значение уникального индекса. Повторяется либо
-- первичный ключ, либо имя. Сообщить о проблеме
-- и инициировать исключение заново.
DBMS_OUTPUT.put_line
( 'идентификатор или имя компании уже используется. ID = '
|| TO_CHAR (id_in)
|| ' name = '
|| name_in
);
RAISE;
WHEN −2291 THEN
-- Родительский ключ не найден. Сообщить о проблеме
-- и инициировать исключение заново.
DBMS_OUTPUT.put_line (
'Недопустимый идентификатор типа компании: ' || TO_CHAR (type_id_in));
RAISE;
ELSE
RAISE;
END CASE;
END; -- Конец анонимного блока.
END add_company;
Будьте осторожны при использовании WHEN OTHERS
— этот раздел способен «поглощать» ошибки, скрывая их от внешних блоков и пользователя. А точнее, обращайте внимание на обработчики WHEN OTHERS
, которые не инициируют текущее исключение заново и не заменяют его другим исключением. Если WHEN OTHERS
не передает исключение наружу, внешние блоки вашей программы не узнают о возникшей ошибке.
В Oracle Database 11g появилось новое предупреждение, которое помогает выявлять программы, игнорирующие ошибки или поглощающие их:
PLW-06009: procedure "string" OTHERS handler does not end in RAISE or RAISE_
APPLICATION_ERROR
Пример использования этого предупреждения:
SQL> ALTER SESSION SET plsql_warnings = 'enable:all'
2 /
SQL> CREATE OR REPLACE PROCEDURE plw6009_demo
2 AS
3 BEGIN
4 DBMS_OUTPUT.put_line ('I am here!');
5 RAISE NO_DATA_FOUND;
6 EXCEPTION
7 WHEN OTHERS
8 THEN
9 NULL;
10 END plw6009_demo;
11 /
SP2-0804: Procedure created with compilation warnings
SQL> SHOW ERRORS
Errors for PROCEDURE PLW6009_DEMO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/9 PLW-06009: procedure "PLW6009_DEMO" OTHERS handler does not end
in RAISE or RAISE_APPLICATION_ERROR
Построение эффективной архитектуры управления ошибками
Механизм инициирования и обработки ошибок в PL/SQL отличается мощью и гибкостью, но он не лишен недостатков, которые могут создать проблемы для групп разработки, желающих реализовать надежную, последовательную, содержательную архитектуру управления ошибками. В частности, вы столкнетесь со следующими проблемами:
EXCEPTION
— особая разновидность структуры данных PL/SQL. Переменные, объявленные с типомEXCEPTION
, можно только инициировать и обрабатывать. Исключение нельзя передать в аргументе программы, с ним нельзя связать дополнительные атрибуты.- Повторное использование кода обработки исключений сильно затруднено. Из предыдущего пункта непосредственно следует другой факт: раз исключение нельзя передать в аргументе, разработчику приходится копировать код обработчика — конечно, такой способ написания кода никак не назовешь оптимальным.
- Не существует формализованного способа объявления исключений, которые могут инициироваться программой. Например, в Java эта информация становится частью спецификации программы. Как следствие, разработчику приходится обращаться к коду реализации и искать в нем информацию о потенциальных исключениях — или же надеяться на лучшее.
- Oracle не предоставляет средств организации и классификации исключений, относящихся к конкретному приложению, а просто резервирует (в основном) 1000 кодов в диапазоне от −20 999 до −20 000. Управлять этими значениями должен сам разработчик.
Давайте посмотрим, как преодолеть большинство из перечисленных трудностей.
Определение стратегии управления ошибками
Очень важно, чтобы еще до написания кода была выработана последовательная стратегия и архитектура обработки ошибок в приложении. Вот лишь некоторые вопросы, на которые необходимо ответить для этого:
- Как и когда сохранять информацию об ошибках для последующего просмотра и исправления? Куда выводить информацию — в файл, в таблицу базы данных? выводить на экран?
- Как и где сообщать об ошибках пользователю? Какую информацию должен получать пользователь? Как «перевести» часто невразумительные сообщения об ошибках, выдаваемые базой данных, на язык, понятный пользователям?
С этими общими вопросами тесно связаны более конкретные проблемы:
- Следует ли включать раздел обработки исключений в каждый блок PL/SQL?
- Следует ли включать раздел обработки исключений только в блок верхнего уровня или внешние блоки?
- Как организовать управление транзакциями при возникновении ошибок? Сложность обработки исключений отчасти связана с тем, что на все эти вопросы не существует единственно правильного ответа. Все зависит (по крайней мере частично) от архитектуры приложения и режима его использования (например, пакетное выполнение или транзакции, управляемые пользователем). Но если вы сможете ответить на эти вопросы для своего приложения, я рекомендую «запрограммировать» стратегию и правила обработки ошибок в стандартном пакете (см. далее «Стандартизация обработки ошибок»).
Некоторые общие принципы, которые стоит принять во внимание:
- Когда в коде происходит ошибка, получите как можно больше информации о контексте ее возникновения. Избыток информации — лучше, чем ее нехватка. Далее исключение можно передавать во внешние блоки, собирая дополнительную информацию по мере продвижения.
- Избегайте применения обработчиков вида
WHEN
ошибкаTHEN NULL
; (или еще хуже,WHEN OTHERS THEN NULL;
). Возможно, для написания такого хода у вас имеются веские причины, но вы должны твердо понимать, что это именно то, что вам нужно, и документировать такое использование, чтобы о нем знали другие. - Там, где это возможно, используйте механизмы обработки ошибок PL/SQL по умолчанию. Избегайте написания программ, возвращающих коды состояния управляющей среде или вызывающим блокам. Применять коды состояния следует только в одной ситуации: если управляющая среда не способна корректно обрабатывать ошибки Oracle (в таком случае стоит подумать о смене управляющей среды!).
Стандартизация обработки разных типов исключений
Исключение всегда свидетельствует о критической ситуации? Вовсе нет. Некоторые исключения (например, ORA-00600) сообщают о том, что в базе данных возникли очень серьезные низкоуровневые проблемы. Другие исключения, такие как NO_DATA_FOUND
, встречаются так часто, что мы воспринимаем их не как ошибки, а как условную логическую конструкцию («Если строка не существует, то выполнить следующие действия…»). Нужно ли различать эти категории исключений?
Коллеги-программисты научил меня очень полезной системе классификации исключений.
- Преднамеренные исключения. Архитектура кода сознательно использует особенности работы исключения. Это означает, что разработчик должен предвидеть исключение и запрограммировать его обработку. Пример —
UTL_FILE
.GET_LINE
. - Нежелательные исключения. Происходит ошибка, но ее возможность была предусмотрена заранее. Возможно, исключение даже не свидетельствует о возникновении проблемы. Пример команда
SELECT INTO
, инициирующая исключениеNO_DATA_FOUND
. - Непредвиденные исключения. Серьезные ошибки, указывающие на возникновение проблемы в приложении. Пример — команда
SELECT INTO
, которая должна вернуть строку для заданного первичного ключа, но вместо этого инициирует исключениеTOO_MANY
ROWS
.
Давайте поближе познакомимся с примерами всех категорий, а затем поговорим о том, какую пользу вы можете извлечь из знания этих категорий.
Преднамеренные исключения
Разработчики PL/SQL могут использовать процедуру UTL_FILE
.GET_LINE
для чтения содержимого файла по строкам. Когда GET_LINE
выходит за границу файла, инициируется исключение NO_DATA_FOUND
. Так работает эта процедура. Итак, если я хочу прочитать все содержимое файла и сделать «что-то полезное», программа может выглядеть так:
PROCEDURE read_file_and_do_stuff (
dir_in IN VARCHAR2, file_in IN VARCHAR2
)
IS
l_file UTL_FILE.file_type;
l_line VARCHAR2 (32767);
BEGIN
l_file := UTL_FILE.fopen (dir_in, file_in, 'R', max_linesize => 32767);
LOOP
UTL_FILE.get_line (l_file, l_line);
do_stuff;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose (l_file);
more_stuff_here;
END;
У этого цикла есть одна особенность: он не содержит команды EXIT
. Кроме того, в разделе исключений выполняется дополнительная логика приложения (more_stuff_here
). Цикл можно переписать в следующем виде:
LOOP
BEGIN
UTL_FILE.get_line (l_file, l_line);
do_stuff;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
UTL_FILE.flcose (l_file);
more_stuff_here;
Теперь цикл содержит команду EXIT
, но код стал более громоздким.
Подобные конструкции приходится использовать при работе с кодом, намеренно инициирующем исключения в своей архитектуре. Дополнительная информация о том, как следует поступать в подобных случаях, приводится в следующих разделах.
Нежелательные и непредвиденные исключения
Я рассматриваю эти две категории вместе, потому что приводимые примеры (NO_DATA_FOUND
и TOO_MANY_ROWS
) тесно связаны между собой. Предположим, я хочу написать функцию, возвращающую полное имя работника (в формате фамилия запятая имя) для заданного значения первичного ключа. Проще всего это сделать так:
FUNCTION fullname (
employee_id_in IN employees.employee_id%TYPE
)
RETURN VARCHAR2
IS
retval VARCHAR2 (32767);
BEGIN
SELECT last_name || ',' || first_name
INTO retval
FROM employees
WHERE employee_id = employee_id_in;
RETURN retval;
END fullname;
Если вызвать эту программу с кодом работника, отсутствующим в таблице, база данных инициирует исключение NO_DATA_FOUND
. Если же вызвать ее с кодом работника, встречающимся в нескольких строках таблицы, будет инициировано исключение TOO_MANY_ROWS
. Один запрос, два разных исключения — нужно ли рассматривать их одинаково? Вероятно, нет. Описывают ли эти два исключения похожие группы проблем? Давайте посмотрим:
NO_DATA_FOUND
— совпадение не найдено. Исключение может указывать на наличие серьезной проблемы, но не обязательно. Возможно, в большинстве обращений к базе данных совпадение не будет обнаруживаться, и я буду вставлять в базу данные нового работника. В общем, исключение нежелательно, но в данном случае оно даже не указывает на возникновение ошибки.TOO_MANY_ROWS
— в базе данных возникла серьезная проблема с ограничением первичного ключа. Трудно представить себе ситуацию, в которой это было бы нормально или просто «нежелательно». Нет, нужно прервать работу программы и привлечь внимание пользователя к совершенно непредвиденной, критической ошибке.
Как извлечь пользу из этой классификации
Надеюсь, вы согласитесь, что такая классификация полезна. Приступая к построению нового приложения, постарайтесь по возможности определиться со стандартным подходом, который будет применяться вами (и всеми остальными участниками группы) для каждого типа исключений. Затем для каждого исключения (которое необходимо обработать или хотя бы учитывать заранее при написании кода) решите, к какой категории относится, и примените уже согласованный подход. Все это поможет сделать ваш код более последовательным, и повысит эффективность вашей работы. Приведу несколько рекомендаций для трех типов исключений.
- Преднамеренные исключения. Пишите код, учитывающий возможность возникновения таких исключений. Прежде всего постарайтесь избежать размещения логики приложения в разделе исключений. Раздел исключений должен содержать только код, относящийся к обработке ошибки: сохранение информации об ошибке в журнале, повторное инициирование исключения и т. д. Программисты не ожидают увидеть логику приложения в разделе исключений, поэтому им будет намного труднее разобраться в таком коде и обеспечить его сопровождение.
- Нежелательные исключения. Если в каких-то обстоятельствах пользователь кода, инициировавшего исключения, не будет интерпретировать ситуацию как ошибку, не передавайте исключения наружу без обработки. Вместо этого верните значение или флаг состояния, показывающий, что исключение было обработано. Далее пользователь программы может сам решить, должна ли программа завершиться с ошибкой. А еще лучше — почему бы не разрешить стороне, вызывающей вашу программу, решить, нужно ли инициировать исключение, и если не нужно — какое значение должно передаваться для обозначения возникшего исключения?
- Непредвиденные исключения. А теперь начинается самое неприятное. Все непредвиденные ошибки должны быть сохранены в журнале с максимумом возможной контекстной информации, которая поможет понять причины возникновения ошибки. Затем программа должна завершиться с необработанным исключением (обычно тем же), инициированным из программы; для этого можно воспользоваться командой
RAISE
. Исключение заставит вызвавшую программу прервать работу и обработать ошибку.
Коды ошибок, связанные с конкретным приложением
Используя команду RAISE_APPLICATION_ERROR
для инициирования ошибок, относящихся к конкретному приложению, вы несете полную ответственность за управление кодами ошибок и сообщениями. Это быстро становится хлопотным и непростым делом («Так, какой бы код мне выбрать? Пожалуй, –20 774 — вроде бы такого еще не было?»).
Чтобы упростить управление кодами ошибок и предоставить последовательный интерфейс, через который разработчики смогут обрабатывать серверные ошибки, постройте таблицу со всеми используемыми кодами ошибок −20 NNN, сопутствующими именами исключений и сообщениями об ошибках.
Разработчик может просмотреть уже определенные ошибки на экране и выбрать ту из них, которая лучше всего подходит для конкретной ситуации.
Также можно попытаться полностью избегать диапазон −20 NNN для ошибок приложений. Почему бы не воспользоваться положительными числами? Из положительного цело-численного поддиапазона Oracle использует только 1 и 100. Теоретически возможно, что когда-нибудь Oracle будет использовать и другие положительные числа, но это весьма маловероятно. В распоряжении разработчиков остается великое множество кодов ошибок.
В частности, я пошел по этому пути при проектировании Quest Error Manager (QEM) — бесплатной программы управления ошибками. В Quest Error Manager вы можете определять свои ошибки в специальной таблице. Ошибка определяется именем и/ или кодом. Коды ошибок могут быть положительными или отрицательными. Если код ошибки положителен, при инициировании исключения QEM использует команду RAISE_APPLICATION_ERROR
для инициирования обобщенного исключения (обычно −20 000). Информация о текущем коде ошибки приложения встраивается в сообщение об ошибке, которое может быть расшифровано программой-получателем.
Упрощенная реализация этого подхода представлена в пакете обработки ошибок errpkg. pkg
, описанном в следующем разделе блога.
Стандартизация обработки ошибок
Обязательным элементом любого профессионально написанного приложения является надежная и согласованная схема обработки ошибок. Согласованность в этом вопросе важна как для пользователя, так и для разработчика. Если при возникновении ошибки пользователю предоставляется понятная, хорошо структурированная информация, он сможет более подробно рассказать об ошибке службе поддержки и будет более уверенно чувствовать себя при работе с приложением. Если приложение всегда обрабатывает и протоколирует ошибки определенным образом, программистам, занимающимся его поддержкой и сопровождением, будет легче их найти и устранить.
Все кажется вполне очевидным, не так ли? К сожалению, на практике (и особенно в больших группах разработчиков) все происходит несколько иначе. Очень часто каждый разработчик идет своим путем, следуя личным принципам и приемам, сохраняя информацию в произвольно выбранном формате и т. д. Одним словом, без стандартизации отладка и сопровождение приложений оборачиваются сущим кошмаром. Рассмотрим типичный пример:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_msg := 'Нет компании с идентификатором '||TO_CHAR (v_id);
v_err := SQLCODE;
v_prog := 'fixdebt';
INSERT INTO errlog VALUES
(v_err,v_msg,v_prog,SYSDATE,USER);
WHEN OTHERS
THEN
v_err := SQLCODE;
v_msg := SQLERRM;
v_prog := 'fixdebt';
INSERT INTO errlog VALUES
(v_err,v_msg,v_prog,SYSDATE,USER);
RAISE;
На первый взгляд код выглядит вполне разумно. Если компания с заданным идентификатором не найдена, мы получаем значение SQLCODE
, задаем имя программы и сообщение и записываем строку с информацией об ошибке в таблицу ошибок. Выполнение родительского блока продолжается, поскольку ошибка не критична. Если происходит любая другая ошибка, получаем ее код и соответствующее сообщение, задаем имя программы и записываем строку с информацией об ошибке в таблицу ошибок, а затем передаем исключение в родительский блок, чтобы остановить его выполнение (поскольку неизвестно, насколько критична эта ошибка).
Что же здесь не так? Чтобы подробно объяснить суть проблемы, достаточно взглянуть на код. В нем жестко закодированы все действия по обработке ошибок. В результате (1) код получается слишком объемистым, (2) его придется полностью переписывать при изменении схемы обработки ошибок. Обратите внимание еще и на тот факт, что информация об ошибке записывается в таблицу базы данных. Это означает, что запись в журнале становится частью логической транзакции. И если потребуется выполнить откат транзакции, записи в журнале ошибок будут утеряны.
Существует несколько способов избежать потери информации: можно записывать данные в файл или использовать автономные транзакции для сохранения журнала вне основной транзакции. Но как бы то ни было, код в случае его изменения придется исправлять в сотнях разных мест.
А теперь посмотрите, как этот же раздел исключений оформляется при использовании стандартизированного пакета:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
errpkg.record_and_continue (
SQLCODE, 'Нет компании с идентификатором ' || TO_CHAR (v_id));
WHEN OTHERS
THEN
errpkg.record_and_stop;
END;
Такой пакет обработки ошибок скрывает все подробности реализации; вы просто решаете, какая из процедур-обработчиков должна использоваться в конкретном случае, просматривая спецификацию пакета. Если требуется сохранить информацию об ошибке и продолжить работу, вызывается программа record_and_continue
. Если же нужно сохранить информацию об ошибке и прервать выполнение родительского блока, вызывается программа record_and_stop
. Мы не знаем, как эти программы сохраняют информацию об ошибке, как они останавливают работу родительского блока, то есть передают исключение, но для нас это и не важно. Главное, что все происходит так, как определено стандартами приложения.
Это дает вам возможность уделить больше времени разработке более интересных элементов приложения и не заниматься административной рутиной.
Имеется файл errpkg.pkg
с прототипом стандартизированного пакета обработки ошибок. Правда, прежде чем использовать его в приложениях, вам необходимо будет завершить его реализацию; это поможет составить ясное представление о том, как конструируются подобные утилиты.
Вы также можете воспользоваться намного более мощным (и тоже бесплатным) средством обработки ошибок Quest Error Manager. Важнейшая концепция, заложенная в основу QEM, заключается в возможности перехвата и протоколирования экземпляров ошибок, не только ошибок Oracle. QEM состоит из пакета PL/SQL и четырех таблиц для хранения информации об ошибках, возникающих в приложениях.
Работа с «объектами» исключений
Реализация типа данных EXCEPTION
в Oracle имеет свои ограничения, о которых было рассказано выше. Исключение состоит из идентификатора (имени), с которым связывается числовой код и сообщение. Исключение можно инициировать, его можно обработать… и все. Теперь представьте, как та же ситуация выглядит в Java: все ошибки являются производными от единого класса Exception
. Этот класс можно расширить, дополняя его новыми характеристиками, которые вы хотите отслеживать (стек ошибок, контекстные данные и т. д.). Объект, созданный на основе класса Exception, ничем не отличается от любых других объектов Java. Разумеется, он может передаваться в аргументах методов.
PL/SQL не позволяет делать ничего подобного со своими исключениями. Впрочем, этот факт не мешает вам реализовать свой «объект» исключения. Для этого можно воспользоваться объектными типами Oracle или реляционной таблицей, содержащей информацию об ошибке. Независимо от выбранной реализации очень важно различать определение ошибки (код ошибки –1403, имя «данные не найдены», причина — «неявный курсор не нашел ни одной записи») и ее конкретный экземпляр (я попытался найти компанию с указанным именем, ни одной строки не найдено). Иначе говоря, существует всего одно определение исключения NO_DATA_FOUND
, которое может существовать во множестве экземпляров. Oracle не различает эти два представления ошибки, но для нас это безусловно необходимо.
Пример простой иерархии объектов исключений продемонстрирует этот момент. Начнем с базового объектного типа всех исключений:
CREATE TYPE exception_t AS OBJECT (
name VARCHAR2(100),
code INTEGER,
description VARCHAR2(4000),
help_text VARCHAR2(4000),
recommendation VARCHAR2(4000),
error_stack CLOB,
call_stack CLOB,
created_on DATE,
created_by VARCHAR2(100)
)
NOT FINAL;
/
Затем базовый тип исключения расширяется для ошибок динамического SQL посредством добавления атрибута sql_string. При обработке ошибок динамического SQL очень важно сохранить строку, создавшую проблемы, для анализа в будущем:
CREATE TYPE dynsql_exception_t UNDER exception_t (
sql_string CLOB )
NOT FINAL;
/
А вот другой подтип exception_t
, на этот раз относящийся к конкретной сущности приложения — работнику. Исключение, инициируемое для ошибок, относящихся к работникам, будет включать идентификатор работника и внешний ключ нарушенного правила:
CREATE TYPE employee_exception_t UNDER exception_t (
employee_id INTEGER,
rule_id INTEGER );
/
Полная спецификация иерархии объектов ошибок включает методы супертипа исключения, предназначенные для вывода информации об ошибках или ее записи в репозиторий. Вы можете самостоятельно завершить иерархию, определенную в файле exception.ot
.
Если вы не хотите работать с объектными типами, попробуйте использовать подход, использованный мной в QEM: я определяю таблицу определений ошибок (Q$ERROR
) и другую таблицу экземпляров ошибок (Q$ERROR_INSTANCE
), которая содержит информацию о конкретных экземплярах ошибок. Все контекстные данные экземпляра ошибки сохраняются в таблице Q$ERROR_CONTEXT
.
Пример кода, который мог бы быть написан для QEM API:
WHEN DUP_VAL_ON_INDEX
THEN
q$error_manager.register_error (
error_name_in => 'DUPLICATE-VALUE'
,err_instance_id_out => l_err_instance_id
);
q$error_manager.add_context (
err_instance_id_in => l_err_instance_id
,name_in => 'TABLE_NAME', value_in => 'EMPLOYEES'
);
q$error_manager.add_context (
err_instance_id_in => l_err_instance_id
,name_in => 'KEY_VALUE', value_in => l_employee_id
);
q$error_manager.raise_error_instance (
err_instance_id_in => l_err_instance_id);
END;
Если ошибка повторяющегося значения была вызвана ограничением уникального имени, я получаю идентификатор экземпляра ошибки DUPLICATE-VALUE
. (Да, все верно: я использую имена ошибок, полностью обходя все проблемы, связанные с номерами ошибок.) Затем я добавляю контекстную информацию экземпляра (имя таблицы и значение первичного ключа, вызвавшее проблему). В завершение инициируется экземпляр ошибки, в результате чего исключение передается в следующий наружный блок.
По аналогии с передачей данных из приложения в репозиторий ошибок через API, вы также можете получить информацию об ошибке при помощи процедуры get_error_info
.
Пример:
BEGIN
run_my_application_code;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
l_error q$error_manager.error_info_rt;
BEGIN
q$error_manager.get_error_info (l_error);
DBMS_OUTPUT.put_line ('');
DBMS_OUTPUT.put_line ('Error in DEPT_SAL Procedure:');
DBMS_OUTPUT.put_line ('Code = ' || l_error.code);
DBMS_OUTPUT.put_line ('Name = ' || l_error.NAME);
DBMS_OUTPUT.put_line ('Text = ' || l_error.text);
DBMS_OUTPUT.put_line ('Error Stack = ' || l_error.error_stack);
END;
END;
Это лишь два из многих способов преодоления ограничений типа EXCEPTION
в PL/SQL. Мораль: ничто не заставляет вас мириться с ситуацией по умолчанию, при которой с экземпляром ошибки связывается только код и сообщение.
Создание стандартного шаблона для обобщенной обработки ошибок
Невозможность передачи исключений программе сильно усложняет совместное использование разделов обработки ошибок в разных блоках PL/SQL. Одну и ту же логику обработчика нередко приходится записывать снова и снова, особенно при работе с конкретными функциональными областями — скажем, файловым вводом/ выводом с UTL_FILE
. В таких ситуациях стоит выделить время на создание шаблонов обработчиков.
Давайте поближе познакомимся с UTL_FILE
. До выхода Oracle9i Database Release 2 в спецификации пакета UTL_FILE
определялся набор исключений. Однако компания Oracle не стала предоставлять коды этих исключений через директиву EXCEPTION_INIT
. А без обработки исключений UTL_FILE
по имени SQLCODE
не сможет разобраться, что пошло не так. Вероятно, в такой ситуации для программ UTL_FILE
можно создать шаблон, часть которого выглядит так:
DECLARE
l_file_id UTL_FILE.file_type;
PROCEDURE cleanup (file_in IN OUT UTL_FILE.file_type
,err_in IN VARCHAR2 := NULL)
IS
BEGIN
UTL_FILE.fclose (file_in);
IF err_in IS NOT NULL
THEN
DBMS_OUTPUT.put_line ('Обнаружена ошибка UTL_FILE:');
DBMS_OUTPUT.put_line (err_in);
END IF;
END cleanup;
BEGIN
-- Здесь размещается тело программы.
-- Перед выходом необходимо прибрать за собой ...
cleanup (l_file_id);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
cleanup (l_file_id, 'invalid_path');
RAISE;
WHEN UTL_FILE.invalid_mode
THEN
cleanup (l_file_id, 'invalid_mode');
RAISE;
END;
Основные элементы шаблона:
- Программа выполнения завершающих действий, пригодная для повторного использования; гарантирует, что текущий файл будет закрыт до потери дескриптора файла.
- Преобразование именованного исключения в строку, которую можно сохранить в журнале или вывести на экран, чтобы пользователь точно знал, какая ошибка была инициирована.
Рассмотрим еще один пример шаблона, который удобно использовать при работе с UTL_FILE
. В Oracle9i Database Release 2 появилась программа FREMOVE
для удаления файлов. Пакет UTL_FILE
предоставляет исключение DELETE_FAILED
, инициируемое тогда, когда FREMOVE
не удается удалить файл. После тестирования программы я обнаружил, что FREMOVE
может инициировать несколько возможных исключений, в числе которых:
UTL_FILE
.INVALID_OPERATION
— удаляемый файл не существует.UTL_FILE
.DELETE_FAILED
— у вас (или у процесса Oracle) недостаточно привилегий для удаления файла, или попытка завершилась неудачей по другой причине.
Начиная с Oracle9i Database Release 2, UTL_FILE
назначает коды ошибок всем своим исключениям, но вы все равно должны проследить за тем, чтобы при возникновении ошибки файлы были закрыты, и организовать последовательную обработку ошибок.
Итак, при использовании UTL_FILE
.FREMOVE
следует включать раздел обработчика исключения, который различает эти две ошибки:
BEGIN
UTL_FILE.fremove (dir, filename);
EXCEPTION
WHEN UTL_FILE.delete_failed
THEN
DBMS_OUTPUT.put_line (
'Ошибка при попытке удаления: ' || filename || ' в ' || dir);
-- Выполнение соответствующих действий...
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.put_line (
'Не удалось найти и удалить: ' || filename || ' в ' || dir);
-- Выполнение соответствующих действий...
END;
Оптимальная организация обработки ошибок в PL/SQL
Без унифицированной качественной методологии обработки ошибок очень трудно написать приложение, которое было бы удобным в использовании и одновременно простым в отладке.
Архитектура обработки ошибок в Oracle PL/SQL предоставляет очень гибкие средства для определения, инициирования и обработки ошибок. Однако у нее имеются свои ограничения, вследствие чего встроенную функциональность обычно приходится дополнять таблицами и кодами ошибок, специфическими для конкретного приложения.
Для решения проблемы обработки ошибок рекомендуется предпринять следующие действия:
- Тщательно разберитесь в системе инициирования и обработки ошибок в PL/SQL. Далеко не во ее аспекты интуитивно понятны. Простейший пример: исключение, инициированное в секции объявлений, не будет обрабатываться секцией исключений текущего блока.
- Выберите общую схему обработки ошибок в вашем приложении. Где и как будут обрабатываться ошибки? Какая информация об ошибке будет сохраняться и как это будет сделано? Как исключения будут передаваться в управляющую среду? Как будут обрабатываться намеренные и непредвиденные ошибки?
- Постройте стандартную инфраструктуру, которая будет использоваться всеми разработчиками проекта. Инфраструктура должна включать таблицы, пакеты и, возможно, объектные типы, а также четко определенный процесс использования всех перечисленных элементов. Не останавливайтесь на ограничениях PL/ SQL. Найдите обходные пути, расширяя модель обработки ошибок.
- Создайте шаблоны, которые могут использоваться всеми участниками вашей группы. Всегда проще следовать готовому стандарту, чем самостоятельно писать код обработки ошибок.
Жду отклика на статью. Что понравилось? Что нет?
Вас заинтересует / Intresting for you:
К сожалению, многие программисты не склонны тратить время на то, чтобы застраховать свой код PL/SQL от всех возможных неожиданностей. У большинства из нас хватает проблем с написанием кода, реализующего положительные аспекты приложения: управление данными клиентов, построение счетов и т. д.; вдобавок это увеличивает объем работы. Всегда бывает дьявольски сложно — как с психологической точки зрения, так и в отношении расходования ресурсов — сосредоточиться на негативных аспектах работы системы: что, если пользователь нажмет не ту клавишу? А что делать, если база данных Oracle недоступна?
В результате мы пишем приложения PL/SQL, предназначенные для работы в «идеальном мире», где в программах не бывает ошибок, пользователи вводят лишь правильные данные, а все системы — и аппаратные и программные — всегда в полном порядке.
Конечно, жестокая реальность устанавливает свои правила: как бы вы ни старались, в приложении все равно отыщется еще одна ошибка. А ваши пользователи всегда постараются отыскать последовательность нажатий клавиш, от которых форма перестанет работать. Проблема проста: либо вы выделяете время на отладку и защиту своих программ, либо вам придется вести бесконечные бои в отступлении, принимая отчаянные звонки от пользователей и пытаясь потушить разгорающееся пламя.
К счастью, PL/SQL предоставляет достаточно мощный и гибкий механизм перехвата и обработки ошибок. И вполне возможно написать на языке PL/SQL такое приложение, которое полностью защитит от ошибок и всех пользователей, и базу данных Oracle.
Основные концепции и терминология обработки исключений
В языке PL/SQL ошибки всех видов интерпретируются как исключения — ситуации, которые не должны возникать при нормальном выполнении программы.
К числу исключений относятся:
- ошибки, генерируемые системой (например, нехватка памяти или повторяющееся значение индекса);
- ошибки, вызванные действиями пользователя;
- предупреждения, выдаваемые приложением пользователю.
PL/SQL перехватывает ошибки и реагирует на них при помощи так называемых обработчиков исключений. Механизм обработчиков исключений позволяет четко отделить код обработки ошибок от основной логики программы, а также дает возможность реализовать обработку ошибок, управляемую событиями (в отличие от старой линейной модели). Независимо от того, как и по какой причине возникло конкретное исключение, оно всегда обрабатывается одним и тем же обработчиком в разделе исключений.
При возникновении ошибки — как системной, так и ошибки в приложении — в PL/SQL инициируется исключение. В результате выполнение блока прерывается, и управление передается для обработки в раздел исключений текущего блока, если он имеется. После обработки исключения возврат в тот блок, где исключение было инициировано, невозможен, поэтому управление передается во внешний блок.
Схема передачи управления при возникновении исключения показана на рис. 1.
Рис. 1. Архитектура обработки исключений
Существует два типа исключений:
- Системное исключение определяется в Oracle и обычно инициируется исполняемым ядром PL/SQL, обнаружившим ошибку. Одним системным исключениям присваиваются имена (например,
NO_DATA_FOUND
), другие ограничиваются номерами и описаниями. - Исключение, определяемое программистом, актуально только для конкретного приложения. Имя исключения можно связать с конкретной ошибкой Oracle с помощью директивы компилятора
EXCEPTION_INIT
или же назначить ошибке номер и описание процедуройRAISE_APPLICATION_ERROR
.
В этом блоге будут использоваться следующие термины:
- Раздел исключений — необязательный раздел блока PL/SQL (анонимного блока, процедуры, функции, триггера или инициализационного раздела пакета), содержащий один или несколько обработчиков исключений. Структура раздела исключений очень похожа на структуру команды
CASE
, о которой рассказывалось в этом блоге. - Инициировать исключение — значит остановить выполнение текущего блока PL/SQL, оповещая исполняемое ядро об ошибке. Исключение может инициировать либо Oracle, либо ваш собственный программный код при помощи команды
RAISE
или процедурыRAISE_APPLICATION_ERROR
. - Обработать исключение — значит перехватить ошибку, передав управление обработчику исключения. Написанный программистом обработчик может содержать код, который в ответ на исключение выполняет определенные действия (например, записывает информацию об ошибке в журнал, выводит сообщение для пользователя или передает исключение во внешний блок).
- Область действия — часть кода (конкретный блок или весь раздел), в котором может инициироваться исключение, а также часть кода, инициируемые исключения которого могут перехватываться и обрабатываться соответствующим разделом исключений.
- Передача исключения — процесс передачи исключения во внешний блок, если в текущем блоке это исключение не обработано.
- Необработанное исключение — исключение, которое передается без обработки из «самого внешнего» блока PL/SQL. После этого управление передается исполнительной среде, которая уже сама определяет, как отреагировать на исключение (выполнить откат транзакции, вывести сообщение об ошибке, проигнорировать ее и т. д.).
- Анонимное исключение — исключение, с которым связан код ошибки и описание. Такое исключение не имеет имени, которое можно было бы использовать в команде
RAISE
или секцииWHEN
обработчика исключений. - Именованное исключение — исключение, которому имя присвоено либо Oracle (в одном из встроенных пакетов), либо разработчиком. В частности, для этой цели можно использовать директиву компилятора
EXCEPTION_INIT
(в таком случае имя можно будет применять и для инициирования, и для обработки исключения).
Определение исключений
Прежде чем исключение можно будет инициировать и обрабатывать, его необходимо определить. В Oracle заранее определены тысячи исключений, большинство из которых имеют только номера и пояснительные сообщения. Имена присваиваются только самым распространенным исключениям.
Имена присваиваются в пакете STANDARD
(одном из двух пакетов по умолчанию PL/SQL; другой пакет — DBMS_STANDARD
), а также в других встроенных пакетах, таких как UTL_FILE
и DBMS_SQL
. Код, используемый Oracle для определения исключений (таких, как NO_DATA_FOUND
), не отличается от кода, который вы будете использовать для определения или объявления ваших собственных исключений.
Это можно сделать двумя способами, описанными ниже.
Объявление именованных исключений
Исключения PL/SQL, объявленные в пакете STANDARD
и в других встроенных пакетах, представляют внутренние (то есть системные) ошибки. Однако многие проблемы, с которыми будет сталкиваться пользователь приложения, актуальны только в этом конкретном приложении. Возможно, вашей программе придется перехватывать и обрабатывать такие ошибки, как «отрицательный баланс счета» или «дата обращения не может быть меньше текущей даты». Хотя эти ошибки имеют иную природу, нежели, скажем, ошибки «деления на нуль», они также относятся к разряду исключений, связанных с нормальной работой программы, и должны обрабатываться этой программой.
Одной из самых полезных особенностей обработки исключений PL/SQL является отсутствие структурных различий между внутренними ошибками и ошибками конкретных приложений. Любое исключение может и должно обрабатываться в разделе исключений независимо от типа ошибки.
Конечно, для обработки исключения необходимо знать его имя. Поскольку в PL/SQL имена пользовательским исключениям автоматически не назначаются, вы должны делать это самостоятельно, определяя исключения в разделе объявлений блока PL/SQL. При этом задается имя исключения, за которым следует ключевое слово EXCEPTION
:
имя_исключения EXCEPTION;
Следующий раздел объявлений процедуры calc_annual_sales
содержит два объявления исключений, определяемых программистом:
PROCEDURE calc_annual_sales(company_id_in IN company.company_id%TYPE)
IS
invalid_company_id EXCEPTION;
negative_balance EXCEPTION;
duplicate_company BOOLEAN;
BEGIN
... исполняемые команды ...
EXCEPTION
WHEN NO_DATA_FOUND -- системное исключение
THEN
...
WHEN invalid_company_id
THEN
WHEN negative_balance
THEN
...
END;
По своему формату имена исключений схожи с именами других переменных, но ссылаться на них можно только двумя способами:
- В команде RAISE, находящейся в исполняемом разделе программы (для инициирования исключения):
RAISE invalid_company_id;
- В секции WHEN раздела исключений (для обработки инициированного исключения):
WHEN invalid_company_id THEN
Связывание имени исключения с кодом ошибки
В Oracle, как уже было сказано, имена определены лишь для самых распространенных исключений. Тысячи других ошибок в СУБД имеют лишь номера и снабжены пояснительными сообщениями. Вдобавок инициировать исключение с номером ошибки (в диапазоне от –20 999 до –20 000) может и разработчик приложения, воспользовавшись для этой цели процедурой RAISE_APPLICATION_ERROR
(см. далее раздел «Инициирование исключений»).
Наличие в программном коде исключений без имен вполне допустимо, но такой код малопонятен и его трудно сопровождать. Допустим, вы написали программу, при выполнении которой Oracle выдает ошибку, связанную с данными, например ORA-01843: not a valid month
. Для перехвата этой ошибки в программу включается обработчик следующего вида:
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1843 THEN
Но код получается совершенно непонятным. Чтобы сделать смысл этого кода более очевидным, следует воспользоваться директивой EXCEPTION_INIT
.
Встроенная функция SQLCODE возвращает номер последней сгенерированной ошибки. Она будет рассмотрена далее в разделе «Обработка исключений» этой статьи.
Директива EXCEPTION_INIT
Директива компилятора EXCEPTION_INIT
(команда, выполняемая во время компиляции) связывает идентификатор, объявленный с ключевым словом EXCEPTION
, с внутренним кодом ошибки. Установив такую связь, можно инициировать исключение по имени и указать это имя в условии WHEN
обработчика ошибок.
С директивой EXCEPTION_INIT
условие WHEN
, использованное в предыдущем примере, приводится к следующему виду:
PROCEDURE my_procedure
IS
invalid_month EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_month, −1843);
BEGIN
...
EXCEPTION
WHEN invalid_month THEN
Жесткое кодирование номера ошибки становится излишним; имя ошибки говорит само за себя.
Директива EXCEPTION_INIT
должна располагаться в разделе объявлений блока. Указанное в ней исключение должно быть объявлено либо в том же блоке, либо во внешнем, либо в спецификации пакета. Синтаксис директивы в анонимном блоке:
DECLARE
имя_исключения EXCEPTION;
PRAGMA EXCEPTION_INIT (имя_исключения, целое_число);
Здесь имя_исключения
— имя исключения, объявляемого программистом, а целое_число
— номер ошибки Oracle, которую следует связать с данным исключением. Номером ошибки может служить любое число со следующими ограничениями:
- Номер ошибки не может быть равен –1403 (один из двух кодов ошибок
NO_DATA_FOUND
). Если вы по какой-либо причине захотите связать свое именованное исключение с этой ошибкой, передайте директивеEXCEPTION_INIT
значение 100. - Номер ошибки не может быть равен 0 или любому положительному числу, кроме 100.
- Номер ошибки не может быть отрицательным числом, меньшим –1 000 000.
Рассмотрим пример возможного объявления исключения. В приведенном ниже программном коде я объявляю и связываю исключение со следующим номером:
ORA-2292 integrity constraint (OWNER.CONSTRAINT) violated -
child record found.
Ошибка происходит при попытке удаления родительской записи, у которой в таблице имеются дочерние записи (то есть записи с внешним ключом, ссылающимся на родительскую запись):
PROCEDURE delete_company (company_id_in IN NUMBER)
IS
/* Объявление исключения. */
still_have_employees EXCEPTION;
/* Имя исключения связывается с номером ошибки. */
PRAGMA EXCEPTION_INIT (still_have_employees, 2292);
BEGIN
/* Попытка удаления информации о компании. */
DELETE FROM company
WHERE company_id = company_id_in;
EXCEPTION
/* При обнаружении дочерних записей инициируется это исключение! */
WHEN still_have_employees
THEN
DBMS_OUTPUT.PUT_LINE
('Пожалуйста, сначала удалите данные о служащих компании.');
END;
Рекомендации по использованию EXCEPTION_INIT
Директиву EXCEPTION_INIT
целесообразно использовать в двух ситуациях:
- при необходимости присвоить имя безымянному системному исключению, задействованному в программе (следовательно, если в Oracle не определено имя для некоторой ошибки, это еще не означает, что с ней можно работать только по номеру);
- когда нужно присвоить имя специфическому для приложения исключению, инициируемому процедурой
RAISE_APPLICATION_ERROR
(см. далее раздел «Инициирование исключений»). Это позволяет обрабатывать данное исключение по имени, а не по номеру.
В обоих случаях все директивы EXCEPTION_INIT
желательно объединить в пакет, чтобы определения исключений не были разбросаны по всему коду приложения. Допустим, вы интенсивно используете динамический SQL, и при выполнении запросов часто возникает ошибка «invalid column name» (неверное имя столбца). Запоминать код ошибки не хочется, но и определять директивы имя для исключения в 20 разных программах тоже неразумно. Поэтому имеет смысл определить собственные «системные исключения» в отдельном пакете для работы с динамическим SQL:
CREATE OR REPLACE PACKAGE dynsql
IS
invalid_table_name EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_table_name, -903);
invalid_identifier EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_identifier, -904);
Теперь перехват этих ошибок в программе может производиться следующим образом:
WHEN dynsql.invalid identifier THEN ...
Аналогичный подход рекомендуется использовать при работе с кодами ошибок –20NNN, передаваемыми процедуре RAISE_APPLICATION_ERROR
(см. далее в этой заметке моего блога). Создайте пакет, в котором этим кодам будут присваиваться имена. Он может выглядеть примерно так:
PACKAGE errnums
IS
en_too_young CONSTANT NUMBER := -20001;
exc_too_young EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_too_young, -20001);
en_sal_too_low CONSTANT NUMBER := -20002;
exc_sal_too_low EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_sal_too_low , -20002);
END errnums;
При наличии такого пакета можно использовать код следующего вида, не указывая номер ошибки в коде:
PROCEDURE validate_emp (birthdate_in IN DATE)
IS
min_years CONSTANT PLS_INTEGER := 18;
BEGIN
IF ADD_MONTHS (SYSDATE, min_years * 12 * -1) < birthdate_in
THEN
RAISE_APPLICATION_ERROR
(errnums.en_too_young,
'Возраст работника должен быть не менее ' || min_years || ' лет.');
END IF;
END;
Именованные системные исключения
В Oracle для относительно небольшого количества исключений определены стандартные имена, задаваемые директивой компилятора EXCEPTION_INIT
во встроенных пакетах. Самые важные и часто применяемые из них определены в пакете STANDARD
. Так как это один из двух используемых по умолчанию пакетов PL/SQL, на определенные в нем исключения можно ссылаться без префикса с именем пакета. Например, если потребуется инициировать в программе исключение NO_DATA_FOUND
, это можно сделать любой из следующих команд:
WHEN NO_DATA_FOUND THEN
WHEN STANDARD.NO_DATA_FOUND THEN
Определения стандартных именованных исключений встречаются и в других встроенных пакетах — например, в пакете DBMS_LOB
, предназначенном для работы с большими объектами. Пример одного такого определения из указанного пакета:
invalid_argval EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_argval, -21560);
Поскольку пакет DBMS_LOB
не используется по умолчанию, перед ссылкой на это исключение необходимо указать имя пакета:
WHEN DBMS_LOB.invalid_argval THEN...
Многие исключения, определенные в пакете STANDARD
, перечислены в табл. 1. Для каждого из них приводится номер ошибки Oracle, значение, возвращаемое при вызове SQLCODE
(встроенная функция SQLCODE
, которая возвращает текущий код ошибки — см. раздел «Встроенные функции ошибок»), и краткое описание. Значение, возвращаемое SQLCODE
, совпадает с кодом ошибки Oracle, с одним исключением: определяемый стандартом ANSI код ошибки NO_DATA_FOUND
равен 100.
Имя исключения/Ошибка Oracle/SQLCODE | Описание |
CURSOR_ALREADY_OPEN ORA-6511 SQLCODE = –6511 | Попытка открытия курсора, который был открыт ранее. Перед повторным открытием курсор необходимо сначала закрыть |
DUP_VAL_ON_INDEX ORA-00001 SQLCODE = −1 | Команда INSERT или UPDATE пытается сохранить повторяющиеся значения в столбцах, объявленных с ограничением UNIQUE |
INVALID_CURSOR ORA-01001 SQLCODE = −1001 | Ссылка на несуществующий курсор. Обычно ошибка встречается при попытке выборки данных из неоткрытого курсора или закрытия курсора до его открытия |
INVALID_NUMBER ORA-01722 SQLCODE = −1722 | Выполняемая SQL-команда не может преобразовать символьную строку в число. Это исключение отличается от VALUE_ERROR тем, что оно инициируется только из SQL-команд |
LOGIN_DENIED ORA-01017 SQLCODE = −1017 | Попытка программы подключиться к СУБД Oracle с неверным именем пользователя или паролем. Исключение обычно встречается при внедрении кода PL/SQL в язык 3GL |
NO_DATA_FOUND ORA-01403 SQLCODE = +100 | Исключение инициируется в трех случаях: (1) при выполнении инструкции SELECT INTO (неявный курсор), которая не возвращает ни одной записи; (2) при ссылке на неинициализированную запись локальной таблицы PL/SQL; (3) при попытке выполнить операцию чтения после достижения конца файла при использовании пакета UTL_FILE |
NOT_LOGGED ON ORA-01012 SQLCODE = −1012 | Программа пытается обратиться к базе данных (обычно из инструкции DML) до подключения к СУБД Oracle |
PROGRAM_ERROR ORA-06501 SQLCODE = −6501 | Внутренняя программная ошибка PL/SQL. В сообщении об ошибке обычно предлагается обратиться в службу поддержки Oracle |
STORAGE_ERROR ORA-06500 SQLCODE = −6500 | Программе PL/SQL не хватает памяти или память по какой-то причине повреждена |
TIMEOUT_ON_RESOURCE ORA-00051 SQLCODE = −51 | Тайм-аут СУБД при ожидании ресурса |
TOO_MANY_ROWS ORA-01422 SQLCODE = −1422 | Команда SELECT INTO возвращает несколько записей, хотя должна возвращать лишь одну (в таких случаях инструкция SELECT включается в явное определение курсора, а записи выбираются по одной) |
TRANSACTION_BACKED_OUT ORA-00061 SQLCODE = −61 | Удаленная часть транзакции отменена либо при помощи явной инструкции ROLLBACK , либо в результате какого-то другого действия (например, неудачного выполнения команды SQL или DML в удаленной базе данных) |
VALUE_ERROR ORA-06502 SQLCODE = −6502 | Ошибка связана с преобразованием, усечением или проверкой ограничений числовых или символьных данных. Это общее и очень распространенное исключение. Если подобная ошибка содержится в инструкции SQL или DML, то в блоке PL/SQL инициируется исключение INVALID_NUMBER |
ZERO_DIVIDE ORA-01476 SQLCODE = −1476 | Попытка деления на ноль |
Рассмотрим пример использования этой таблицы исключений. Предположим, ваша программа инициирует необрабатываемое исключение для ошибки ORA-6511. Заглянув в таблицу, вы видите, что она связана с исключением CURSOR_ALREADY_OPEN
. Найдите блок PL/SQL, в котором произошла ошибка, и добавьте в него обработчик исключения
CURSOR_ALREADY_OPEN:
EXCEPTION
WHEN CURSOR_ALREADY_OPEN
THEN
CLOSE my_cursor;
END;
Конечно, еще лучше было бы проанализировать весь программный код и заранее определить, какие из стандартных исключений в нем могут инициироваться. В таком случае вы сможете решить, какие исключения следует обрабатывать конкретно, какие следует включить в конструкцию WHEN OTHERS
(см. далее), а какие оставить необработанными.
Область действия исключения
Областью действия исключения называется та часть программного кода, к которой оно относится, то есть блок, где данное исключение может быть инициировано. В следующей таблице указаны области действия исключений четырех разных типов.
Тип исключения | Область действия |
Именованное системное исключение | Исключение является глобальным, то есть не ограничивается каким-то конкретным блоком кода. Системные исключения могут инициироваться и обрабатываться в любом блоке |
Именованное исключение, определяемое программистом | Исключение может инициироваться и обрабатываться только в исполнительном разделе и разделе исключений, входящих в состав блока, где объявлено данное исключение (или в состав любого из вложенных в него блоков). Если исключение определено в спецификации пакета, то его областью действия являются все те программы, владельцы которых обладают для этого пакета привилегией EXECUTE |
Анонимное системное исключение | Исключение может обрабатываться в секции WHEN OTHERS любого раздела исключений PL/SQL. Если присвоить ему имя, то его область действия будет такой же, как у именованного исключения, определяемого программистом |
Анонимное исключение, определяемое программистом | Исключение определяется в вызове процедуры RAISE_APPLICATION_ERROR , а затем передается в вызывающую программу |
Рассмотрим пример исключения overdue_balance
, объявленного в процедуре check_account
(таким образом, область его действия ограничивается указанной процедурой):
PROCEDURE check_account (company_id_in IN NUMBER)
IS
overdue_balance EXCEPTION;
BEGIN
... исполняемые команды ...
LOOP
...
IF ... THEN
RAISE overdue_balance;
END IF;
END LOOP;
EXCEPTION
WHEN overdue_balance THEN ...
END;
С помощью команды RAISE
исключение overdue_balance
можно инициировать в процедуре check_account
, но не в программе, которая ее вызывает. Например, для следующего анонимного блока компилятор выдает ошибку:
DECLARE
company_id NUMBER := 100;
BEGIN
check_account (100);
EXCEPTION
WHEN overdue_balance /* В PL/SQL такая ссылка недопустима. */
THEN ...
END;
PLS-00201: identifier "OVERDUE_BALANCE" must be declared
Для приведенного выше анонимного блока процедура check_account
является «черным ящиком». Все объявленные в ней идентификаторы, в том числе идентификаторы исключения, не видны для внешнего программного кода.
Инициирование исключений
Исключение может быть инициировано приложением в трех случаях:
- Oracle инициирует исключение при обнаружении ошибки;
- приложение инициирует исключение командой
RAISE
; - исключение инициируется встроенной процедурой
RAISE_APPLICATION_ERROR
.
Как Oracle инициирует исключения, вы уже знаете. Теперь давайте посмотрим, как это может сделать программист.
Команда RAISE
Чтобы программист имел возможность самостоятельно инициировать именованные исключения, в Oracle поддерживается команда RAISE
. С ее помощью можно инициировать как собственные, так и системные исключения. Команда имеет три формы:
RAISE имя_исключения;
RAISE имя_пакета.имя_исключения;
RAISE;
Первая форма (без имени пакета) может инициировать исключения, определенные в текущем блоке (или в содержащем его блоке), а также системные исключения, объявленные в пакете STANDARD
. Далее приводятся два примера, в первом из которых инициируется исключение, определенное программистом:
DECLARE
invalid_id EXCEPTION; -- Все идентификаторы должны начинаться с буквы 'X'.
id_value VARCHAR2(30);
BEGIN
id_value := id_for ('SMITH');
IF SUBSTR (id_value, 1, 1) != 'X'
THEN
RAISE invalid_id;
END IF;
...
END;
При необходимости вы всегда можете инициировать системное исключение:
BEGIN
IF total_sales = 0
THEN
RAISE ZERO_DIVIDE; -- Определено в пакете STANDARD
ELSE
RETURN (sales_percentage_calculation (my_sales, total_sales));
END IF;
END;
Если исключение объявлено в пакете (но не в STANDARD
) и инициируется извне, имя исключения необходимо уточнить именем пакета:
IF days_overdue (isbn_in, borrower_in) > 365
THEN
RAISE overdue_pkg.book_is_lost;
END IF;
Третья форма RAISE
не требует указывать имя исключения, но используется только в условии WHEN
раздела исключений. Ее синтаксис предельно прост:
RAISE;
Используйте эту форму для повторного инициирования (передачи) перехваченного исключения:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- Используем общий пакет для сохранений всей контекстной
-- информации: код ошибки, имя программы и т. д.
errlog.putline (company_id_in);
-- А теперь исключение NO_DATA_FOUND передается
-- в родительский блок без обработки.
RAISE;
Эта возможность особенно полезна в тех случаях, когда информацию об ошибке нужно записать в журнал, а сам процесс обработки возложить на родительский блок. Таким образом выполнение родительских блоков завершается без потери информации об ошибке.
Процедура RAISE_APPLICATION_ERROR
Для инициирования исключений, специфических для приложения, Oracle предоставляет процедуру RAISE_APPLICATION_ERROR
(определенную в используемом по умолчанию пакете DBMS_STANDARD
). Ее преимущество перед командой RAISE (которая тоже может инициировать специфические для приложения явно объявленные исключения) заключается в том, что она позволяет связать с исключением сообщение об ошибке.
При вызове этой процедуры выполнение текущего блока PL/SQL прекращается, а любые изменения аргументов OUT
и IN OUT
(если таковые имеются) отменяются. Изменения, внесенные в глобальные структуры данных (с помощью команды INSERT, UPDATE, MERGE
или DELETE
), такие как переменные пакетов и объекты баз данных, не отменяются. Для отката DML-команд необходимо явно указать в разделе обработки исключений команду ROLLBACK
.
Заголовок этой процедуры (определяемый в пакете DBMS_STANDARD
) выглядит так:
PROCEDURE RAISE_APPLICATION_ERROR (
num binary_integer,
msg varchar2,
keeperrorstack boolean default FALSE);
Здесь num
— номер ошибки из диапазона от –20 999 до –20 000 (только представьте: все остальные отрицательные числа Oracle резервирует для собственных исключений!); msg
— сообщение об ошибке, длина которого не должна превышать 2048 символов (символы, выходящие за эту границу, игнорируются); аргумент keeperrorstack
указывает, хотите ли вы добавить ошибку к уже имеющимся в стеке (TRUE
), или заменить существующую ошибку (значение по умолчанию — FALSE
).
Oracle выделяет диапазон номеров от –20 999 до –20 000 для пользовательских ошибок, но учтите, что в некоторых встроенных пакетах, в том числе в DBMS_OUTPUT
и DBMS_DESCRIBE
, номера от –20 005 до –20 000 все равно присваиваются системным ошибкам. За дополнительной информацией обращайтесь к документации пакетов.
Рассмотрим пример полезного применения этой встроенной процедуры. Допустим, мы хотим, чтобы сообщения об ошибках выдавались пользователям на разных языках. Создадим для них таблицу error_table
и определим в ней язык каждого сообщения значением столбца string_language
. Затем создается процедура, которая генерирует заданную ошибку, загружая соответствующее сообщение из таблицы с учетом языка текущего сеанса:
PROCEDURE raise_by_language (code_in IN PLS_INTEGER)
IS
l_message error_table.error_string%TYPE;
BEGIN
SELECT error_string
INTO l_message
FROM error_table
WHERE error_number = code_in
AND string_language = USERENV ('LANG');
RAISE_APPLICATION_ERROR (code_in, l_message);
END;
Обработка исключений
Как только в программе возникает исключение, нормальное выполнение блока PL/SQL останавливается, и управление передается в раздел исключений. Затем исключение либо обрабатывается обработчиком исключений в текущем блоке PL/SQL, либо передается в родительский блок.
Чтобы обработать или перехватить исключение, нужно написать для него обработчик. Обработчики исключений располагаются после всех исполняемых команд блока, но перед завершающим ключевым словом END
. Начало раздела исключений отмечает ключевое слово EXCEPTION
:
DECLARE
... объявления ...
BEGIN
... исполняемые команды ...
[ EXCEPTION
... обработчики исключений ... ]
END;
Синтаксис обработчика исключений может быть таким:
WHEN имя_исключения [ OR имя_исключения ... ]
THEN
исполняемые команды
или таким:
WHEN OTHERS
THEN
исполняемые команды
В одном разделе исключений может быть несколько их обработчиков. Структура обработчиков напоминает структуру условной команды CASE
.
Свойство | Описание |
EXCEPTION WHEN NO_DATA_FOUND THEN исполняемые_команды1; | Если инициировано исключение NO_DATA_FOUND , выполнить первый набор команд |
WHEN payment_overdue THEN исполняемые_команды2; | Если просрочена оплата, выполнить второй набор команд |
WHEN OTHERS THEN исполняемые_команды3; END; | Если инициировано иное исключение, выполнить третий набор команд |
Если имя, заданное в условии WHEN
, совпадает с инициированным исключением, то это исключение обрабатывается соответствующим набором команд. Обратите внимание: исключения перехватываются по именам, а не по кодам ошибок. Но если инициированное исключение не имеет имени или его имя не соответствует ни одному из имен, указанных в условиях WHEN
, тогда оно обрабатывается командами, заданными в секции WHEN OTHERS
(если она имеется). Любая ошибка может быть перехвачена только одним обработчиком исключений. После выполнения команд обработчика управление сразу же передается из текущего блока в родительский или вызывающий блок.
Секция WHEN OTHERS
не является обязательной. Когда она отсутствует, все необработанные исключения немедленно передаются в родительский блок, если таковой имеется. Секция WHEN OTHERS
должна быть последним обработчиком исключений в блоке. Если разместить после нее еще одну секцию WHEN
, компилятор выдаст сообщение об ошибке.
Встроенные функции ошибок
Прежде чем переходить к изучению тонкостей обработки ошибок, мы сначала вкратце познакомимся со встроенными функциями Oracle, предназначенными для идентификации, анализа и реагирования на ошибки, возникающие в приложениях PL/SQL.
SQLCODE
Функция SQLCODE
возвращает код ошибки последнего исключения, инициированного в блоке. При отсутствии ошибок SQLCODE
возвращает 0. Кроме того, SQLCODE
возвращает 0 при вызове за пределами обработчика исключений.
База данных Oracle поддерживает стек значений SQLCODE
. Допустим, к примеру, что функция FUNC инициирует исключение VALUE_ERROR
(–6502). В разделе исключений FUNC
вызывается процедура PROC
, которая инициирует исключение DUP_VAL_ON_INDEX
(–1). В разделе исключений PROC
функция SQLCODE
возвращает значение –1. Но когда управление передается в раздел исключений FUNC
, SQLCODE
будет возвращать –6502.
SQLERRM
Функция SQLERRM
возвращает сообщение об ошибке для заданного кода ошибки. Если вызвать SQLERRM
без указания кода ошибки, функция вернет сообщение, связанное со значением, возвращаемым SQLCODE
. Например, если SQLCODE
возвращает 0, функция SQLERRM
вернет следующую строку:
ORA-0000: normal, successful completion
Если же SQLCODE
возвращает 1 (обобщенный код ошибки для исключения, определяемого пользователем), SQLERRM
вернет строку:
User-Defined Exception
Пример вызова SQLERRM
для получения сообщения об ошибке для конкретного кода:
1 BEGIN
2 DBMS_OUTPUT.put_line (SQLERRM (-1403));
3* END;
SQL> /
ORA-01403: no data found
Максимальная длина строки, возвращаемой SQLERRM
, составляет 512 байт (в некоторых ранних версиях Oracle — 255 байт). Из-за этого ограничения Oracle Corporation рекомендует вызывать функцию DBMS_UTILITY
.FORMAT_ERROR_STACK
, чтобы гарантировать вывод полной строки (эта встроенная функция не усекает текст до 2000 байт).
DBMS_UTILITY.FORMAT_ERROR_STACK
Эта встроенная функция, как и SQLERRM
, возвращает сообщение, связанное с текущей ошибкой (то есть значение, возвращаемое SQLCODE
). Ее отличия от SQLERRM
:
- Она возвращает до 1899 символов сообщения, что позволяет избежать проблем с усечением.
- Этой функции не может передаваться код ошибки; соответственно, она не может использоваться для получения сообщения, соответствующего произвольному коду.
Как правило, эта функция вызывается в логике обработчика исключения для получения полного сообщения об ошибке.
Хотя в имя функции входит слово «stack», она не возвращает информацию о стеке ошибок, приведшем к строке, в которой изначально была инициирована ошибка. Эту задачу решает DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Эта функция, появившаяся в Oracle10g, возвращает отформатированную строку с содержимым стека программ и номеров строк. Ее выходные данные позволяют отследить строку, в которой изначально была инициирована ошибка.
Тем самым заполняется весьма существенный пробел в функциональности PL/SQL. В Oracle9i и предшествующих версиях после обработки исключения в блоке PL/ SQL было невозможно определить строку, в которой произошла ошибка (возможно, самая важная информация для разработчика). Если программист хотел получить эту информацию, он должен был разрешить прохождение необработанного исключения, чтобы полная трассировочная информация ошибки была выведена на экран. Ситуация более подробно описана в следующем разделе.
DBMS_UTILITY.FORMAT_CALL_STACK
Функция возвращает отформатированную строку со стеком вызовов в приложении PL/SQL. Практическая полезность функции не ограничивается обработкой ошибок; она также пригодится для трассировки выполнения вашего кода.
В Oracle Database 12c появился пакет UTL_CALL_STACK
, который также предоставляет доступ к стеку вызовов, стеку ошибок и информации обратной трассировки.
Подробнее о DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Функцию DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
следует вызывать в обработчике исключения. Она выводит содержимое стека выполнения в точке инициирования исключения. Таким образом, вызов DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
в разделе исключений на верхнем уровне стека позволит узнать, где именно в стеке вызовов произошла ошибка. Рассмотрим следующий сценарий: мы определяем процедуру proc3
, которая вызывает процедуру proc2
, а последняя, в свою очередь, вызывает proc1
. Процедура proc1 инициирует исключение:
CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
DBMS_OUTPUT.put_line ('выполнение proc1');
RAISE NO_DATA_FOUND;
END;
/
CREATE OR REPLACE PROCEDURE proc2 IS
l_str VARCHAR2 (30) := 'вызов proc1';
BEGIN
DBMS_OUTPUT.put_line (l_str);
proc1;
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
DBMS_OUTPUT.put_line ('вызов proc2');
proc2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Стек ошибок верхнего уровня:');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/
Единственной программой с обработчиком ошибок является внешняя процедура proc3
. Вызов функции трассировки включен в обработчик WHEN OTHERS
процедуры proc3
. При выполнении этой процедуры будет получен следующий результат:
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace');
3 proc3;
4 END;
5 /
Proc3 -> Proc2 -> Proc1 backtrace
вызов proc2
вызов proc1
выполнение proc1
Error stack at top level:
ORA-06512: at "SCOTT.PROC1", line 4
ORA-06512: at "SCOTT.PROC2", line 5
ORA-06512: at "SCOTT.PROC3", line 4
Как видите, функция трассировки выводит в начале стека номер строки proc1
, в которой произошла исходная ошибка.
Часто исключение происходит где-то в глубине стека вызовов. Если вы хотите, чтобы оно было передано во внешний блок PL/SQL, вероятно, вам придется заново инициировать его в каждом обработчике стека блоков. Функция DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
выдает трассировку исполнения вплоть до последней команды RAISE
в сеансе пользователя. Учтите, что вызов RAISE
для конкретного исключения или повторное инициирование текущего исключения приводит к инициализации стека, выдаваемого DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
. Таким образом, если вы хотите использовать эту функцию, возможны два пути:
- Вызовите функцию в разделе исключений блока, в котором была инициирована ошибка. Это позволит вам получить (и сохранить в журнале) номер ошибки, даже если исключение было заново инициировано в дальнейшей позиции стека.
- Обойдите обработчики исключений в промежуточных программах вашего стека и вызовите функцию в разделе исключений внешней программы в стеке.
Только номер строки, пожалуйста
В реальном приложении трассировка ошибок может быть очень длинной. Как правило, специалиста, занимающегося отладкой или поддержкой, не интересует весь стек — достаточно только последнего элемента. Возможно, разработчику приложения стоит вывести эту важную информацию, чтобы пользователь мог немедленно и точно описать суть проблемы группе поддержки.
В такой ситуации необходимо разобрать строку с данными трассировки и извлечь из нее последний элемент. Я написал для этого специальную программу и оформил ее в пакет BT
. В этом пакете реализован простой, понятный интерфейс:
PACKAGE bt
IS
TYPE error_rt IS RECORD (
program_owner all_objects.owner%TYPE
, program_name all_objects.object_name%TYPE
, line_number PLS_INTEGER
);
FUNCTION info (backtrace_in IN VARCHAR2)
RETURN error_rt;
PROCEDURE show_info (backtrace_in IN VARCHAR2);
END bt;
Тип записи error_rt
содержит отдельное поле для каждого возвращаемого элемента трассировки (владелец программного модуля, имя программного модуля и номер строки в программе). Затем вместо того, чтобы вызывать функцию трассировки в каждом разделе исключения и разбирать ее результаты, я вызываю функцию bt.info
и вывожу конкретную информацию об ошибке.
Полезные применения SQLERRM
Вы можете использовать DBMS_UTILITY
.FORMAT_ERROR_STACK
вместо SQLERRM
, но это не означает, что функция SQLERRM
совершенно неактуальна. В частности, она поможет вам получить ответ на следующие вопросы:
- Является ли заданное число действительным кодом ошибки Oracle?
- Какое сообщение соответствует коду ошибки?
Как упоминалось ранее в нашей статье, функция SQLERRM
возвращает сообщение об ошибке для заданного кода. Но если передать SQLERRM
недействительный код, исключение не инициируется. Вместо этого возвращается строка в одном из двух форматов:
- Если число отрицательно:
ORA-NNNNN: Message NNNNN not found; product=RDBMS; facility=ORA
- Если число положительно или меньше −65535:
-N: non-ORACLE exception
Этим обстоятельством можно воспользоваться для построения функций, возвращающих точную информацию о том коде, с которым вы работаете в настоящее время. Ниже приведена спецификация пакета с этими программами:
PACKAGE oracle_error_info
IS
FUNCTION is_app_error (code_in IN INTEGER)
RETURN BOOLEAN;
FUNCTION is_valid_oracle_error (
code_in IN INTEGER
, app_errors_ok_in IN BOOLEAN DEFAULT TRUE
, user_error_ok_in IN BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN;
PROCEDURE validate_oracle_error (
code_in IN INTEGER
, message_out OUT VARCHAR2
, is_valid_out OUT BOOLEAN
, app_errors_ok_in IN BOOLEAN DEFAULT TRUE
, user_error_ok_in IN BOOLEAN DEFAULT TRUE
);
END oracle_error_info;
Объединение нескольких исключений в одном обработчике
В одном условии WHEN
можно оператором OR
объединить несколько исключений — подобно тому, как этим оператором объединяются логические выражения:
WHEN invalid_company_id OR negative_balance
THEN
В одном обработчике также можно комбинировать имена пользовательских и системных исключений:
WHEN balance_too_low OR ZERO_DIVIDE OR DBMS_LDAP.INVALID_SESSION
THEN
Впрочем, применять оператор AND
в такой комбинации нельзя, потому что в любой момент времени может быть инициировано только одно исключение.
Необработанные исключения
Исключение, инициированное в программе, но не обработанное в соответствующем разделе текущего или родительского блока PL/SQL, называется необработанным. PL/ SQL возвращает сообщение об ошибке, вызвавшей необработанное исключение, в ту среду, где была запущена данная программа. Эта среда (ею может быть SQL*Plus. Oracle Forms, программа на языке Java и т. д.) действует по ситуации. В частности, SQL*Plus осуществляет откат всех DML-инструкций, выполненных в родительском блоке.
Одним из важнейших моментов, связанных с проектированием архитектуры приложения, является вопрос о том, разрешается ли в нем использовать необработанные исключения. Такие исключения разными средами обрабатываются по-разному, и не всегда это делается корректно. Если ваша программа PL/SQL вызывается не из PL/SQL-среды, в ее «самом внешнем» блоке можно запрограммировать следующие действия:
- перехват всех исключений, которые могли быть переданы до текущей точки;
- запись информации об ошибке в журнал, с тем чтобы впоследствии ее мог проанализировать разработчик;
- возврат кода состояния, описания и другой информации, необходимой управляющей среде для выбора оптимального варианта действий.
Передача необработанного исключения
Блок, в котором может быть инициировано исключение, определяется правилами области действия исключений. В программе инициированное исключение распространяется в соответствии с определенными правилами.
Сначала PL/SQL ищет обработчик исключения в текущем блоке (анонимном блоке, процедуре или функции). Если такового нет, исключение передается в родительский блок. Затем PL/SQL пытается обработать исключение, инициировав его еще раз в родительском блоке. И так происходит в каждом внешнем по отношению к другому блоке до тех пор, пока все они не будут исчерпаны (рис. 2). После этого PL/SQL возвращает необработанное исключение в среду приложения, выполнившего «самый внешний» блок PL/SQL. И только теперь исключение может прервать выполнение основной программы.
Рис. 2. Передача исключений во вложенных блоках PL/SQL
Потеря информации об исключении
Структура процесса обработки локальных, определяемых программистом исключений в PL/SQL такова, что можно легко потерять информацию об исключении (то есть о том, какая именно произошла ошибка). Пример:
BEGIN
<<local_block>>
DECLARE
case_is_not_made EXCEPTION;
BEGIN
...
END local_block;
Допустим, мы забыли включить в этот блок раздел исключений. Область действия исключения case_is_not_made
ограничена блоком local_block
. Если исключение не обрабатывается в данном блоке, оно передается в родительский, где нет никакой информации о нем. Известно только то, что произошла ошибка, а какая именно — неизвестно. Ведь все пользовательские исключения имеют один и тот же номер ошибки 1 и одно и то же сообщение «User Defined Exception» — если только вы не воспользуетесь директивой EXCEPTION_INIT
, чтобы связать с объявленным исключением другой номер, и не присвоите ему другое сообщение об ошибке при вызове RAISE_APPLICATION_ERROR
.
Таким образом, локально объявленные (и инициированные) исключения всегда следует обрабатывать по имени.
Примеры передачи исключения
Рассмотрим несколько примеров передачи исключений через внешние блоки. На рис. 3 показано, как исключение too_many_faults
, инициированное во внутреннем блоке, обрабатывается в следующем — внешнем — блоке. Внутренний блок содержит раздел исключений, так что PL/SQL сначала проверяет, обрабатывается ли в этом разделе инициированное исключение too_many_faults
.
Рис. 3. Передача исключений во вложенных блоках PL/SQL
А поскольку оно не обрабатывается, PL/SQL закрывает этот блок и инициирует исключение too_many_faults
во внешнем блоке, обозначенном на рисунке как вложенный блок 1. (Используемые команды, расположенные после вложенного блока 2, не выполняются.) Затем просматривается раздел исключений этого блока с целью поиска обработчика исключения too_many_faults
, который обрабатывает его и передает управление процедуре list_my_faults
.
Обратите внимание: если исключение NO_DATA_FOUND
будет инициировано в «самом внутреннем» блоке, то оно будет обработано в разделе исключений этого же блока. Затем управление передается во вложенный блок 1 и будут выполнены исполняемые команды, расположенные после вложенного блока 2.
На рис. 4 представлен пример обработки в «самом внешнем» блоке исключения, инициированного во внутреннем блоке. В изображенной ситуации раздел исключений присутствует только во внешнем блоке, поэтому когда во вложенном блоке 2 инициируется исключение too_many_faults
, PL/SQL прекращает выполнение этого блока и инициирует данное исключение в его родительском блоке, то есть вложенном блоке 1. Но поскольку и у него нет раздела исключений, управление передается «самому внешнему» блоку, процедуре list_my_faults
. В этой процедуре имеется раздел исключений, поэтому PL/ SQL проверяет его, находит обработчик исключения too_many_faults
, выполняет имеющийся там код и передает управление программе, вызвавшей процедуру list_my_faults
.
Рис. 4. Исключение, инициированное во вложенном блоке,
обрабатывается в «самом внешнем» блоке
Продолжение выполнения после исключений
Когда в блоке PL/SQL инициируется исключение, нормальная последовательность выполнения программы прерывается, а управление передается в раздел исключений. Вернуться к исполняемому разделу блока после возникновения в нем исключения уже не удастся. Впрочем, в некоторых ситуациях требуется именно это — продолжить выполнение программы после обработки исключения.
Рассмотрим следующий сценарий: требуется написать процедуру, которая применяет серию операций DML к разным таблицам (удаление из одной таблицы, обновление другой, вставка в последнюю таблицу). На первый взгляд код мог бы выглядеть примерно так:
PROCEDURE change_data IS
BEGIN
DELETE FROM employees WHERE ... ;
UPDATE company SET ... ;
INSERT INTO company_history SELECT * FROM company WHERE ... ;
END;
Безусловно, процедура содержит все необходимые команды DML. Однако одно из требований к программе заключается в том, что при последовательном выполнении этих команд они должны быть логически независимы друг от друга. Другими словами, даже если при выполнении DELETE
произойдет сбой, программа должна выполнить UPDATE
и INSERT
.
В текущей версии change_data
ничто не гарантирует, что программа хотя бы попытается выполнить все три операции DML. Если при выполнении DELETE
произойдет исключение, например, то выполнение всей программы прервется, а управление будет передано в раздел исключений (если он имеется). Остальные команды SQL при этом выполняться не будут.
Как обеспечить обработку исключения без прерывания программы? Для этого DELETE
следует поместить в собственный блок PL/SQL. Рассмотрим следующую версию программы change_data
:
PROCEDURE change_data
IS
BEGIN
BEGIN
DELETE FROM employees WHERE ... ;
EXCEPTION
WHEN OTHERS THEN log_error;
END;
BEGIN
UPDATE company SET ... ;
EXCEPTION
WHEN OTHERS THEN log_error;
END;
BEGIN
INSERT INTO company_history SELECT * FROM company WHERE ... ;
EXCEPTION
WHEN OTHERS THEN log_error;
END;
END;
В новом варианте программы, если при выполнении DELETE
произойдет исключение, управление немедленно передается в раздел исключений. Но поскольку команда DELETE
теперь находится в собственном блоке, она может иметь собственный раздел исключений. Условие WHEN OTHERS
этого раздела обрабатывает ошибку без повторного инициирования этой или другой ошибки, после чего управление возвращается за пределы блока DELETE
внешней процедуре change_data
. Так как «активное» исключение отсутствует, выполнение продолжается во внешнем блоке со следующей команды процедуры. Программа входит в новый анонимный блок для команды UPDATE
. Если при выполнении UPDATE
произойдет ошибка, она будет перехвачена условием WHEN OTHERS
раздела исключений UPDATE
. Далее управление будет возвращено процедуре change_data
, которая перейдет к выполнению команды INSERT
(также содержащейся в собственном блоке).
На рис. 5 показано, как выполняется этот процесс для двух последовательно выполняемых команд DELETE
.
Рис. 5. Последовательное выполнение DELETE с разными областями действия
Подведем итог: исключение, инициированное в исполняемом разделе, всегда обрабатывается в текущем блоке (при наличии подходящего обработчика). Любую команду можно заключить в «виртуальный блок», заключив ее между ключевыми словами BEGIN
и END
с определением раздела EXCEPTION
. Это позволяет ограничить область действия сбоев в программе посредством определения «буферных» анонимных блоков.
Эту стратегию можно развить с выделением изолируемого кода в отдельные процедуры и функции. Конечно, именованные блоки PL/SQL тоже могут иметь собственные разделы исключений и предоставлять ту же защиту от общих сбоев. Важнейшее преимущество процедур и функций заключается в том, что они скрывают все команды BEGIN-EXCEPTION-END
от основной программы. Программа лучше читается, код проще сопровождать и повторно использовать в других контекстах.
Существуют и другие способы продолжить выполнение после исключения DML — например, можно использовать конструкцию SAVE EXCEPTIONS
с FORALL
и LOG ERRORS
в сочетании с DBMS_ERRORLOG
.
Написание раздела WHEN OTHERS
Условие WHEN OTHERS
включается в раздел исключений для перехвата всех исключений, не обработанных предшествующими обработчиками. Так как конкретный тип исключения изначально неизвестен, в WHEN OTHERS
очень часто используются встроенные функции для получения информации о возникшей ошибке (такие, как SQLCODE
и DBMS_UTILITY
. FORMAT_ERROR_STACK
).
В сочетании с WHEN OTHERS
функция SQLCODE
представляет средства для обработки разных видов исключений без применения директивы EXCEPTION_INIT
. В следующем примере перехватываются два исключения категории «родитель/потомок», −1 и −2292, и для каждой ситуации выполняется подходящее действие:
PROCEDURE add_company (
id_in IN company.ID%TYPE
, name_in IN company.name%TYPE
, type_id_in IN company.type_id%TYPE
)
IS
BEGIN
INSERT INTO company (ID, name, type_id)
VALUES (id_in, name_in, type_id_in);
EXCEPTION
WHEN OTHERS
THEN
/*
|| Анонимный блок в обработчике исключения позволяет объявить
|| локальные переменные для хранения информации о кодах ошибок.
*/
DECLARE
l_errcode PLS_INTEGER := SQLCODE;
BEGIN
CASE l_errcode
WHEN −1 THEN
-- Дублирующееся значение уникального индекса. Повторяется либо
-- первичный ключ, либо имя. Сообщить о проблеме
-- и инициировать исключение заново.
DBMS_OUTPUT.put_line
( 'идентификатор или имя компании уже используется. ID = '
|| TO_CHAR (id_in)
|| ' name = '
|| name_in
);
RAISE;
WHEN −2291 THEN
-- Родительский ключ не найден. Сообщить о проблеме
-- и инициировать исключение заново.
DBMS_OUTPUT.put_line (
'Недопустимый идентификатор типа компании: ' || TO_CHAR (type_id_in));
RAISE;
ELSE
RAISE;
END CASE;
END; -- Конец анонимного блока.
END add_company;
Будьте осторожны при использовании WHEN OTHERS
— этот раздел способен «поглощать» ошибки, скрывая их от внешних блоков и пользователя. А точнее, обращайте внимание на обработчики WHEN OTHERS
, которые не инициируют текущее исключение заново и не заменяют его другим исключением. Если WHEN OTHERS
не передает исключение наружу, внешние блоки вашей программы не узнают о возникшей ошибке.
В Oracle Database 11g появилось новое предупреждение, которое помогает выявлять программы, игнорирующие ошибки или поглощающие их:
PLW-06009: procedure "string" OTHERS handler does not end in RAISE or RAISE_
APPLICATION_ERROR
Пример использования этого предупреждения:
SQL> ALTER SESSION SET plsql_warnings = 'enable:all'
2 /
SQL> CREATE OR REPLACE PROCEDURE plw6009_demo
2 AS
3 BEGIN
4 DBMS_OUTPUT.put_line ('I am here!');
5 RAISE NO_DATA_FOUND;
6 EXCEPTION
7 WHEN OTHERS
8 THEN
9 NULL;
10 END plw6009_demo;
11 /
SP2-0804: Procedure created with compilation warnings
SQL> SHOW ERRORS
Errors for PROCEDURE PLW6009_DEMO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/9 PLW-06009: procedure "PLW6009_DEMO" OTHERS handler does not end
in RAISE or RAISE_APPLICATION_ERROR
Построение эффективной архитектуры управления ошибками
Механизм инициирования и обработки ошибок в PL/SQL отличается мощью и гибкостью, но он не лишен недостатков, которые могут создать проблемы для групп разработки, желающих реализовать надежную, последовательную, содержательную архитектуру управления ошибками. В частности, вы столкнетесь со следующими проблемами:
EXCEPTION
— особая разновидность структуры данных PL/SQL. Переменные, объявленные с типомEXCEPTION
, можно только инициировать и обрабатывать. Исключение нельзя передать в аргументе программы, с ним нельзя связать дополнительные атрибуты.- Повторное использование кода обработки исключений сильно затруднено. Из предыдущего пункта непосредственно следует другой факт: раз исключение нельзя передать в аргументе, разработчику приходится копировать код обработчика — конечно, такой способ написания кода никак не назовешь оптимальным.
- Не существует формализованного способа объявления исключений, которые могут инициироваться программой. Например, в Java эта информация становится частью спецификации программы. Как следствие, разработчику приходится обращаться к коду реализации и искать в нем информацию о потенциальных исключениях — или же надеяться на лучшее.
- Oracle не предоставляет средств организации и классификации исключений, относящихся к конкретному приложению, а просто резервирует (в основном) 1000 кодов в диапазоне от −20 999 до −20 000. Управлять этими значениями должен сам разработчик.
Давайте посмотрим, как преодолеть большинство из перечисленных трудностей.
Определение стратегии управления ошибками
Очень важно, чтобы еще до написания кода была выработана последовательная стратегия и архитектура обработки ошибок в приложении. Вот лишь некоторые вопросы, на которые необходимо ответить для этого:
- Как и когда сохранять информацию об ошибках для последующего просмотра и исправления? Куда выводить информацию — в файл, в таблицу базы данных? выводить на экран?
- Как и где сообщать об ошибках пользователю? Какую информацию должен получать пользователь? Как «перевести» часто невразумительные сообщения об ошибках, выдаваемые базой данных, на язык, понятный пользователям?
С этими общими вопросами тесно связаны более конкретные проблемы:
- Следует ли включать раздел обработки исключений в каждый блок PL/SQL?
- Следует ли включать раздел обработки исключений только в блок верхнего уровня или внешние блоки?
- Как организовать управление транзакциями при возникновении ошибок? Сложность обработки исключений отчасти связана с тем, что на все эти вопросы не существует единственно правильного ответа. Все зависит (по крайней мере частично) от архитектуры приложения и режима его использования (например, пакетное выполнение или транзакции, управляемые пользователем). Но если вы сможете ответить на эти вопросы для своего приложения, я рекомендую «запрограммировать» стратегию и правила обработки ошибок в стандартном пакете (см. далее «Стандартизация обработки ошибок»).
Некоторые общие принципы, которые стоит принять во внимание:
- Когда в коде происходит ошибка, получите как можно больше информации о контексте ее возникновения. Избыток информации — лучше, чем ее нехватка. Далее исключение можно передавать во внешние блоки, собирая дополнительную информацию по мере продвижения.
- Избегайте применения обработчиков вида
WHEN
ошибкаTHEN NULL
; (или еще хуже,WHEN OTHERS THEN NULL;
). Возможно, для написания такого хода у вас имеются веские причины, но вы должны твердо понимать, что это именно то, что вам нужно, и документировать такое использование, чтобы о нем знали другие. - Там, где это возможно, используйте механизмы обработки ошибок PL/SQL по умолчанию. Избегайте написания программ, возвращающих коды состояния управляющей среде или вызывающим блокам. Применять коды состояния следует только в одной ситуации: если управляющая среда не способна корректно обрабатывать ошибки Oracle (в таком случае стоит подумать о смене управляющей среды!).
Стандартизация обработки разных типов исключений
Исключение всегда свидетельствует о критической ситуации? Вовсе нет. Некоторые исключения (например, ORA-00600) сообщают о том, что в базе данных возникли очень серьезные низкоуровневые проблемы. Другие исключения, такие как NO_DATA_FOUND
, встречаются так часто, что мы воспринимаем их не как ошибки, а как условную логическую конструкцию («Если строка не существует, то выполнить следующие действия…»). Нужно ли различать эти категории исключений?
Коллеги-программисты научил меня очень полезной системе классификации исключений.
- Преднамеренные исключения. Архитектура кода сознательно использует особенности работы исключения. Это означает, что разработчик должен предвидеть исключение и запрограммировать его обработку. Пример —
UTL_FILE
.GET_LINE
. - Нежелательные исключения. Происходит ошибка, но ее возможность была предусмотрена заранее. Возможно, исключение даже не свидетельствует о возникновении проблемы. Пример команда
SELECT INTO
, инициирующая исключениеNO_DATA_FOUND
. - Непредвиденные исключения. Серьезные ошибки, указывающие на возникновение проблемы в приложении. Пример — команда
SELECT INTO
, которая должна вернуть строку для заданного первичного ключа, но вместо этого инициирует исключениеTOO_MANY
ROWS
.
Давайте поближе познакомимся с примерами всех категорий, а затем поговорим о том, какую пользу вы можете извлечь из знания этих категорий.
Преднамеренные исключения
Разработчики PL/SQL могут использовать процедуру UTL_FILE
.GET_LINE
для чтения содержимого файла по строкам. Когда GET_LINE
выходит за границу файла, инициируется исключение NO_DATA_FOUND
. Так работает эта процедура. Итак, если я хочу прочитать все содержимое файла и сделать «что-то полезное», программа может выглядеть так:
PROCEDURE read_file_and_do_stuff (
dir_in IN VARCHAR2, file_in IN VARCHAR2
)
IS
l_file UTL_FILE.file_type;
l_line VARCHAR2 (32767);
BEGIN
l_file := UTL_FILE.fopen (dir_in, file_in, 'R', max_linesize => 32767);
LOOP
UTL_FILE.get_line (l_file, l_line);
do_stuff;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose (l_file);
more_stuff_here;
END;
У этого цикла есть одна особенность: он не содержит команды EXIT
. Кроме того, в разделе исключений выполняется дополнительная логика приложения (more_stuff_here
). Цикл можно переписать в следующем виде:
LOOP
BEGIN
UTL_FILE.get_line (l_file, l_line);
do_stuff;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
UTL_FILE.flcose (l_file);
more_stuff_here;
Теперь цикл содержит команду EXIT
, но код стал более громоздким.
Подобные конструкции приходится использовать при работе с кодом, намеренно инициирующем исключения в своей архитектуре. Дополнительная информация о том, как следует поступать в подобных случаях, приводится в следующих разделах.
Нежелательные и непредвиденные исключения
Я рассматриваю эти две категории вместе, потому что приводимые примеры (NO_DATA_FOUND
и TOO_MANY_ROWS
) тесно связаны между собой. Предположим, я хочу написать функцию, возвращающую полное имя работника (в формате фамилия запятая имя) для заданного значения первичного ключа. Проще всего это сделать так:
FUNCTION fullname (
employee_id_in IN employees.employee_id%TYPE
)
RETURN VARCHAR2
IS
retval VARCHAR2 (32767);
BEGIN
SELECT last_name || ',' || first_name
INTO retval
FROM employees
WHERE employee_id = employee_id_in;
RETURN retval;
END fullname;
Если вызвать эту программу с кодом работника, отсутствующим в таблице, база данных инициирует исключение NO_DATA_FOUND
. Если же вызвать ее с кодом работника, встречающимся в нескольких строках таблицы, будет инициировано исключение TOO_MANY_ROWS
. Один запрос, два разных исключения — нужно ли рассматривать их одинаково? Вероятно, нет. Описывают ли эти два исключения похожие группы проблем? Давайте посмотрим:
NO_DATA_FOUND
— совпадение не найдено. Исключение может указывать на наличие серьезной проблемы, но не обязательно. Возможно, в большинстве обращений к базе данных совпадение не будет обнаруживаться, и я буду вставлять в базу данные нового работника. В общем, исключение нежелательно, но в данном случае оно даже не указывает на возникновение ошибки.TOO_MANY_ROWS
— в базе данных возникла серьезная проблема с ограничением первичного ключа. Трудно представить себе ситуацию, в которой это было бы нормально или просто «нежелательно». Нет, нужно прервать работу программы и привлечь внимание пользователя к совершенно непредвиденной, критической ошибке.
Как извлечь пользу из этой классификации
Надеюсь, вы согласитесь, что такая классификация полезна. Приступая к построению нового приложения, постарайтесь по возможности определиться со стандартным подходом, который будет применяться вами (и всеми остальными участниками группы) для каждого типа исключений. Затем для каждого исключения (которое необходимо обработать или хотя бы учитывать заранее при написании кода) решите, к какой категории относится, и примените уже согласованный подход. Все это поможет сделать ваш код более последовательным, и повысит эффективность вашей работы. Приведу несколько рекомендаций для трех типов исключений.
- Преднамеренные исключения. Пишите код, учитывающий возможность возникновения таких исключений. Прежде всего постарайтесь избежать размещения логики приложения в разделе исключений. Раздел исключений должен содержать только код, относящийся к обработке ошибки: сохранение информации об ошибке в журнале, повторное инициирование исключения и т. д. Программисты не ожидают увидеть логику приложения в разделе исключений, поэтому им будет намного труднее разобраться в таком коде и обеспечить его сопровождение.
- Нежелательные исключения. Если в каких-то обстоятельствах пользователь кода, инициировавшего исключения, не будет интерпретировать ситуацию как ошибку, не передавайте исключения наружу без обработки. Вместо этого верните значение или флаг состояния, показывающий, что исключение было обработано. Далее пользователь программы может сам решить, должна ли программа завершиться с ошибкой. А еще лучше — почему бы не разрешить стороне, вызывающей вашу программу, решить, нужно ли инициировать исключение, и если не нужно — какое значение должно передаваться для обозначения возникшего исключения?
- Непредвиденные исключения. А теперь начинается самое неприятное. Все непредвиденные ошибки должны быть сохранены в журнале с максимумом возможной контекстной информации, которая поможет понять причины возникновения ошибки. Затем программа должна завершиться с необработанным исключением (обычно тем же), инициированным из программы; для этого можно воспользоваться командой
RAISE
. Исключение заставит вызвавшую программу прервать работу и обработать ошибку.
Коды ошибок, связанные с конкретным приложением
Используя команду RAISE_APPLICATION_ERROR
для инициирования ошибок, относящихся к конкретному приложению, вы несете полную ответственность за управление кодами ошибок и сообщениями. Это быстро становится хлопотным и непростым делом («Так, какой бы код мне выбрать? Пожалуй, –20 774 — вроде бы такого еще не было?»).
Чтобы упростить управление кодами ошибок и предоставить последовательный интерфейс, через который разработчики смогут обрабатывать серверные ошибки, постройте таблицу со всеми используемыми кодами ошибок −20 NNN, сопутствующими именами исключений и сообщениями об ошибках.
Разработчик может просмотреть уже определенные ошибки на экране и выбрать ту из них, которая лучше всего подходит для конкретной ситуации.
Также можно попытаться полностью избегать диапазон −20 NNN для ошибок приложений. Почему бы не воспользоваться положительными числами? Из положительного цело-численного поддиапазона Oracle использует только 1 и 100. Теоретически возможно, что когда-нибудь Oracle будет использовать и другие положительные числа, но это весьма маловероятно. В распоряжении разработчиков остается великое множество кодов ошибок.
В частности, я пошел по этому пути при проектировании Quest Error Manager (QEM) — бесплатной программы управления ошибками. В Quest Error Manager вы можете определять свои ошибки в специальной таблице. Ошибка определяется именем и/ или кодом. Коды ошибок могут быть положительными или отрицательными. Если код ошибки положителен, при инициировании исключения QEM использует команду RAISE_APPLICATION_ERROR
для инициирования обобщенного исключения (обычно −20 000). Информация о текущем коде ошибки приложения встраивается в сообщение об ошибке, которое может быть расшифровано программой-получателем.
Упрощенная реализация этого подхода представлена в пакете обработки ошибок errpkg. pkg
, описанном в следующем разделе блога.
Стандартизация обработки ошибок
Обязательным элементом любого профессионально написанного приложения является надежная и согласованная схема обработки ошибок. Согласованность в этом вопросе важна как для пользователя, так и для разработчика. Если при возникновении ошибки пользователю предоставляется понятная, хорошо структурированная информация, он сможет более подробно рассказать об ошибке службе поддержки и будет более уверенно чувствовать себя при работе с приложением. Если приложение всегда обрабатывает и протоколирует ошибки определенным образом, программистам, занимающимся его поддержкой и сопровождением, будет легче их найти и устранить.
Все кажется вполне очевидным, не так ли? К сожалению, на практике (и особенно в больших группах разработчиков) все происходит несколько иначе. Очень часто каждый разработчик идет своим путем, следуя личным принципам и приемам, сохраняя информацию в произвольно выбранном формате и т. д. Одним словом, без стандартизации отладка и сопровождение приложений оборачиваются сущим кошмаром. Рассмотрим типичный пример:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_msg := 'Нет компании с идентификатором '||TO_CHAR (v_id);
v_err := SQLCODE;
v_prog := 'fixdebt';
INSERT INTO errlog VALUES
(v_err,v_msg,v_prog,SYSDATE,USER);
WHEN OTHERS
THEN
v_err := SQLCODE;
v_msg := SQLERRM;
v_prog := 'fixdebt';
INSERT INTO errlog VALUES
(v_err,v_msg,v_prog,SYSDATE,USER);
RAISE;
На первый взгляд код выглядит вполне разумно. Если компания с заданным идентификатором не найдена, мы получаем значение SQLCODE
, задаем имя программы и сообщение и записываем строку с информацией об ошибке в таблицу ошибок. Выполнение родительского блока продолжается, поскольку ошибка не критична. Если происходит любая другая ошибка, получаем ее код и соответствующее сообщение, задаем имя программы и записываем строку с информацией об ошибке в таблицу ошибок, а затем передаем исключение в родительский блок, чтобы остановить его выполнение (поскольку неизвестно, насколько критична эта ошибка).
Что же здесь не так? Чтобы подробно объяснить суть проблемы, достаточно взглянуть на код. В нем жестко закодированы все действия по обработке ошибок. В результате (1) код получается слишком объемистым, (2) его придется полностью переписывать при изменении схемы обработки ошибок. Обратите внимание еще и на тот факт, что информация об ошибке записывается в таблицу базы данных. Это означает, что запись в журнале становится частью логической транзакции. И если потребуется выполнить откат транзакции, записи в журнале ошибок будут утеряны.
Существует несколько способов избежать потери информации: можно записывать данные в файл или использовать автономные транзакции для сохранения журнала вне основной транзакции. Но как бы то ни было, код в случае его изменения придется исправлять в сотнях разных мест.
А теперь посмотрите, как этот же раздел исключений оформляется при использовании стандартизированного пакета:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
errpkg.record_and_continue (
SQLCODE, 'Нет компании с идентификатором ' || TO_CHAR (v_id));
WHEN OTHERS
THEN
errpkg.record_and_stop;
END;
Такой пакет обработки ошибок скрывает все подробности реализации; вы просто решаете, какая из процедур-обработчиков должна использоваться в конкретном случае, просматривая спецификацию пакета. Если требуется сохранить информацию об ошибке и продолжить работу, вызывается программа record_and_continue
. Если же нужно сохранить информацию об ошибке и прервать выполнение родительского блока, вызывается программа record_and_stop
. Мы не знаем, как эти программы сохраняют информацию об ошибке, как они останавливают работу родительского блока, то есть передают исключение, но для нас это и не важно. Главное, что все происходит так, как определено стандартами приложения.
Это дает вам возможность уделить больше времени разработке более интересных элементов приложения и не заниматься административной рутиной.
Имеется файл errpkg.pkg
с прототипом стандартизированного пакета обработки ошибок. Правда, прежде чем использовать его в приложениях, вам необходимо будет завершить его реализацию; это поможет составить ясное представление о том, как конструируются подобные утилиты.
Вы также можете воспользоваться намного более мощным (и тоже бесплатным) средством обработки ошибок Quest Error Manager. Важнейшая концепция, заложенная в основу QEM, заключается в возможности перехвата и протоколирования экземпляров ошибок, не только ошибок Oracle. QEM состоит из пакета PL/SQL и четырех таблиц для хранения информации об ошибках, возникающих в приложениях.
Работа с «объектами» исключений
Реализация типа данных EXCEPTION
в Oracle имеет свои ограничения, о которых было рассказано выше. Исключение состоит из идентификатора (имени), с которым связывается числовой код и сообщение. Исключение можно инициировать, его можно обработать… и все. Теперь представьте, как та же ситуация выглядит в Java: все ошибки являются производными от единого класса Exception
. Этот класс можно расширить, дополняя его новыми характеристиками, которые вы хотите отслеживать (стек ошибок, контекстные данные и т. д.). Объект, созданный на основе класса Exception, ничем не отличается от любых других объектов Java. Разумеется, он может передаваться в аргументах методов.
PL/SQL не позволяет делать ничего подобного со своими исключениями. Впрочем, этот факт не мешает вам реализовать свой «объект» исключения. Для этого можно воспользоваться объектными типами Oracle или реляционной таблицей, содержащей информацию об ошибке. Независимо от выбранной реализации очень важно различать определение ошибки (код ошибки –1403, имя «данные не найдены», причина — «неявный курсор не нашел ни одной записи») и ее конкретный экземпляр (я попытался найти компанию с указанным именем, ни одной строки не найдено). Иначе говоря, существует всего одно определение исключения NO_DATA_FOUND
, которое может существовать во множестве экземпляров. Oracle не различает эти два представления ошибки, но для нас это безусловно необходимо.
Пример простой иерархии объектов исключений продемонстрирует этот момент. Начнем с базового объектного типа всех исключений:
CREATE TYPE exception_t AS OBJECT (
name VARCHAR2(100),
code INTEGER,
description VARCHAR2(4000),
help_text VARCHAR2(4000),
recommendation VARCHAR2(4000),
error_stack CLOB,
call_stack CLOB,
created_on DATE,
created_by VARCHAR2(100)
)
NOT FINAL;
/
Затем базовый тип исключения расширяется для ошибок динамического SQL посредством добавления атрибута sql_string. При обработке ошибок динамического SQL очень важно сохранить строку, создавшую проблемы, для анализа в будущем:
CREATE TYPE dynsql_exception_t UNDER exception_t (
sql_string CLOB )
NOT FINAL;
/
А вот другой подтип exception_t
, на этот раз относящийся к конкретной сущности приложения — работнику. Исключение, инициируемое для ошибок, относящихся к работникам, будет включать идентификатор работника и внешний ключ нарушенного правила:
CREATE TYPE employee_exception_t UNDER exception_t (
employee_id INTEGER,
rule_id INTEGER );
/
Полная спецификация иерархии объектов ошибок включает методы супертипа исключения, предназначенные для вывода информации об ошибках или ее записи в репозиторий. Вы можете самостоятельно завершить иерархию, определенную в файле exception.ot
.
Если вы не хотите работать с объектными типами, попробуйте использовать подход, использованный мной в QEM: я определяю таблицу определений ошибок (Q$ERROR
) и другую таблицу экземпляров ошибок (Q$ERROR_INSTANCE
), которая содержит информацию о конкретных экземплярах ошибок. Все контекстные данные экземпляра ошибки сохраняются в таблице Q$ERROR_CONTEXT
.
Пример кода, который мог бы быть написан для QEM API:
WHEN DUP_VAL_ON_INDEX
THEN
q$error_manager.register_error (
error_name_in => 'DUPLICATE-VALUE'
,err_instance_id_out => l_err_instance_id
);
q$error_manager.add_context (
err_instance_id_in => l_err_instance_id
,name_in => 'TABLE_NAME', value_in => 'EMPLOYEES'
);
q$error_manager.add_context (
err_instance_id_in => l_err_instance_id
,name_in => 'KEY_VALUE', value_in => l_employee_id
);
q$error_manager.raise_error_instance (
err_instance_id_in => l_err_instance_id);
END;
Если ошибка повторяющегося значения была вызвана ограничением уникального имени, я получаю идентификатор экземпляра ошибки DUPLICATE-VALUE
. (Да, все верно: я использую имена ошибок, полностью обходя все проблемы, связанные с номерами ошибок.) Затем я добавляю контекстную информацию экземпляра (имя таблицы и значение первичного ключа, вызвавшее проблему). В завершение инициируется экземпляр ошибки, в результате чего исключение передается в следующий наружный блок.
По аналогии с передачей данных из приложения в репозиторий ошибок через API, вы также можете получить информацию об ошибке при помощи процедуры get_error_info
.
Пример:
BEGIN
run_my_application_code;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
l_error q$error_manager.error_info_rt;
BEGIN
q$error_manager.get_error_info (l_error);
DBMS_OUTPUT.put_line ('');
DBMS_OUTPUT.put_line ('Error in DEPT_SAL Procedure:');
DBMS_OUTPUT.put_line ('Code = ' || l_error.code);
DBMS_OUTPUT.put_line ('Name = ' || l_error.NAME);
DBMS_OUTPUT.put_line ('Text = ' || l_error.text);
DBMS_OUTPUT.put_line ('Error Stack = ' || l_error.error_stack);
END;
END;
Это лишь два из многих способов преодоления ограничений типа EXCEPTION
в PL/SQL. Мораль: ничто не заставляет вас мириться с ситуацией по умолчанию, при которой с экземпляром ошибки связывается только код и сообщение.
Создание стандартного шаблона для обобщенной обработки ошибок
Невозможность передачи исключений программе сильно усложняет совместное использование разделов обработки ошибок в разных блоках PL/SQL. Одну и ту же логику обработчика нередко приходится записывать снова и снова, особенно при работе с конкретными функциональными областями — скажем, файловым вводом/ выводом с UTL_FILE
. В таких ситуациях стоит выделить время на создание шаблонов обработчиков.
Давайте поближе познакомимся с UTL_FILE
. До выхода Oracle9i Database Release 2 в спецификации пакета UTL_FILE
определялся набор исключений. Однако компания Oracle не стала предоставлять коды этих исключений через директиву EXCEPTION_INIT
. А без обработки исключений UTL_FILE
по имени SQLCODE
не сможет разобраться, что пошло не так. Вероятно, в такой ситуации для программ UTL_FILE
можно создать шаблон, часть которого выглядит так:
DECLARE
l_file_id UTL_FILE.file_type;
PROCEDURE cleanup (file_in IN OUT UTL_FILE.file_type
,err_in IN VARCHAR2 := NULL)
IS
BEGIN
UTL_FILE.fclose (file_in);
IF err_in IS NOT NULL
THEN
DBMS_OUTPUT.put_line ('Обнаружена ошибка UTL_FILE:');
DBMS_OUTPUT.put_line (err_in);
END IF;
END cleanup;
BEGIN
-- Здесь размещается тело программы.
-- Перед выходом необходимо прибрать за собой ...
cleanup (l_file_id);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
cleanup (l_file_id, 'invalid_path');
RAISE;
WHEN UTL_FILE.invalid_mode
THEN
cleanup (l_file_id, 'invalid_mode');
RAISE;
END;
Основные элементы шаблона:
- Программа выполнения завершающих действий, пригодная для повторного использования; гарантирует, что текущий файл будет закрыт до потери дескриптора файла.
- Преобразование именованного исключения в строку, которую можно сохранить в журнале или вывести на экран, чтобы пользователь точно знал, какая ошибка была инициирована.
Рассмотрим еще один пример шаблона, который удобно использовать при работе с UTL_FILE
. В Oracle9i Database Release 2 появилась программа FREMOVE
для удаления файлов. Пакет UTL_FILE
предоставляет исключение DELETE_FAILED
, инициируемое тогда, когда FREMOVE
не удается удалить файл. После тестирования программы я обнаружил, что FREMOVE
может инициировать несколько возможных исключений, в числе которых:
UTL_FILE
.INVALID_OPERATION
— удаляемый файл не существует.UTL_FILE
.DELETE_FAILED
— у вас (или у процесса Oracle) недостаточно привилегий для удаления файла, или попытка завершилась неудачей по другой причине.
Начиная с Oracle9i Database Release 2, UTL_FILE
назначает коды ошибок всем своим исключениям, но вы все равно должны проследить за тем, чтобы при возникновении ошибки файлы были закрыты, и организовать последовательную обработку ошибок.
Итак, при использовании UTL_FILE
.FREMOVE
следует включать раздел обработчика исключения, который различает эти две ошибки:
BEGIN
UTL_FILE.fremove (dir, filename);
EXCEPTION
WHEN UTL_FILE.delete_failed
THEN
DBMS_OUTPUT.put_line (
'Ошибка при попытке удаления: ' || filename || ' в ' || dir);
-- Выполнение соответствующих действий...
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.put_line (
'Не удалось найти и удалить: ' || filename || ' в ' || dir);
-- Выполнение соответствующих действий...
END;
Оптимальная организация обработки ошибок в PL/SQL
Без унифицированной качественной методологии обработки ошибок очень трудно написать приложение, которое было бы удобным в использовании и одновременно простым в отладке.
Архитектура обработки ошибок в Oracle PL/SQL предоставляет очень гибкие средства для определения, инициирования и обработки ошибок. Однако у нее имеются свои ограничения, вследствие чего встроенную функциональность обычно приходится дополнять таблицами и кодами ошибок, специфическими для конкретного приложения.
Для решения проблемы обработки ошибок рекомендуется предпринять следующие действия:
- Тщательно разберитесь в системе инициирования и обработки ошибок в PL/SQL. Далеко не во ее аспекты интуитивно понятны. Простейший пример: исключение, инициированное в секции объявлений, не будет обрабатываться секцией исключений текущего блока.
- Выберите общую схему обработки ошибок в вашем приложении. Где и как будут обрабатываться ошибки? Какая информация об ошибке будет сохраняться и как это будет сделано? Как исключения будут передаваться в управляющую среду? Как будут обрабатываться намеренные и непредвиденные ошибки?
- Постройте стандартную инфраструктуру, которая будет использоваться всеми разработчиками проекта. Инфраструктура должна включать таблицы, пакеты и, возможно, объектные типы, а также четко определенный процесс использования всех перечисленных элементов. Не останавливайтесь на ограничениях PL/ SQL. Найдите обходные пути, расширяя модель обработки ошибок.
- Создайте шаблоны, которые могут использоваться всеми участниками вашей группы. Всегда проще следовать готовому стандарту, чем самостоятельно писать код обработки ошибок.
Жду отклика на статью. Что понравилось? Что нет?
Вас заинтересует / Intresting for you:
Summary: in this tutorial, you will learn how to use the PL/SQL RAISE
statement to raise a user-defined exception, internally defined exception, and reraising an exception.
To raise an exception explicitly, you use the RAISE
statement. The RAISE
statement allows you to:
- Raise a user-defined exception.
- Raise an internally defined exception.
- Reraising the current exception.
Raising a user-defined exception
A user-defined exception is defined by users like you or other developers in the declaration section of a block or subprogram.
Declaring a user-defined exception
To define a user-defined exception, you use the following syntax:
DECLARE
exception_name EXCEPTION;
Code language: SQL (Structured Query Language) (sql)
Similar to the variable declaration, you declare an exception in the declaration section of a block.
A user-defined exception must have assigned error_code
. To do it, you use the EXCEPTION_INIT
pragma as follows:
PRAGMA EXCEPTION_INIT (exception_name, error_code)
Code language: SQL (Structured Query Language) (sql)
In this syntax, the error_code
is an integer that ranges from -20,999
to -20,000
. And the message
is a character string with a maximum length of 2,048
bytes.
The entire syntax for declaring a user-defined exception is as follows:
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name, error_number);
Code language: SQL (Structured Query Language) (sql)
Raising a user-defined exception example
The following example illustrates how to declare a user-defined exception and associate it with an error code.
DECLARE
e_credit_too_high EXCEPTION;
PRAGMA exception_init( e_credit_too_high, -20001 );
l_max_credit customers.credit_limit%TYPE;
l_customer_id customers.customer_id%TYPE := &customer_id;
l_credit customers.credit_limit%TYPE := &credit_limit;
BEGIN
-- get the meax credit limit
SELECT MAX(credit_limit)
INTO l_max_credit
FROM customers;
-- check if input credit is greater than the max credit
IF l_credit > l_max_credit THEN
RAISE e_credit_too_high;
END IF;
-- if not, update credit limit
UPDATE customers
SET credit_limit = l_credit
WHERE customer_id = l_customer_id;
COMMIT;
END;
/
Code language: SQL (Structured Query Language) (sql)
In this example,
- First, declare a user-defined exception e_credit_too_high and associates it with the error number
-20001
. - Second, select maximum credit from the
customers
table using theMAX()
function and assign this value to thel_max_credit
variable. - Third, check if the input credit with the maximum credit, if the input credit is greater than the max, then raise the
e_credit_too_high
exception. - Finally, update the customer whose id is entered by the user with the new credit limit.
Here is the output if you enter customer id 100 and credit limit 20000
:
ORA-20001:
If you want to include a custom message, you can replace the line:
RAISE e_credit_too_high;
Code language: SQL (Structured Query Language) (sql)
by the following line:
raise_application_error(-20001,'Credit is too high');
Code language: SQL (Structured Query Language) (sql)
And execute the code block again, you will receive the following error:
ORA-20001: Credit is too high
Code language: SQL (Structured Query Language) (sql)
Raising an internally defined exception
Typically, the runtime system raises internally defined exceptions implicitly when they occur. Besides, you can explicitly raise an internally defined exception with the RAISE
statement if the exception has a name:
RAISE exception_name;
Code language: SQL (Structured Query Language) (sql)
This example shows how to raise an internally defined exception INVALID_NUMBER
:
DECLARE
l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
-- get the meax credit limit
IF l_customer_id < 0 THEN
RAISE invalid_number;
END IF;
END;
/
Code language: SQL (Structured Query Language) (sql)
If you execute the block and enter the customer id -10, you will get the following error:
ORA-01722: invalid number
Code language: SQL (Structured Query Language) (sql)
Reraising the current exception
You can re-raise the current exception with the RAISE
statement. Reraising an exception passes it to the enclosing block, which later can be handled further. To reraise an exception, you don’t need to specify the exception name.
DECLARE
e_credit_too_high EXCEPTION;
PRAGMA exception_init( e_credit_too_high, -20001 );
l_max_credit customers.credit_limit%TYPE;
l_customer_id customers.customer_id%TYPE := &customer_id;
l_credit customers.credit_limit%TYPE := &credit_limit;
BEGIN
BEGIN
-- get the max credit limit
SELECT MAX(credit_limit)
INTO l_max_credit
FROM customers;
-- check if input credit is greater than the max credit
IF l_credit > l_max_credit THEN
RAISE e_credit_too_high;
END IF;
EXCEPTION
WHEN e_credit_too_high THEN
dbms_output.put_line('The credit is too high' || l_credit);
RAISE; -- reraise the exception
END;
EXCEPTION
WHEN e_credit_too_high THEN
-- get average credit limit
SELECT avg(credit_limit)
into l_credit
from customers;
-- adjust the credit limit to the average
dbms_output.put_line('Adjusted credit to ' || l_credit);
-- update credit limit
UPDATE customers
SET credit_limit = l_credit
WHERE customer_id = l_customer_id;
COMMIT;
END;
/
Code language: SQL (Structured Query Language) (sql)
In this example:
- First, get the max credit limit from the
customers
table. - Second, compare the max credit with the user-input credit. If the user-input credit is greater than the max credit, then raise the
e_credit_too_high
exception. - Third, display a message and reraise the exception in the exception-handling section in the inner block.
- Finally, in the outer block, reassign the average credit to the
l_credit
variable and update the customer with the newly adjusted credit.
If you enter the customer id 100 and credit limit 10000, the credit limit of the customer will be updated to the average credit.
SELECT * FROM customers
WHERE customer_id = 100;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
In this tutorial, you have learned how to use the PL/SQL RAISE
statement to explicitly raise a user-defined exception, internally defined exception, and reraising an exception.
Was this tutorial helpful?
Improve Article
Save Article
Like Article
Improve Article
Save Article
Like Article
An exception is an error which disrupts the normal flow of program instructions. PL/SQL provides us the exception block which raises the exception thus helping the programmer to find out the fault and resolve it.
There are two types of exceptions defined in PL/SQL
- User defined exception.
- System defined exceptions.
Syntax to write an exception
WHEN exception THEN statement;
DECLARE
declarations section;BEGIN
executable command(s);EXCEPTION
WHEN exception1 THEN
statement1;
WHEN exception2 THEN
statement2;
[WHEN others THEN]
/* default exception handling code */END;
Note:
When other keyword should be used only at the end of the exception handling block as no exception handling part present later will get executed as the control will exit from the block after executing the WHEN OTHERS.
- System defined exceptions:
These exceptions are predefined in PL/SQL which get raised WHEN certain database rule is violated.
System-defined exceptions are further divided into two categories:- Named system exceptions.
- Unnamed system exceptions.
- Named system exceptions: They have a predefined name by the system like ACCESS_INTO_NULL, DUP_VAL_ON_INDEX, LOGIN_DENIED etc. the list is quite big.
So we will discuss some of the most commonly used exceptions:
Lets create a table geeks.
create table geeks(g_id int , g_name varchar(20), marks int); insert into geeks values(1, 'Suraj',100); insert into geeks values(2, 'Praveen',97); insert into geeks values(3, 'Jessie', 99);
- NO_DATA_FOUND: It is raised WHEN a SELECT INTO statement returns no rows. For eg:
DECLARE
temp
varchar
(20);
BEGIN
SELECT
g_id
into
temp
from
geeks
where
g_name=
'GeeksforGeeks'
;
exception
WHEN
no_data_found
THEN
dbms_output.put_line(
'ERROR'
);
dbms_output.put_line(
'there is no name as'
);
dbms_output.put_line(
'GeeksforGeeks in geeks table'
);
end
;
Output:
ERROR there is no name as GeeksforGeeks in geeks table
- TOO_MANY_ROWS:It is raised WHEN a SELECT INTO statement returns more than one row.
DECLARE
temp
varchar
(20);
BEGIN
SELECT
g_name
into
temp
from
geeks;
dbms_output.put_line(
temp
);
EXCEPTION
WHEN
too_many_rows
THEN
dbms_output.put_line(
'error trying to SELECT too many rows'
);
end
;
Output:
error trying to SELECT too many rows
- VALUE_ERROR:This error is raised WHEN a statement is executed that resulted in an arithmetic, numeric, string, conversion, or constraint error. This error mainly results from programmer error or invalid data input.
DECLARE
temp
number;
BEGIN
SELECT
g_name
into
temp
from
geeks
where
g_name=
'Suraj'
;
dbms_output.put_line(
'the g_name is '
||
temp
);
EXCEPTION
WHEN
value_error
THEN
dbms_output.put_line(
'Error'
);
dbms_output.put_line(
'Change data type of temp to varchar(20)'
);
END
;
Output:
Error Change data type of temp to varchar(20)
- ZERO_DIVIDE = raises exception WHEN dividing with zero.
DECLARE
a
int
:=10;
b
int
:=0;
answer
int
;
BEGIN
answer:=a/b;
dbms_output.put_line(
'the result after division is'
||answer);
exception
WHEN
zero_divide
THEN
dbms_output.put_line(
'dividing by zero please check the values again'
);
dbms_output.put_line(
'the value of a is '
||a);
dbms_output.put_line(
'the value of b is '
||b);
END
;
Output:
dividing by zero please check the values again the value of a is 10 the value of b is 0
- NO_DATA_FOUND: It is raised WHEN a SELECT INTO statement returns no rows. For eg:
- Unnamed system exceptions:Oracle doesn’t provide name for some system exceptions called unnamed system exceptions.These exceptions don’t occur frequently.These exceptions have two parts code and an associated message.
The way to handle to these exceptions is to assign name to them using Pragma EXCEPTION_INIT
Syntax:PRAGMA EXCEPTION_INIT(exception_name, -error_number);
error_number are pre-defined and have negative integer range from -20000 to -20999.
Example:
DECLARE
exp exception;
pragma exception_init (exp, -20015);
n
int
:=10;
BEGIN
FOR
i
IN
1..n LOOP
dbms_output.put_line(i*i);
IF i*i=36
THEN
RAISE exp;
END
IF;
END
LOOP;
EXCEPTION
WHEN
exp
THEN
dbms_output.put_line(
'Welcome to GeeksforGeeks'
);
END
;
Output:
1 4 9 16 25 36 Welcome to GeeksforGeeks
- User defined exceptions:
This type of users can create their own exceptions according to the need and to raise these exceptions explicitly raise command is used.Example:
- Divide non-negative integer x by y such that the result is greater than or equal to 1.
From the given question we can conclude that there exist two exceptions
- Division be zero.
- If result is greater than or equal to 1 means y is less than or equal to x.
DECLARE
x
int
:=&x; /*taking value
at
run
time
*/
y
int
:=&y;
div_r
float
;
exp1 EXCEPTION;
exp2 EXCEPTION;
BEGIN
IF y=0
then
raise exp1;
ELSEIF y > x
then
raise exp2;
ELSE
div_r:= x / y;
dbms_output.put_line(
'the result is '
||div_r);
END
IF;
EXCEPTION
WHEN
exp1
THEN
dbms_output.put_line(
'Error'
);
dbms_output.put_line(
'division by zero not allowed'
);
WHEN
exp2
THEN
dbms_output.put_line(
'Error'
);
dbms_output.put_line(
'y is greater than x please check the input'
);
END
;
Input 1: x = 20 y = 10 Output: the result is 2
Input 2: x = 20 y = 0 Output: Error division by zero not allowed
Input 3: x=20 y = 30 Output:<.em> Error y is greater than x please check the input
RAISE_APPLICATION_ERROR:
It is used to display user-defined error messages with error number whose range is in between -20000 and -20999. When RAISE_APPLICATION_ERROR executes it returns error message and error code which looks same as Oracle built-in error.Example:
DECLARE
myex EXCEPTION;
n NUMBER :=10;
BEGIN
FOR
i
IN
1..n LOOP
dbms_output.put_line(i*i);
IF i*i=36
THEN
RAISE myex;
END
IF;
END
LOOP;
EXCEPTION
WHEN
myex
THEN
RAISE_APPLICATION_ERROR(-20015,
'Welcome to GeeksForGeeks'
);
END
;
Output:
Error report: ORA-20015: Welcome to GeeksForGeeks ORA-06512: at line 13 1 4 9 16 25 36
Note: The output is based on Oracle Sql developer, the output order might change IF you’re running this code somewhere else.
Scope rules in exception handling:
- We can’t DECLARE an exception twice but we can DECLARE the same exception in two dIFferent blocks.
- Exceptions DECLAREd inside a block are local to that block and global to all its sub-blocks.
As a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions DECLAREd in a sub-block.
If we reDECLARE a global exception in a sub-block, the local declaration prevails i.e. the scope of local is more.Example:
DECLARE
GeeksforGeeks EXCEPTION;
age NUMBER:=16;
BEGIN
DECLARE
GeeksforGeeks EXCEPTION;
age NUMBER:=22;
BEGIN
IF age > 16
THEN
RAISE GeeksforGeeks; /* this
is
not
handled*/
END
IF;
END
;
EXCEPTION
WHEN
GeeksforGeeks
THEN
DBMS_OUTPUT.PUT_LINE
(
'Handling GeeksforGeeks exception.'
);
WHEN
OTHERS
THEN
DBMS_OUTPUT.PUT_LINE
(
'Could not recognize exception GeeksforGeeks in this scope.'
);
END
;
Output:
Could not recognize exception GeeksforGeeks in this scope.
- Divide non-negative integer x by y such that the result is greater than or equal to 1.
Advantages:
- Exception handling is very useful for error handling, without it we have to issue the command at every point to check for execution errors:
Example:Select .. .. check for 'no data found' error Select .. .. check for 'no data found' error Select .. .. check for 'no data found' error
Here we can see that it is not robust as error processing is not separated from normal processing and IF we miss some line in the code than it may lead to some other kind of error.
- With exception handling we handle errors without writing statements multiple times and we can even handle dIFferent types of errors in one exception block:
Example:BEGIN SELECT ... SELECT ... SELECT ... . . . exception WHEN NO_DATA_FOUND THEN /* catches all 'no data found' errors */ ... WHEN ZERO_DIVIDE THEN /* different types of */ WHEN value_error THEN /* errors handled in same block */ ...
From above code we can conclude that exception handling
- Improves readability by letting us isolate error-handling routines and thus providing robustness.
- Provides reliability, instead of checking for dIFferent types of errors at every point we can simply write them in exception block and IF error exists exception will be raised thus helping the programmer to find out the type of error and eventually resolve it.
Uses: One of the real lIFe use of exception can be found in online train reservation system.
While filling the station code to book the ticket IF we input wrong code it shows us the exception that the code doesn’t exist in database.
Reference: You can find the list of all pre-defined exception here.
Total number of pre-defined exceptions
Last Updated :
28 Apr, 2018
Like Article
Save Article
11.1 Compile-Time Warnings
While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation—for example, using a deprecated PL/SQL feature.
To see warnings (and errors) generated during compilation, either query the static data dictionary view *_ERRORS
or, in the SQL*Plus environment, use the command SHOW
ERRORS
.
The message code of a PL/SQL warning has the form PLW-nnnnn.
Table 11-1 Compile-Time Warning Categories
Category | Description | Example |
---|---|---|
|
Condition might cause unexpected action or wrong results. |
Aliasing problems with parameters |
|
Condition might cause performance problems. |
Passing a |
|
Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable. |
Code that can never run |
By setting the compilation parameter PLSQL_WARNINGS
, you can:
-
Enable and disable all warnings, one or more categories of warnings, or specific warnings
-
Treat specific warnings as errors (so that those conditions must be corrected before you can compile the PL/SQL unit)
You can set the value of PLSQL_WARNINGS
for:
-
Your Oracle database instance
Use the
ALTER
SYSTEM
statement, described in Oracle Database SQL Language Reference. -
Your session
Use the
ALTER
SESSION
statement, described in Oracle Database SQL Language Reference. -
A stored PL/SQL unit
Use an
ALTER
statement from «ALTER Statements» with itscompiler_parameters_clause
.
In any of the preceding ALTER
statements, you set the value of PLSQL_WARNINGS
with this syntax:
PLSQL_WARNINGS = 'value_clause' [, 'value_clause' ] ...
For the syntax of value_clause
, see Oracle Database Reference.
To display the current value of PLSQL_WARNINGS
, query the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS
.
See Also:
-
Oracle Database Reference for more information about the static data dictionary view
ALL_PLSQL_OBJECT_SETTINGS
-
Oracle Database Error Messages Reference for the message codes of all PL/SQL warnings
-
Oracle Database Reference for more information about the static data dictionary view
*_ERRORS
-
«PL/SQL Units and Compilation Parameters» for more information about PL/SQL units and compiler parameters
Example 11-1 Setting Value of PLSQL_WARNINGS Compilation Parameter
This example shows several ALTER
statements that set the value of PLSQL_WARNINGS
.
For the session, enable all warnings—highly recommended during development:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
For the session, enable PERFORMANCE
warnings:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
For the procedure loc_var
, enable PERFORMANCE
warnings, and reuse settings:
ALTER PROCEDURE loc_var COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE' REUSE SETTINGS;
For the session, enable SEVERE
warnings, disable PERFORMANCE
warnings, and treat PLW-06002 warnings as errors:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE', 'ERROR:06002';
For the session, disable all warnings:
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
11.1.1 DBMS_WARNING Package
If you are writing PL/SQL units in a development environment that compiles them (such as SQL*Plus), you can display and set the value of PLSQL_WARNINGS
by invoking subprograms in the DBMS_WARNING
package.
Example 11-2 uses an ALTER
SESSION
statement to disable all warning messages for the session and then compiles a procedure that has unreachable code. The procedure compiles without warnings. Next, the example enables all warnings for the session by invoking DBMS_WARNING.set_warning_setting_string
and displays the value of PLSQL_WARNINGS
by invoking DBMS_WARNING.get_warning_setting_string
. Finally, the example recompiles the procedure, and the compiler generates a warning about the unreachable code.
Note:
Unreachable code could represent a mistake or be intentionally hidden by a debug flag.
DBMS_WARNING
subprograms are useful when you are compiling a complex application composed of several nested SQL*Plus scripts, where different subprograms need different PLSQL_WARNINGS
settings. With DBMS_WARNING
subprograms, you can save the current PLSQL_WARNINGS
setting, change the setting to compile a particular set of subprograms, and then restore the setting to its original value.
Example 11-2 Displaying and Setting PLSQL_WARNINGS with DBMS_WARNING Subprograms
Disable all warning messages for this session:
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
With warnings disabled, this procedure compiles with no warnings:
CREATE OR REPLACE PROCEDURE unreachable_code AUTHID DEFINER AS x CONSTANT BOOLEAN := TRUE; BEGIN IF x THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; END unreachable_code; /
Enable all warning messages for this session:
CALL DBMS_WARNING.set_warning_setting_string ('ENABLE:ALL', 'SESSION');
Check warning setting:
SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL;
Result:
DBMS_WARNING.GET_WARNING_SETTING_STRING() ----------------------------------------- ENABLE:ALL 1 row selected.
Recompile procedure:
ALTER PROCEDURE unreachable_code COMPILE;
Result:
SP2-0805: Procedure altered with compilation warnings
Show errors:
SHOW ERRORS
Result:
Errors for PROCEDURE UNREACHABLE_CODE: LINE/COL ERROR -------- ----------------------------------------------------------------- 7/5 PLW-06002: Unreachable code
11.2 Overview of Exception Handling
Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources. You cannot anticipate all possible exceptions, but you can write exception handlers that let your program to continue to operate in their presence.
Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers. For example, an exception-handling part could have this syntax:
EXCEPTION WHEN ex_name_1 THEN statements_1 -- Exception handler WHEN ex_name_2 OR ex_name_3 THEN statements_2 -- Exception handler WHEN OTHERS THEN statements_3 -- Exception handler END;
In the preceding syntax example, ex_name_
n
is the name of an exception and statements_
n
is one or more statements. (For complete syntax and semantics, see «Exception Handler».)
When an exception is raised in the executable part of the block, the executable part stops and control transfers to the exception-handling part. If ex_name_1
was raised, then statements_1
run. If either ex_name_2
or ex_name_3
was raised, then statements_2
run. If any other exception was raised, then statements_3
run.
After an exception handler runs, control transfers to the next statement of the enclosing block. If there is no enclosing block, then:
-
If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation.
-
If the exception handler is in an anonymous block, then control transfers to the host environment (for example, SQL*Plus)
If an exception is raised in a block that has no exception handler for it, then the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a block has a handler for it or there is no enclosing block (for more information, see «Exception Propagation»). If there is no handler for the exception, then PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome (for more information, see «Unhandled Exceptions»).
Topics
-
Exception Categories
-
Advantages of Exception Handlers
-
Guidelines for Avoiding and Handling Exceptions
11.2.1 Exception Categories
The exception categories are:
-
Internally defined
The runtime system raises internally defined exceptions implicitly (automatically). Examples of internally defined exceptions are ORA-00060 (deadlock detected while waiting for resource) and ORA-27102 (out of memory).
An internally defined exception always has an error code, but does not have a name unless PL/SQL gives it one or you give it one.
For more information, see «Internally Defined Exceptions».
-
Predefined
A predefined exception is an internally defined exception that PL/SQL has given a name. For example, ORA-06500 (PL/SQL: storage error) has the predefined name
STORAGE_ERROR
.For more information, see «Predefined Exceptions».
-
User-defined
You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package. For example, you might declare an exception named
insufficient_funds
to flag overdrawn bank accounts.You must raise user-defined exceptions explicitly.
For more information, see «User-Defined Exceptions».
Table 11-2 summarizes the exception categories.
Table 11-2 Exception Categories
Category | Definer | Has Error Code | Has Name | Raised Implicitly | Raised Explicitly |
---|---|---|---|---|---|
Internally defined |
Runtime system |
Always |
Only if you assign one |
Yes |
OptionallyFoot 1 |
Predefined |
Runtime system |
Always |
Always |
Yes |
OptionallyFootref 1 |
User-defined |
User |
Only if you assign one |
Always |
No |
Always |
Footnote 1
For details, see «Raising Internally Defined Exception with RAISE Statement».
For a named exception, you can write a specific exception handler, instead of handling it with an OTHERS
exception handler. A specific exception handler is more efficient than an OTHERS
exception handler, because the latter must invoke a function to determine which exception it is handling. For details, see «Retrieving Error Code and Error Message».
11.2.2 Advantages of Exception Handlers
Using exception handlers for error-handling makes programs easier to write and understand, and reduces the likelihood of unhandled exceptions.
Without exception handlers, you must check for every possible error, everywhere that it might occur, and then handle it. It is easy to overlook a possible error or a place where it might occur, especially if the error is not immediately detectable (for example, bad data might be undetectable until you use it in a calculation). Error-handling code is scattered throughout the program.
With exception handlers, you need not know every possible error or everywhere that it might occur. You need only include an exception-handling part in each block where errors might occur. In the exception-handling part, you can include exception handlers for both specific and unknown errors. If an error occurs anywhere in the block (including inside a sub-block), then an exception handler handles it. Error-handling code is isolated in the exception-handling parts of the blocks.
In Example 11-3, a procedure uses a single exception handler to handle the predefined exception NO_DATA_FOUND
, which can occur in either of two SELECT
INTO
statements.
If multiple statements use the same exception handler, and you want to know which statement failed, you can use locator variables, as in Example 11-4.
You determine the precision of your error-handling code. You can have a single exception handler for all division-by-zero errors, bad array indexes, and so on. You can also check for errors in a single statement by putting that statement inside a block with its own exception handler.
Example 11-3 Single Exception Handler for Multiple Exceptions
CREATE OR REPLACE PROCEDURE select_item ( t_column VARCHAR2, t_name VARCHAR2 ) AUTHID DEFINER IS temp VARCHAR2(30); BEGIN temp := t_column; -- For error message if next SELECT fails -- Fails if table t_name does not have column t_column: SELECT COLUMN_NAME INTO temp FROM USER_TAB_COLS WHERE TABLE_NAME = UPPER(t_name) AND COLUMN_NAME = UPPER(t_column); temp := t_name; -- For error message if next SELECT fails -- Fails if there is no table named t_name: SELECT OBJECT_NAME INTO temp FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER(t_name) AND OBJECT_TYPE = 'TABLE'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Unexpected error'); RAISE; END; /
Invoke procedure (there is a DEPARTMENTS
table, but it does not have a LAST_NAME
column):
BEGIN select_item('departments', 'last_name'); END; /
Result:
No Data found for SELECT on departments
Invoke procedure (there is no EMP
table):
BEGIN select_item('emp', 'last_name'); END; /
Result:
No Data found for SELECT on emp
Example 11-4 Locator Variables for Statements that Share Exception Handler
CREATE OR REPLACE PROCEDURE loc_var AUTHID DEFINER IS stmt_no POSITIVE; name_ VARCHAR2(100); BEGIN stmt_no := 1; SELECT table_name INTO name_ FROM user_tables WHERE table_name LIKE 'ABC%'; stmt_no := 2; SELECT table_name INTO name_ FROM user_tables WHERE table_name LIKE 'XYZ%'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Table name not found in query ' || stmt_no); END; / CALL loc_var();
Result:
Table name not found in query 1
11.2.3 Guidelines for Avoiding and Handling Exceptions
To make your programs as reliable and safe as possible:
-
Use both error-checking code and exception handlers.
Use error-checking code wherever bad input data can cause an error. Examples of bad input data are incorrect or null actual parameters and queries that return no rows or more rows than you expect. Test your code with different combinations of bad input data to see what potential errors arise.
Sometimes you can use error-checking code to avoid raising an exception, as in Example 11-7.
-
Add exception handlers wherever errors can occur.
Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Errors can also arise from problems that are independent of your code—for example, disk storage or memory hardware failure—but your code still must take corrective action.
-
Design your programs to work when the database is not in the state you expect.
For example, a table you query might have columns added or deleted, or their types might have changed. You can avoid problems by declaring scalar variables with
%TYPE
qualifiers and record variables to hold query results with%ROWTYPE
qualifiers. -
Whenever possible, write exception handlers for named exceptions instead of using
OTHERS
exception handlers.Learn the names and causes of the predefined exceptions. If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them.
-
Have your exception handlers output debugging information.
If you store the debugging information in a separate table, do it with an autonomous routine, so that you can commit your debugging information even if you roll back the work that the main subprogram did. For information about autonomous routines, see «AUTONOMOUS_TRANSACTION Pragma».
-
For each exception handler, carefully decide whether to have it commit the transaction, roll it back, or let it continue.
Regardless of the severity of the error, you want to leave the database in a consistent state and avoid storing bad data.
-
Avoid unhandled exceptions by including an
OTHERS
exception handler at the top level of every PL/SQL program.Make the last statement in the
OTHERS
exception handler eitherRAISE
or an invocation of theRAISE_APPLICATION_ERROR
procedure. (If you do not follow this practice, and PL/SQL warnings are enabled, then you get PLW-06009.) For information aboutRAISE
or an invocation of theRAISE_APPLICATION_ERROR
, see «Raising Exceptions Explicitly».
11.3 Internally Defined Exceptions
Internally defined exceptions (ORA-n errors) are described in Oracle Database Error Messages Reference. The runtime system raises them implicitly (automatically).
An internally defined exception does not have a name unless either PL/SQL gives it one (see «Predefined Exceptions») or you give it one.
If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them. Otherwise, you can handle them only with OTHERS
exception handlers.
To give a name to an internally defined exception, do the following in the declarative part of the appropriate anonymous block, subprogram, or package. (To determine the appropriate block, see «Exception Propagation».)
-
Declare the name.
An exception name declaration has this syntax:
exception_name EXCEPTION;
For semantic information, see «Exception Declaration».
-
Associate the name with the error code of the internally defined exception.
The syntax is:
PRAGMA EXCEPTION_INIT (exception_name, error_code)
For semantic information, see «EXCEPTION_INIT Pragma».
Note:
An internally defined exception with a user-declared name is still an internally defined exception, not a user-defined exception.
Example 11-5 gives the name deadlock_detected
to the internally defined exception ORA-00060 (deadlock detected while waiting for resource) and uses the name in an exception handler.
Example 11-5 Naming Internally Defined Exception
DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN ... EXCEPTION WHEN deadlock_detected THEN ... END; /
11.4 Predefined Exceptions
Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD
. The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.
Table 11-3 lists the names and error codes of the predefined exceptions.
Table 11-3 PL/SQL Predefined Exceptions
Exception Name | Error Code |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Example 11-6 calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE
and the executable part of the block transfers control to the exception-handling part.
Example 11-7 uses error-checking code to avoid the exception that Example 11-6 handles.
In Example 11-8, the procedure opens a cursor variable for either the EMPLOYEES
table or the DEPARTMENTS
table, depending on the value of the parameter discrim
. The anonymous block invokes the procedure to open the cursor variable for the EMPLOYEES
table, but fetches from the DEPARTMENTS
table, which raises the predefined exception ROWTYPE_MISMATCH
.
Example 11-6 Anonymous Block Handles ZERO_DIVIDE
DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN pe_ratio := stock_price / net_earnings; -- raises ZERO_DIVIDE exception DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Company had zero earnings.'); pe_ratio := NULL; END; /
Result:
Company had zero earnings.
Example 11-7 Anonymous Block Avoids ZERO_DIVIDE
DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN pe_ratio := CASE net_earnings WHEN 0 THEN NULL ELSE stock_price / net_earnings END; END; /
Example 11-8 Anonymous Block Handles ROWTYPE_MISMATCH
CREATE OR REPLACE PACKAGE emp_dept_data AUTHID DEFINER AS TYPE cv_type IS REF CURSOR; PROCEDURE open_cv ( cv IN OUT cv_type, discrim IN POSITIVE ); END emp_dept_data; / CREATE OR REPLACE PACKAGE BODY emp_dept_data AS PROCEDURE open_cv ( cv IN OUT cv_type, discrim IN POSITIVE) IS BEGIN IF discrim = 1 THEN OPEN cv FOR SELECT * FROM EMPLOYEES ORDER BY employee_id; ELSIF discrim = 2 THEN OPEN cv FOR SELECT * FROM DEPARTMENTS ORDER BY department_id; END IF; END open_cv; END emp_dept_data; /
Invoke procedure open_cv
from anonymous block:
DECLARE emp_rec EMPLOYEES%ROWTYPE; dept_rec DEPARTMENTS%ROWTYPE; cv Emp_dept_data.CV_TYPE; BEGIN emp_dept_data.open_cv(cv, 1); -- Open cv for EMPLOYEES fetch. FETCH cv INTO dept_rec; -- Fetch from DEPARTMENTS. DBMS_OUTPUT.PUT(dept_rec.DEPARTMENT_ID); DBMS_OUTPUT.PUT_LINE(' ' || dept_rec.LOCATION_ID); EXCEPTION WHEN ROWTYPE_MISMATCH THEN BEGIN DBMS_OUTPUT.PUT_LINE ('Row type mismatch, fetching EMPLOYEES data ...'); FETCH cv INTO emp_rec; DBMS_OUTPUT.PUT(emp_rec.DEPARTMENT_ID); DBMS_OUTPUT.PUT_LINE(' ' || emp_rec.LAST_NAME); END; END; /
Result:
Row type mismatch, fetching EMPLOYEES data ...
90 King
11.5 User-Defined Exceptions
You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package.
An exception name declaration has this syntax:
exception_name EXCEPTION;
For semantic information, see «Exception Declaration».
You must raise a user-defined exception explicitly. For details, see «Raising Exceptions Explicitly».
11.6 Redeclared Predefined Exceptions
Oracle recommends against redeclaring predefined exceptions—that is, declaring a user-defined exception name that is a predefined exception name. (For a list of predefined exception names, see Table 11-3.)
If you redeclare a predefined exception, your local declaration overrides the global declaration in package STANDARD
. Exception handlers written for the globally declared exception become unable to handle it—unless you qualify its name with the package name STANDARD
.
Example 11-9 shows this.
Example 11-9 Redeclared Predefined Identifier
DROP TABLE t; CREATE TABLE t (c NUMBER);
In the following block, the INSERT
statement implicitly raises the predefined exception INVALID_NUMBER
, which the exception handler handles.
DECLARE default_number NUMBER := 0; BEGIN INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); EXCEPTION WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.'); INSERT INTO t VALUES(default_number); END; /
Result:
Substituting default value for invalid number.
The following block redeclares the predefined exception INVALID_NUMBER
. When the INSERT
statement implicitly raises the predefined exception INVALID_NUMBER
, the exception handler does not handle it.
DECLARE default_number NUMBER := 0; i NUMBER := 5; invalid_number EXCEPTION; -- redeclare predefined exception BEGIN INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); EXCEPTION WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.'); INSERT INTO t VALUES(default_number); END; /
Result:
DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 6
The exception handler in the preceding block handles the predefined exception INVALID_NUMBER
if you qualify the exception name in the exception handler:
DECLARE default_number NUMBER := 0; i NUMBER := 5; invalid_number EXCEPTION; -- redeclare predefined exception BEGIN INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); EXCEPTION WHEN STANDARD.INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.'); INSERT INTO t VALUES(default_number); END; /
Result:
Substituting default value for invalid number.
11.7 Raising Exceptions Explicitly
11.7.1 RAISE Statement
11.7.1.1 Raising User-Defined Exception with RAISE Statement
In Example 11-10, the procedure declares an exception named past_due
, raises it explicitly with the RAISE
statement, and handles it with an exception handler.
Example 11-10 Declaring, Raising, and Handling User-Defined Exception
CREATE PROCEDURE account_status ( due_date DATE, today DATE ) AUTHID DEFINER IS past_due EXCEPTION; -- declare exception BEGIN IF due_date < today THEN RAISE past_due; -- explicitly raise exception END IF; EXCEPTION WHEN past_due THEN -- handle exception DBMS_OUTPUT.PUT_LINE ('Account past due.'); END; / BEGIN account_status (TO_DATE('01-JUL-2010', 'DD-MON-YYYY'), TO_DATE('09-JUL-2010', 'DD-MON-YYYY')); END; /
Result:
Account past due.
11.7.1.2 Raising Internally Defined Exception with RAISE Statement
Although the runtime system raises internally defined exceptions implicitly, you can raise them explicitly with the RAISE
statement if they have names. Table 11-3 lists the internally defined exceptions that have predefined names. «Internally Defined Exceptions» explains how to give user-declared names to internally defined exceptions.
An exception handler for a named internally defined exception handles that exception whether it is raised implicitly or explicitly.
In Example 11-11, the procedure raises the predefined exception INVALID_NUMBER
either explicitly or implicitly, and the INVALID_NUMBER
exception handler always handles it.
Example 11-11 Explicitly Raising Predefined Exception
DROP TABLE t; CREATE TABLE t (c NUMBER); CREATE PROCEDURE p (n NUMBER) AUTHID DEFINER IS default_number NUMBER := 0; BEGIN IF n < 0 THEN RAISE INVALID_NUMBER; -- raise explicitly ELSE INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); -- raise implicitly END IF; EXCEPTION WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.'); INSERT INTO t VALUES(default_number); END; / BEGIN p(-1); END; /
Result:
Substituting default value for invalid number. BEGIN p(1); END; /
Result:
Substituting default value for invalid number.
11.7.1.3 Reraising Current Exception with RAISE Statement
In an exception handler, you can use the RAISE
statement to»reraise» the exception being handled. Reraising the exception passes it to the enclosing block, which can handle it further. (If the enclosing block cannot handle the reraised exception, then the exception propagates—see «Exception Propagation».) When reraising the current exception, you need not specify an exception name.
In Example 11-12, the handling of the exception starts in the inner block and finishes in the outer block. The outer block declares the exception, so the exception name exists in both blocks, and each block has an exception handler specifically for that exception. The inner block raises the exception, and its exception handler does the initial handling and then reraises the exception, passing it to the outer block for further handling.
Example 11-12 Reraising Exception
DECLARE salary_too_high EXCEPTION; current_salary NUMBER := 20000; max_salary NUMBER := 10000; erroneous_salary NUMBER; BEGIN BEGIN IF current_salary > max_salary THEN RAISE salary_too_high; -- raise exception END IF; EXCEPTION WHEN salary_too_high THEN -- start handling exception erroneous_salary := current_salary; DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary ||' is out of range.'); DBMS_OUTPUT.PUT_LINE ('Maximum salary is ' || max_salary || '.'); RAISE; -- reraise current exception (exception name is optional) END; EXCEPTION WHEN salary_too_high THEN -- finish handling exception current_salary := max_salary; DBMS_OUTPUT.PUT_LINE ( 'Revising salary from ' || erroneous_salary || ' to ' || current_salary || '.' ); END; /
Result:
Salary 20000 is out of range. Maximum salary is 10000. Revising salary from 20000 to 10000.
11.7.2 RAISE_APPLICATION_ERROR Procedure
You can invoke the RAISE_APPLICATION_ERROR
procedure (defined in the DBMS_STANDARD
package) only from a stored subprogram or method. Typically, you invoke this procedure to raise a user-defined exception and return its error code and error message to the invoker.
To invoke RAISE_APPLICATION_ERROR
, use this syntax:
RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);
You must have assigned error_code
to the user-defined exception with the EXCEPTION_INIT
pragma. The syntax is:
PRAGMA EXCEPTION_INIT (exception_name, error_code)
The error_code
is an integer in the range -20000..-20999 and the message
is a character string of at most 2048 bytes.
For semantic information, see «EXCEPTION_INIT Pragma».
The message
is a character string of at most 2048 bytes.
If you specify TRUE
, PL/SQL puts error_code
on top of the error stack. Otherwise, PL/SQL replaces the error stack with error_code
.
In Example 11-13, an anonymous block declares an exception named past_due
, assigns the error code -20000 to it, and invokes a stored procedure. The stored procedure invokes the RAISE_APPLICATION_ERROR
procedure with the error code -20000 and a message, whereupon control returns to the anonymous block, which handles the exception. To retrieve the message associated with the exception, the exception handler in the anonymous block invokes the SQLERRM
function, described in «Retrieving Error Code and Error Message».
Example 11-13 Raising User-Defined Exception with RAISE_APPLICATION_ERROR
CREATE PROCEDURE account_status ( due_date DATE, today DATE ) AUTHID DEFINER IS BEGIN IF due_date < today THEN -- explicitly raise exception RAISE_APPLICATION_ERROR(-20000, 'Account past due.'); END IF; END; / DECLARE past_due EXCEPTION; -- declare exception PRAGMA EXCEPTION_INIT (past_due, -20000); -- assign error code to exception BEGIN account_status (TO_DATE('01-JUL-2010', 'DD-MON-YYYY'), TO_DATE('09-JUL-2010', 'DD-MON-YYYY')); -- invoke procedure EXCEPTION WHEN past_due THEN -- handle exception DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000))); END; /
Result:
ORA-20000: Account past due.
11.8 Exception Propagation
If an exception is raised in a block that has no exception handler for it, then the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until either a block has a handler for it or there is no enclosing block. If there is no handler for the exception, then PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome (for more information, see «Unhandled Exceptions»).
In Figure 11-1, one block is nested inside another. The inner block raises exception A. The inner block has an exception handler for A, so A does not propagate. After the exception handler runs, control transfers to the next statement of the outer block.
In Figure 11-2, the inner block raises exception B. The inner block does not have an exception handler for exception B, so B propagates to the outer block, which does have an exception handler for it. After the exception handler runs, control transfers to the host environment.
In Figure 11-3, the inner block raises exception C. The inner block does not have an exception handler for C, so exception C propagates to the outer block. The outer block does not have an exception handler for C, so PL/SQL returns an unhandled exception error to the host environment.
A user-defined exception can propagate beyond its scope (that is, beyond the block that declares it), but its name does not exist beyond its scope. Therefore, beyond its scope, a user-defined exception can be handled only with an OTHERS
exception handler.
In Example 11-14, the inner block declares an exception named past_due
, for which it has no exception handler. When the inner block raises past_due
, the exception propagates to the outer block, where the name past_due
does not exist. The outer block handles the exception with an OTHERS
exception handler.
If the outer block does not handle the user-defined exception, then an error occurs, as in Example 11-15.
Note:
Exceptions cannot propagate across remote subprogram invocations. Therefore, a PL/SQL block cannot handle an exception raised by a remote subprogram.
Topics
-
Propagation of Exceptions Raised in Declarations
-
Propagation of Exceptions Raised in Exception Handlers
Example 11-14 Exception that Propagates Beyond Scope is Handled
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS BEGIN DECLARE past_due EXCEPTION; PRAGMA EXCEPTION_INIT (past_due, -4910); due_date DATE := trunc(SYSDATE) - 1; todays_date DATE := trunc(SYSDATE); BEGIN IF due_date < todays_date THEN RAISE past_due; END IF; END; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END; /
Example 11-15 Exception that Propagates Beyond Scope is Not Handled
BEGIN
DECLARE
past_due EXCEPTION;
due_date DATE := trunc(SYSDATE) - 1;
todays_date DATE := trunc(SYSDATE);
BEGIN
IF due_date < todays_date THEN
RAISE past_due;
END IF;
END;
END;
/
Result:
BEGIN
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 9
11.8.1 Propagation of Exceptions Raised in Declarations
An exception raised in a declaration propagates immediately to the enclosing block (or to the invoker or host environment if there is no enclosing block). Therefore, the exception handler must be in an enclosing or invoking block, not in the same block as the declaration.
In Example 11-16, the VALUE_ERROR
exception handler is in the same block as the declaration that raises VALUE_ERROR
. Because the exception propagates immediately to the host environment, the exception handler does not handle it.
Example 11-17 is like Example 11-16 except that an enclosing block handles the VALUE_ERROR
exception that the declaration in the inner block raises.
Example 11-16 Exception Raised in Declaration is Not Handled
DECLARE credit_limit CONSTANT NUMBER(3) := 5000; -- Maximum value is 999 BEGIN NULL; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Exception raised in declaration.'); END; /
Result:
DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 2
Example 11-17 Exception Raised in Declaration is Handled by Enclosing Block
BEGIN DECLARE credit_limit CONSTANT NUMBER(3) := 5000; BEGIN NULL; END; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Exception raised in declaration.'); END; /
Result:
Exception raised in declaration.
11.8.2 Propagation of Exceptions Raised in Exception Handlers
An exception raised in an exception handler propagates immediately to the enclosing block (or to the invoker or host environment if there is no enclosing block). Therefore, the exception handler must be in an enclosing or invoking block.
In Example 11-18, when n
is zero, the calculation 1/n
raises the predefined exception ZERO_DIVIDE
, and control transfers to the ZERO_DIVIDE
exception handler in the same block. When the exception handler raises ZERO_DIVIDE
, the exception propagates immediately to the invoker. The invoker does not handle the exception, so PL/SQL returns an unhandled exception error to the host environment.
Example 11-19 is like Example 11-18 except that when the procedure returns an unhandled exception error to the invoker, the invoker handles it.
Example 11-20 is like Example 11-18 except that an enclosing block handles the exception that the exception handler in the inner block raises.
In Example 11-21, the exception-handling part of the procedure has exception handlers for user-defined exception i_is_one
and predefined exception ZERO_DIVIDE
. When the i_is_one
exception handler raises ZERO_DIVIDE
, the exception propagates immediately to the invoker (therefore, the ZERO_DIVIDE
exception handler does not handle it). The invoker does not handle the exception, so PL/SQL returns an unhandled exception error to the host environment.
Example 11-22 is like Example 11-21 except that an enclosing block handles the ZERO_DIVIDE
exception that the i_is_one
exception handler raises.
Example 11-18 Exception Raised in Exception Handler is Not Handled
CREATE PROCEDURE print_reciprocal (n NUMBER) AUTHID DEFINER IS BEGIN DBMS_OUTPUT.PUT_LINE(1/n); -- handled EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error:'); DBMS_OUTPUT.PUT_LINE(1/n || ' is undefined'); -- not handled END; / BEGIN -- invoking block print_reciprocal(0); END;
Result:
Error: BEGIN * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at "HR.PRINT_RECIPROCAL", line 7 ORA-01476: divisor is equal to zero ORA-06512: at line 2
Example 11-19 Exception Raised in Exception Handler is Handled by Invoker
CREATE PROCEDURE print_reciprocal (n NUMBER) AUTHID DEFINER IS BEGIN DBMS_OUTPUT.PUT_LINE(1/n); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error:'); DBMS_OUTPUT.PUT_LINE(1/n || ' is undefined'); END; / BEGIN -- invoking block print_reciprocal(0); EXCEPTION WHEN ZERO_DIVIDE THEN -- handles exception raised in exception handler DBMS_OUTPUT.PUT_LINE('1/0 is undefined.'); END; /
Result:
Error: 1/0 is undefined.
Example 11-20 Exception Raised in Exception Handler is Handled by Enclosing Block
CREATE PROCEDURE print_reciprocal (n NUMBER) AUTHID DEFINER IS BEGIN BEGIN DBMS_OUTPUT.PUT_LINE(1/n); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error in inner block:'); DBMS_OUTPUT.PUT_LINE(1/n || ' is undefined.'); END; EXCEPTION WHEN ZERO_DIVIDE THEN -- handles exception raised in exception handler DBMS_OUTPUT.PUT('Error in outer block: '); DBMS_OUTPUT.PUT_LINE('1/0 is undefined.'); END; / BEGIN print_reciprocal(0); END; /
Result:
Error in inner block: Error in outer block: 1/0 is undefined.
Example 11-21 Exception Raised in Exception Handler is Not Handled
CREATE PROCEDURE descending_reciprocals (n INTEGER) AUTHID DEFINER IS i INTEGER; i_is_one EXCEPTION; BEGIN i := n; LOOP IF i = 1 THEN RAISE i_is_one; ELSE DBMS_OUTPUT.PUT_LINE('Reciprocal of ' || i || ' is ' || 1/i); END IF; i := i - 1; END LOOP; EXCEPTION WHEN i_is_one THEN DBMS_OUTPUT.PUT_LINE('1 is its own reciprocal.'); DBMS_OUTPUT.PUT_LINE('Reciprocal of ' || TO_CHAR(i-1) || ' is ' || TO_CHAR(1/(i-1))); WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error:'); DBMS_OUTPUT.PUT_LINE(1/n || ' is undefined'); END; / BEGIN descending_reciprocals(3); END; /
Result:
Reciprocal of 3 is .3333333333333333333333333333333333333333 Reciprocal of 2 is .5 1 is its own reciprocal. BEGIN * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at "HR.DESCENDING_RECIPROCALS", line 19 ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 2
Example 11-22 Exception Raised in Exception Handler is Handled by Enclosing Block
CREATE PROCEDURE descending_reciprocals (n INTEGER) AUTHID DEFINER IS i INTEGER; i_is_one EXCEPTION; BEGIN BEGIN i := n; LOOP IF i = 1 THEN RAISE i_is_one; ELSE DBMS_OUTPUT.PUT_LINE('Reciprocal of ' || i || ' is ' || 1/i); END IF; i := i - 1; END LOOP; EXCEPTION WHEN i_is_one THEN DBMS_OUTPUT.PUT_LINE('1 is its own reciprocal.'); DBMS_OUTPUT.PUT_LINE('Reciprocal of ' || TO_CHAR(i-1) || ' is ' || TO_CHAR(1/(i-1))); WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error:'); DBMS_OUTPUT.PUT_LINE(1/n || ' is undefined'); END; EXCEPTION WHEN ZERO_DIVIDE THEN -- handles exception raised in exception handler DBMS_OUTPUT.PUT_LINE('Error:'); DBMS_OUTPUT.PUT_LINE('1/0 is undefined'); END; / BEGIN descending_reciprocals(3); END; /
Result:
Reciprocal of 3 is .3333333333333333333333333333333333333333 Reciprocal of 2 is .5 1 is its own reciprocal. Error: 1/0 is undefined
11.9 Unhandled Exceptions
If there is no handler for a raised exception, PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome.
If a stored subprogram exits with an unhandled exception, PL/SQL does not roll back database changes made by the subprogram.
The FORALL
statement runs one DML statement multiple times, with different values in the VALUES
and WHERE
clauses. If one set of values raises an unhandled exception, then PL/SQL rolls back all database changes made earlier in the FORALL
statement. For more information, see «Handling FORALL Exceptions Immediately» and «Handling FORALL Exceptions After FORALL Statement Completes».
Tip:
Avoid unhandled exceptions by including an OTHERS
exception handler at the top level of every PL/SQL program.
11.10 Retrieving Error Code and Error Message
In an exception handler, for the exception being handled:
-
You can retrieve the error code with the PL/SQL function
SQLCODE
, described in «SQLCODE Function». -
You can retrieve the error message with either:
-
The PL/SQL function
SQLERRM
, described in «SQLERRM Function»This function returns a maximum of 512 bytes, which is the maximum length of an Oracle Database error message (including the error code, nested messages, and message inserts such as table and column names).
-
The package function
DBMS_UTILITY
.FORMAT_ERROR_STACK
, described in Oracle Database PL/SQL Packages and Types ReferenceThis function returns the full error stack, up to 2000 bytes.
Oracle recommends using
DBMS_UTILITY
.FORMAT_ERROR_STACK
, except when using theFORALL
statement with itsSAVE
EXCEPTIONS
clause, as in Example 12-13. -
A SQL statement cannot invoke SQLCODE
or SQLERRM
. To use their values in a SQL statement, assign them to local variables first, as in Example 11-23.
See Also:
-
Oracle Database PL/SQL Packages and Types Reference for information about the
DBMS_UTILITY
.FORMAT_ERROR_BACKTRACE
function, which displays the call stack at the point where an exception was raised, even if the subprogram is called from an exception handler in an outer scope -
Oracle Database PL/SQL Packages and Types Reference for information about the
UTL_CALL_STACK
package, whose subprograms provide information about currently executing subprograms, including subprogram names
Example 11-23 Displaying SQLCODE and SQLERRM Values
DROP TABLE errors; CREATE TABLE errors ( code NUMBER, message VARCHAR2(64) ); CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS name EMPLOYEES.LAST_NAME%TYPE; v_code NUMBER; v_errm VARCHAR2(64); BEGIN SELECT last_name INTO name FROM EMPLOYEES WHERE EMPLOYEE_ID = -1; EXCEPTION WHEN OTHERS THEN v_code := SQLCODE; v_errm := SUBSTR(SQLERRM, 1, 64); DBMS_OUTPUT.PUT_LINE ('Error code ' || v_code || ': ' || v_errm); /* Invoke another procedure, declared with PRAGMA AUTONOMOUS_TRANSACTION, to insert information about errors. */ INSERT INTO errors (code, message) VALUES (v_code, v_errm); RAISE; END; /
11.11 Continuing Execution After Handling Exceptions
After an exception handler runs, control transfers to the next statement of the enclosing block (or to the invoker or host environment if there is no enclosing block). The exception handler cannot transfer control back to its own block.
For example, in Example 11-24, after the SELECT
INTO
statement raises ZERO_DIVIDE
and the exception handler handles it, execution cannot continue from the INSERT
statement that follows the SELECT
INTO
statement.
If you want execution to resume with the INSERT
statement that follows the SELECT
INTO
statement, then put the SELECT
INTO
statement in an inner block with its own ZERO_DIVIDE
exception handler, as in Example 11-25.
See Also:
Example 12-13, where a bulk SQL operation continues despite exceptions
Example 11-24 Exception Handler Runs and Execution Ends
DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, salary, commission_pct FROM employees; DECLARE sal_calc NUMBER(8,2); BEGIN INSERT INTO employees_temp (employee_id, salary, commission_pct) VALUES (301, 2500, 0); SELECT (salary / commission_pct) INTO sal_calc FROM employees_temp WHERE employee_id = 301; INSERT INTO employees_temp VALUES (302, sal_calc/100, .1); DBMS_OUTPUT.PUT_LINE('Row inserted.'); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero.'); END; /
Result:
Division by zero.
Example 11-25 Exception Handler Runs and Execution Continues
DECLARE sal_calc NUMBER(8,2); BEGIN INSERT INTO employees_temp (employee_id, salary, commission_pct) VALUES (301, 2500, 0); BEGIN SELECT (salary / commission_pct) INTO sal_calc FROM employees_temp WHERE employee_id = 301; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Substituting 2500 for undefined number.'); sal_calc := 2500; END; INSERT INTO employees_temp VALUES (302, sal_calc/100, .1); DBMS_OUTPUT.PUT_LINE('Enclosing block: Row inserted.'); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Enclosing block: Division by zero.'); END; /
Result:
Substituting 2500 for undefined number. Enclosing block: Row inserted.
11.12 Retrying Transactions After Handling Exceptions
To retry a transaction after handling an exception that it raised, use this technique:
- Enclose the transaction in a sub-block that has an exception-handling part.
- In the sub-block, before the transaction starts, mark a savepoint.
- In the exception-handling part of the sub-block, put an exception handler that rolls back to the savepoint and then tries to correct the problem.
- Put the sub-block inside a
LOOP
statement. - In the sub-block, after the
COMMIT
statement that ends the transaction, put anEXIT
statement.If the transaction succeeds, the
COMMIT
andEXIT
statements execute.If the transaction fails, control transfers to the exception-handling part of the sub-block, and after the exception handler runs, the loop repeats.
Example 11-26 Retrying Transaction After Handling Exception
DROP TABLE results; CREATE TABLE results ( res_name VARCHAR(20), res_answer VARCHAR2(3) ); CREATE UNIQUE INDEX res_name_ix ON results (res_name); INSERT INTO results (res_name, res_answer) VALUES ('SMYTHE', 'YES'); INSERT INTO results (res_name, res_answer) VALUES ('JONES', 'NO'); DECLARE name VARCHAR2(20) := 'SMYTHE'; answer VARCHAR2(3) := 'NO'; suffix NUMBER := 1; BEGIN FOR i IN 1..5 LOOP -- Try transaction at most 5 times. DBMS_OUTPUT.PUT('Try #' || i); BEGIN -- sub-block begins SAVEPOINT start_transaction; -- transaction begins DELETE FROM results WHERE res_answer = 'NO'; INSERT INTO results (res_name, res_answer) VALUES (name, answer); -- Nonunique name raises DUP_VAL_ON_INDEX. -- If transaction succeeded: COMMIT; DBMS_OUTPUT.PUT_LINE(' succeeded.'); EXIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE(' failed; trying again.'); ROLLBACK TO start_transaction; -- Undo changes. suffix := suffix + 1; -- Try to fix problem. name := name || TO_CHAR(suffix); END; -- sub-block ends END LOOP; END; /
Result:
Try #1 failed; trying again. Try #2 succeeded.
Example 11-26 uses the preceding technique to retry a transaction whose INSERT
statement raises the predefined exception DUP_VAL_ON_INDEX
if the value of res_name
is not unique.
11.13 Handling Errors in Distributed Queries
You can use a trigger or a stored subprogram to create a distributed query. This distributed query is decomposed by the local Oracle Database instance into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes run the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.
If a portion of a distributed statement fails, possibly from a constraint violation, then Oracle Database returns ORA-02055. Subsequent statements, or subprogram invocations, return ORA-02067 until a rollback or a rollback to savepoint is entered.
Design your application to check for any returned error messages that indicates that a portion of the distributed update has failed. If you detect a failure, rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.