Как исправить ошибку oracle - Исправление недочетов и поиск решений вместе с Examum.ru

Регулярное создание резервных копий производственной базы данных Oracle является обязательным, но эти резервные копии никак не помогут, если по какой-то причине окажутся непригодными для использования. Этап тестирования резервных копий часто игнорируется в процессах резервного копирования и восстановления. К сожалению, многие администраторы осознают его необходимость, будучи уже в тяжелых обстоятельствах.

Создаваемые резервные копии файлов базы данных могут становиться бесполезными во время восстановления по нескольким причинам: из-за повреждения файлов данных и журналов повторного выполнения, из-за случайного перезаписывания файлов, из-за дефектов на ленте и даже из-за несуществующих файлов. Поэтому лучше обзавестись привычкой регулярно тестировать производственные резервные копии в соответствие с графиком. Это поможет перехватывать любые повреждения данных. Под повреждением подразумевается несоответствие данных тому виду, в котором они должны быть. Здесь интерес главным образом представляет так называемое повреждение блоков (ошибки блоков), которое может быть как логическим, так и физическим.

Обнаружение ошибок в носителях

Повреждения в носителях могут возникать по массе причин, начиная от ошибки пользователя и неполадок в программном обеспечении операционной системы и заканчивая дефектными дисками, ошибками диспетчера логических томов ( Logical Volume Manager — LVM) и неисправными микросхемами памяти. Они могут приводить, в свою очередь, к возникновению повреждений в управляющих файлах, журналах повторного выполнения, словаре данных, табличных данных и данных индексов.

Выявлять повреждения в носителях в любом месте базы данных можно путем применения специальных сценариев для регулярного осуществления мониторинга за журналами предупреждений и с помощью ряда функций Oracle, позволяющих обнаруживать проблемы заблаговременно. Например, применяя функции мультиплексирования как на уровне операционной системы, так и на уровне Oracle, можно практически полностью предотвратить вероятность повреждения управляющих файлов и журналов повторного выполнения. Благодаря просто размеру базы данных и тому факту, что файлы не мультиплексируются сами по себе, администраторам баз данных остается беспокоиться главным образом только о повреждении блоков данных. Им вполне достаточно стараться как можно раньше перехватывать сообщения о повреждениях в журналах предупреждений и устранять любые связанные с повреждениями проблемы в базе данных, выполняя поиск решений в справочных материалах Oracle Worldwide Support.

Обнаружение ошибок в блоках данных

Ошибки блоков данных происходят при появлении несогласованных данных в таблицах или индексах СУБД Oracle. Обычно невозможность устранить поврежденные блоки приводит к потере приличного количества данных. Хотя можно предпринимать несколько мер для предотвращения повреждения, своевременное обнаружение поврежденных файлов данных будет тоже помогать.

Своевременное обнаружение позволяет отыскивать быстрые способы спасения всех или насколько возможно большей части пострадавших данных.
Своевременное обнаружение избавляет от сюрпризов при выполнении восстановления после ошибок на уровне носителей, поскольку будет сводить к минимуму проблему, позволяя переводить файлы в автономный режим и тем самым сокращать потенциальный объем ущерба.

Существует несколько методов, которые можно применять для обнаружения повреждений в блоках данных БД Oracle. Во-первых, можно устанавливать несколько специальных параметров инициализации и тем самым обеспечивать возможность перехвата информации о поврежденных блоках. Во-вторых, можно использовать утилиты наподобие DBVERIFY и DBMS_REPAIR или команду ANALYZE и тем самым обеспечивать возможность выявления повреждений в блоках данных. Эти методы не являются взаимоисключающими; напротив, их следует рассматривать как дополнения друг к другу, поскольку каждый обладает своими собственными привлекательными возможностями. В следующих подразделах более подробно рассказывается о том, как применять каждый из этих приемов.

Настройка параметров инициализации

Установив такой параметр инициализации, как DB_BLOCK_CHECKSUM, можно заставить базу данных Oracle вычислять контрольные суммы (check-summing) для каждого блока данных и сохранять их в заголовках блоков. Тогда при чтении данных эти контрольные суммы сравниваются и выявляются поврежденные блоки данных. В Oracle рекомендуют оставить для параметра DB_BLOCK_CHECKSUM принятое для него по умолчанию значение TYPICAL (равнозначное значению TRUE, которое использовалось в предыдущих версиях). Согласно заявлениям Oracle, использование этой функции в режиме TYPICAL приводит к увеличению накладных расходов всего лишь на 1–2%. Применение ее в другом возможном режиме FULL приводит к увеличению накладных расходов уже на 4–5%.

Параметр DB_BLOCK_CHECKING является более сложным и предусматривает выполнение проверки блоков данных и индексов только тогда, когда они действительно изменяются. Он обнаруживает повреждения до присвоения блокам данных статуса поврежденных. По умолчанию для него используется значение OFF. Другие значения, которые он может принимать: LOW, MEDIUM и FULL. Его применение может приводить к увеличению объема накладных расходов на 1–10%; этот объем напрямую зависит от количества выполняемых в базе данных операций обновления и вставки. При наличии возможности справляться с дополнительными накладными расходами, в СУБД Oracle рекомендуют устанавливать для этого параметра значение FULL. Конфигурировать этот параметр можно в файле init.ora, как показано в следующем примере, где для него выбрано значение LOW:

DB_BLOCK_CHECKING=LOW

Его также можно конфигурировать и динамически с помощью оператора ALTER SESSION:

SQL> ALTER SESSION SET DB_BLOCK_CHECKING=LOW;

Еще одним параметром инициализации, который можно устанавливать, является DB_ULTRA_SAFE. Этот параметр применяется для управления значениями параметров DB_BLOCK_CHECKSUM и DB_BLOCK_CHECKING. В случае если для него оставляется принятое по умолчанию значение (OFF), база данных устанавливает для обоих связанных с выявлением повреждений параметров значение TYPICAL, что означает выполнение минимальных проверок и, следовательно, меньшее потребление ресурсов ЦП. В случае же установки для него значения DATA_ONLY или DATA_AND_INDEX, база данных будет устанавливать для двух связанных с выявлением повреждений параметров значение FULL, что будет приводить к выполнению более интенсивных проверок на предмет повреждений и, следовательно, большему потреблению ресурсов.

Применение команды ANALYZE

Команду ANALYZE удобно применять для перехвата поврежденных блоков данных. Например, выполнение показанной ниже команды ANALYZE приведет к проверке каждого блока данных в таблице customer и, в случае обнаружения любых поврежденных блоков — добавлению всех подозрительных строк в таблицу invalid_rows:

SQL> ANALYZE TABLE customer VALIDATE STRUCTURE;

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

Применение утилиты DBVERIFY

При возникновении подозрений в повреждении блоков данных еще можно использовать поставляемую Oracle утилиту DBVERIFY. Эта утилита запускается на уровне операционной системы. Она выполняет проверку на предмет повреждения структурной целостности файлов базы данных.

Для иллюстрации применения утилиты DBVERIFY ниже приведен пример выполнения верификации файла на платформе Windows (на платформах UNIX команда будет работать точно так же). Администратор базы данных может легко писать для выполнения верификации файлов данных специальный сценарий и затем настраивать для него график регулярного выполнения с помощью crontab. В листинге 1 показаны результаты применения утилиты DBVERIFY.

$ dbv file=/u01/orcl/oradata/system01.dbf
DBVERIFY: Release 11.1.0.6.0 - Production on Sun Mar 30 15:53:46 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting :
FILE = =/u01/orcl/oradata/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 19200
Total Pages Processed (Data) : 4404
Total Pages Failing (Data) : 0
Total Pages Processed (Index) : 1245
Total Pages Failing (Index) : 0
Total Pages Processed (Other) : 2663
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 10888
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 935681 (0.935681)
$

Этот пример иллюстрирует упрощенный вариант применения утилиты DBVERIFY, которая вызывается командой DBV на платформах Windows и UNIX. Ключевое слово FILE указывает, какой файл данных требуется проверить на предмет повреждения. Согласно приведенному здесь выводу, общее количество страниц, помеченных как поврежденные (Total Pages Marked Corrupt), равняется нулю, а это значит, что в базе данных нет никаких проблем со структурной целостностью.

Применение пакета DBMS_REPAIR

Несмотря на то что утилита DBVERIFY очень проста в применении, использовать ее для исправления поврежденных данных нельзя, а это является очень серьезным ограничением. Поэтому еще в версии Oracle8i появился пакет DBMS_REPAIR, позволяющий не только выявлять, но и исправлять поврежденные блоки данных без перевода файлов данных в автономный режим. Прежде чем использовать этот пакет, нужно войти в систему от имени пользователя SYS и создать две специальные таблицы: одну с приставкой repair_ и вторую с именем orphan_key.

После создания таблицы repair_table пакет DBMS_REPAIR можно запускать. В эту таблицу будет заноситься информация обо всех поврежденных данных. Выполнение содержащейся в пакете DBMS_REPAIR процедуры CHECK_OBJECT будет приводить к выявлению поврежденных блоков и отображению рекомендуемых вариантов для их исправления, а выполнение после процедуры CHECK_OBJECT запроса к таким столбцам таблицы repair_table, как OBJECT_NAME и CORRUPT_DESCRIPTION — выяснить, существуют ли повреждения в блоках данных, и если да, то какого типа.

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

Инициатива HARD

Применение RAID обеспечивает избыточность только на уровне устройств хранения данных, чтобы позволить при потере нескольких дисков не терять данные. А что если используется система с зеркальным отображением дисков, но данные, записываемые на зеркальную пару дисков, повреждены? Тогда на обоих дисках в зеркальной паре, конечно же, будут содержаться поврежденные данные. Поэтому в Oracle недавно объявили о новой инициативе для предотвращения повреждения данных еще до его возникновения, которая получила название Hardware Assisted Resilient Data (Обеспечение устойчивости данных на уровне аппаратных средств), или просто HARD. В рамках этой инициативы Oracle будет встраивать в устройства хранения, продаваемые участвующими в этой инициативе производителями, специальные алгоритмы верификации данных и тем самым предотвращать окончательную запись поврежденных данных на диск. В частности, инициатива HARD направлена на решение проблем следующего рода:

перезаписывание данных Oracle операционной системой;
частичная запись блоков и потеря блоков при записи;
запись физически и логически поврежденных блоков;
запись блоков в неправильное место.

Вас заинтересует / 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;

Именованные системные исключения

В 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

Создание собственных исключений

Связываем исключение с кодом ошибки

Именованные системные исключения

Инициирование исключений

Оператор RAISE

Использование процедуры RAISE_APPLICATION_ERROR

Использование функций обработки ошибок

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

Эскалация необработанного исключения

На что стоит обратить внимание

Динамический SQL и динамический PL/SQL

Инструкции NDS

Инструкция EXECUTE IMMEDIATE

Инструкция OPEN FOR

Режимы использования параметров

Дублирование формальных параметров

Передача значений NULL

Использование пакета DBMS_SQL

Когда следует использовать DBMS_SQL

Новые возможности Oracle 11g

SQL Injection

Statement modification

Statement injection

Data Type Conversion

Методы защиты от SQL-инъекций

Использование внутреннего преобразования формата 

Исключения

Ошибки, возникающие при работе с СУБД, можно разделить на следующие группы:

ошибки, генерируемые системой (например, нехватка памяти или повторяющееся значение индекса);
ошибки, генерируемые приложением (например, невыполнение каких-либо условий и проверок).

В языке PL/SQL ошибки всех видов интерпретируются как исключительные ситуации, или исключения.

Исключения могут быть системными и пользовательскими:

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

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

Определены в СУБД.

— неименованные исключения — имеют только номера (ORA-02292)

— именованные исключения – имеют как номера, так и названия (например, ORA-01403: NO_DATA_FOUND)

Определяются программистом в приложении.
Имеют номер в диапазоне от -20999 до -20000 и текстовое описание.
Инициируются с помощью RAISE_APPLICATION_ERROR

Предупреждающие сообщения при компиляции

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

Категории предупреждающих сообщений:

Категория	Описание	Пример
SEVERE	Условия, которые могут привести к неожиданным последствиям или некорректным результатам	Использование INTO при объявлении курсора
PERFORMANCE	Код, приводящий к снижению производительности	Использование значения VARCHAR2 для поля с типом NUMBER в операторе INSERT
INFORMATIONAL	Условия, которые не влияют на производительность, но усложняют чтение кода	Код, который никогда не будет выполнен

Конфигурирование производится посредством установки значения параметра PLSQL_WARNINGS.

Посредством установки параметра PLSQL_WARNINGS можно:

включать и отключать либо все предупреждающие сообщения, либо сообщения одной или нескольких категорий, либо конкретное сообщение;
трактовать конкретные предупреждения как ошибки.

Значение этого параметра можно задавать для:

всего экземпляра базы данных (ALTER SYSTEM);
текущего сеанса (ALTER SESSION);
хранимого PL/SQL-модуля (ALTER «PL/SQL block»).

Во всех ALTER-операторах значение параметра PLSQL_WARNINGS задается в следующем виде:

SET PLSQL_WARNINGS = ‘value_clause’ [, ‘value_clause’ ] …

, где

value_clause::=

{ ENABLE | DISABLE | ERROR }:

{ ALL | SEVERE | INFORMATIONAL | PERFORMANCE | { integer | (integer [, integer ] …) } }

Для отображения предупреждающих сообщений, сгенерированных в процессе компиляции, можно либо опрашивать представления *_ERRORS (DBA_, USER_, ALL_ ), либо использовать команду SHOW ERRORS.

Несколько примеров настройки режима выдачи предупреждений

Включение всех предупреждений внутри сессии (полезно при разработке):

ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL’;

Включение сообщений PERFORMANCE для сессии:

ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:PERFORMANCE’;

Включение сообщений PERFORMANCE для процедуры loc_var:

ALTER PROCEDURE loc_var COMPILE PLSQL_WARNINGS=’ENABLE:PERFORMANCE’;

Включение сообщений SEVERE, отключение сообщений PERFORMANCE и трактования сообщения
PLW-06002 (unreachable code) как ошибки:

ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:SEVERE’, ‘DISABLE:PERFORMANCE’, ‘ERROR:06002′;

Отключение всех предупреждающих сообщений для текущей сессии:

ALTER SESSION SET PLSQL_WARNINGS=’DISABLE:ALL’;

Для просмотра текущего значения PLSQL_WARNINGS следует обратиться к представлению ALL_PLSQL_OBJECT_SETTINGS.

Обработка исключений в PL/SQL

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

Независимо от того, как и по какой причине было инициировано конкретное исключение, оно обрабатывается одним и тем же обработчиком в разделе исключений.

Любая ошибка может быть обработана только одним обработчиком.

Для обработки исключений в блоке PL/SQL предназначается необязательный раздел EXCEPTION:

BEGIN

операторы

EXCEPTION

WHEN [исключение 1]THEN …..;

WHEN [исключение 2]THEN …..;

…

WHEN [исключение N]THEN …..;

WHEN OTHERS THEN …..;

END;

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

Обработчик WHEN OTHERS должен быть последним обработчиком в блоке, иначе возникнет ошибка компиляции. Этот обработчик не является обязательным. Если он отсутствует, то все необработанные исключения передадутся в родительский блок, либо в вызывающую хост-систему.

В одном предложении WHEN, можно объединить несколько исключений, используя оператор OR:

WHEN invalid_company_id OR negative_balance THEN

Также в одном о6ра6отчике можно ком6инировать имена пользовательских и системных исключений:

WHEN balance_too_low OR zero_divide OR dbms_ldap.invalid_session THEN

Создание собственных исключений

Внутри приложения можно определять свои собственные (пользовательские) исключения.

Сделать это можно в разделе объявлений блока РL/SQL следующим образом:

DECLARE

INVALID_COMPANY_ID EXCEPTION;

Для того, чтобы инициировать исключение, необходимо воспользоваться оператором RAISE:

raise INVALID_COMPANY_ID;

После этого выполнение программы переходит в раздел EXCEPTION на соответствующий обработчик:

BEGIN

…..

raise INVALID_COMPANY_ID;

EXCEPTION

when DUP_VAL_ON_INDEX then

….

when INVALID_COMPANY_ID then

….

END;

Для того, чтобы присвоить ошибке номер и создать для нее текстовое описание, следует воспользоваться процедурой RAISE_APPLICATION_ERROR:

RAISE_APPLICATION_ERROR(-20000, ‘My error!’);

Связываем исключение с кодом ошибки

Предположим, у нас есть программа, при выполнении которой может сгенерироваться ошибка, связанная с данными, например ОRА-01843: not a valid month.

Для перехвата этой ошибки в код программы потребуется поместить такой обработчик:

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -1843 ТНЕN /* not a valid month */

Но такой код малопонятен.

Конкретную ошибку Oracle можно привязать к именованному исключению с помощью директивы компилятора EXCEPTION_INIT:

DECLARE

invalid_month EXCEPTION;

PRAGMA EXCEPTION_INIT(invalid_month, -1843);

BEGIN

……

EXCEPTION

WHEN invalid_month THEN …..

END;

Теперь имя ошибки говорит само за себя и никакие литеральные номера ошибок, которые трудно запомнить, не понадобятся.

Установив такую связь, можно инициировать исключение по имени и использовать это имя в предложении WHEN обработчика ошибок.

Именованные системные исключения

B Oracle для некоторых системных исключений определены стандартные имена, которые заданы с помощью директивы компилятора EXCEPTION_INIT во встроенных пакетах.

Наиболее важные и широко применяемые из них определены в пакете STANDARD.

То обстоятельство, что этот пакет используется по умолчанию, означает, что на определенные в нем исключения можно ссылаться без указания в качестве префикса имени пакета.
Например, если необходимо обработать в программе исключение NO_DАТА_FOUND, то это можно сделать посредством любого из двух операторов:

WHEN NO_DАТА_FOUND THEN

WHEN STANDARD.NO_DАТА_FOUND THEN

Именованные системные исключения

Название	Код	Название	Код
ACCESS_INTO_NULL	-6530	PROGRAM_ERROR	-6501
CASE_NOT_FOUND	-6592	ROWTYPE_MISMATCH	-6504
COLLECTION_IS_NULL	-6531	SELF_IS_NULL	-30625
CURSOR_ALREADY_OPEN	-6511	STORAGE_ERROR	-6500
DUP_VAL_ON_INDEX	-1	SUBSCRIPT_BEYOND_COUNT	-6533
INVALID_CURSOR	-1001	SUBSCRIPT_OUTSIDE_LIMIT	-6532
INVALID_NUMBER	-1722	SYS_INVALID_ROWID	-1410
LOGIN_DENIED	-1017	TIMEOUT_ON_RESOURCE	-51
NO_DATA_FOUND	+100	TOO_MANY_ROWS	-1422
NO_DATA_NEEDED	-6548	VALUE_ERROR	-6502
NOT_LOGGED_ON	-1012	ZERO_DIVIDE	-1476

Инициирование исключений

Программно инициировать исключение можно посредством оператора RAISE или процедуры RAISE_АРРLICATIОN_ERROR.

Оператор RAISE

С помощью оператора RAISE можно инициировать как собственные, так и системные исключения.

Оператор имеет три формы:

RAISE имя_исключения	Инициирование исключения, определенного в текущем блоке, а также инициирование системных исключений, объявленных в пакете STANDARD
RAISE имя_пакета.имя_исключения	Если исключение объявлено в любом другом пакете, отличном от STANDARD, имя исключения нужно уточнять именем пакета
RAISE	Не требует указывать имя исключения, но используется только в предложении WHEN раздела исключений. Этой формой оператора следует пользоваться, когда в обработчике исключений нужно повторно инициировать то же самое исключение

Использование процедуры RAISE_APPLICATION_ERROR

Для инициирования исключений, специфических для приложения, в Oracle существует процедура RAISЕ_APPLICATION_ERROR.
Ее преимущество перед оператором RAISЕ (который тоже может инициировать специфические для приложения явно объявленные исключения) заключается в том, что она позволяет связать с номером исключения некоторое текстовое сообщение об ошибке.

PROCEDURE RAISE_APPLICATION_ERROR(num BINARY_INTEGER,

msg VARCHAR2,

keeperrorstack boolean default false);

Здесь

num – это номер ошибки из диапазона от -20999 до -20000;

msg — это сообщение об ошибке, длина которого не должна превышать 2048 символов (символы, выходящие за эту границу, игнорируются);

keepеrrorstасk – параметр указывает, хотите вы добавить ошибку к тем, что уже имеются в стеке (true), или заменить существующую ошибку (значение по умолчанию – false).

Использование функций обработки ошибок

SQLCODE

Предложение WHEN OTHERS используется для перехвата исключений, не указанных в предложениях WHEN. Однако в этом обработчике тоже нужна информация о том, какая именно ошибка произошла. Для ее получения можно воспользоваться функцией SQLCODE, возвращающей номер возникшей ошибки (значение 0 указывает, что в стеке ошибок нет ни одной ошибки).

SQLERRM

Возвращает поясняющее сообщение для текущей или для указанной ошибки:

SQLERRM – возвратит описание для самой последней ошибки

SQLERRM(code NUMBER) – возвратит описание для ошибки с указанным кодом

DBMS_UTILITY.FORMAT_CALL_STACK

Функция возвращает отформатированную строку со стеком вызовов в приложении PL/SQL.

DBMS_UTILITY.FORMAT_ERROR_STACK

Эта функция, как и SQLERRM, возвращает сообщение, связанное с текущей ошибкой.
Ее отличия от SQLERRM:

она возвращает до 2000 символов (SQLERRM возвращает 512 символов)
этой функции нельзя в качестве аргумента передать код ошибки

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Функция появилась в Oracle 10.

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

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

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

Эскалация необработанного исключения

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

На что стоит обратить внимание

Если при выполнении нескольких DML-операций в SQL-среде возникает исключительная ситуация, то все операции, предшествующие ошибочному оператору, считаются выполненными корректно и не откатываются.
Если те же самые DML-операции обернуть в блок BEGIN … END — тогда при возникновении исключительной ситуации на очередном DML-операторе все предыдущие успешно (!) выполненные операции откатываются. Откат происходит к моменту начала выполнения блока.
Т.е. блок либо выполняется целиком, либо не выполняется совсем.
Если обработчик завершается с повторной инициацией исключительной ситуации (напр., WHEN OTHERS then raise), то все изменения, проделанные в блоке, откатываются.
Если же выход из блока происходит через обработку исключительной ситуации и повторной инициации исключительной ситуации не происходит (напр., WHEN OTHERS then null), то блок считается исполненным успешно и отката изменений, которые внутри него произошли, не будет (!!!). То есть результат работы операторов, предшествующих ошибочному оператору, останется в БД.
Поэтому, если по бизнес-логике такого не нужно, то в обработчике исключения надо явно делать ROLLBACK.

Статическими называются жестко закодированные инструкции и операторы, которые не изменяются с момента компиляции программы.
Инструкции динамического SQL формируются, компилируются и вызываются непосредственно во время выполнения программы.
Следует отметить, что такая гибкость языка открывает перед программистами огромные возможности и позволяет писать универсальный код многократного использования.

Начиная с Огасlе7 поддержка динамического SQL осуществляется с помощью встроенного пакета DВМS_SQL.
В Оrасlе 8i для этого появилась еще одна возможность — встроенный динамический SQL (Native Dynamic SQL, NDS).
NDS интегрируется в язык PL/SQL; пользоваться им намного удобнее, чем DВМS_SQL.

На практике NDS в подавляющем большинстве является более предпочтительным решением.

Инструкции NDS

Главным достоинством NDS является его простота.

NDS представлен в языке РL/SQL единственной инструкцией EXECUTE IMMEDIATE, немедленно выполняющей заданную SQL инструкцию, а также расширением инструкции OPEN FOR, позволяющей выполнять сложные динамические запросы.

В отличие от пакета DBMS_SQL, для работы с которым требуется знание десятка процедур и множества правил их использования, при использовании NDS все очень просто.

Инструкция EXECUTE IMMEDIATE

Инструкция ЕХЕСUТЕ IMMEDIATE, используемая для выполнения необходимой SQL-инструкции, имеет следующий синтаксис:

EXECUTE IMMEDIATE строка_SQL
[INTO {переменная[, переменная]…| запись}]
[USING [ IN | OUT | IN OUT ] аргумент
[. [ IN | OUT | IN OUT ] аргумент]…];

где

строка_SQL — строковое выражение, содержащее SQL-инструкцию или блок РL/SQL;
переменная — переменная, которой присваивается содержимое поля, возвращаемого запросом;
запись — запись, основаниая на типе данных который определяется пользавателем или объявляется с помощью атрибута %ROWTYPE, и принимающая всю возвращаемую запросом строку;
аргумент — выражение, значение которого передается SQL-инструкции или блоху РL/SQL, либо идентификатор, являющийся входной и/или выходной переменной для функции или процедуры, вызываемой из блока PL/SQL;
INTO — предложение, используемое для однострочных запросов (для каждого возвращаемого запросом столбца в этом предложении должна быть задана отдельная переменная или же ему должно соответствовать поле записи совместимого типа);
USING — предложение, определяющее параметры SQL-инструкции и используемое как в динамическом SQL, так и в динамическом РL/SQL (способ передачи параметра дается только в РL/SQL, причем по умолчанию для него установлен режим передачи IN).

Инструкция ЕХЕСUТЕ IMMEDIATE может использоваться для выполнения любой SQL-инструкции или PL/SQL-блока, за исключением многострочных запросов.

Если SQL-строка заканчивается точкой с запятой, она интерпретируется как блок РL/SQL. В противном случае воспринимается как DML- или DDL-инструкция.

Строка может содержать формальные параметры, но с их помощью не могут быть заданы имена объектов схемы, скажем, такие, как имена столбцов таблицы.

При выполнении инструкции исполняющее ядро заменяет в SQL-строке формальные параметры (идентификаторы, начинающиеся с двоеточия) фактическими значениями параметров подстановки в предложении USING.
В инструкции ЕХЕСUТЕ IMMEDIATE не разрешается передача литерального значения NULL — вместо него следует указывать переменную соответствующего типа, содержащую это значение.

Несколько примеров:

Создание индекса:

EXECUTE IMMEDIATE ‘CREATE INDEX emp_u_l ON employee (last_name)’;

Хранимую процедуру, выполняющую любую инструкцию DDL, можно создать так:
CREATE OR REPLACE PROCEDURE execDDL(ddl_string in varchar2) is
BEGIN
EXECUTE IMMEDIATE ddl_string;
END;

При наличии процедуры создание того же индекса выглядит так:
BEGIN
execDDL(‘CREATE INDEX emp_u_l ON employee (last_name)’);
END;

DECLARE
v_emp_last_name    VARCHAR2(50);
v_emp_first_name    VARCHAR2(50);
v_birth        DATE;
BEGIN
EXECUTE IMMEDIATE ‘select emp_last_name, emp_first_name, birth ‘ ||
’from EMPLOYEE where id = :id’
INTO v_emp_last_name, v_emp_first_name, v_birth
USING 178;
dbms_output.put_line(v_emp_last_name);
dbms_output.put_line(v_emp_first_name);
dbms_output.put_line(to_char(v_birth, ‘dd.mm.yyyy’));
END;

Инструкция OPEN FOR

Синтаксис инструкции OPEN FOR таков:

OPEN {переменная_курсор|:хост_переменная_курсор } FOR строка_SQL
[USING аргумент[, аргумент]…];

Здесь

переменная_курсор – слаботипизированная переменная-курсор (SYS_REFCURSOR);
:хост_переменная_курсор — переменная-курсор, объявленная в хост-среде PL/SQL;
cтрока_SQL – инструкция SELECT, подлежащая динамическому выполнению;
USING – такое же предложение, как в EXECUTE IMMEDIATE.

Режимы использования параметров

При передаче значений параметров SQL-инструкции можно использовать один из трех режимов:

— IN (только чтение, задан по умолчанию);
— OUT (только запись);
— IN OUT (чтение и запись).

Когда выполняется динамический запрос, все параметры SQL-инструкции, за исключением параметра в предложении RETURNING, должны передаваться в режиме IN:

DECLARE
v_emp_name1 VARCHAR2(50) := ‘Марина’;
v_emp_name2 VARCHAR2(50) := ‘Иванова’;
v_emp_name VARCHAR2(50);
v_id_emp NUMBER := 1666;
BEGIN
EXECUTE IMMEDIATE ‘update ADM.EMPLOYEE ‘ ||
‘set emp_name1 = :v_emp_name1, ‘ ||
’emp_name2 = :v_emp_name2 ‘ ||
‘where id_emp = :v_id_emp ‘ ||
‘returning emp_name1 into :val’
USING IN v_emp_name1, IN v_emp_name2, IN v_id_emp, OUT v_emp_name;
dbms_output.put_line(v_emp_name);
END;
/

Дублирование формальных параметров

При выполнении динамической SQL-инструкции связь между формальными и фактическими параметрами устанавливается в соответствии с их позициями. Однако интерпретация одноименных параметров зависит от того, какой код, SQL или PL/SQL, выполняется с помощью оператора EXECUTE IMMEDIATE:

При выполнении динамической SQL-инструкции (DML- или DDL-строки, не оканчивающейся точкой с запятой) параметр подстановки нужно задать для каждого формального параметра, даже если их имена повторяются.

Когда выполняется динамический блок PL/SQL (строки, оканчивающейся точкой с запятой), нужно указать параметр подстановки для каждого уникального формального параметра.

Передача значений NULL

При попытке передать NULL в качестве параметра подстановки:

EXECUTE IMMEDIATE ‘UPDATE employee SET salary = :newsal WHERE hire_date IS NULL’
USUNG NULL;

произойдет ошибка.

Дело в том, что NULL типа данных не имеет и поэтому не может являться значением одного из типов данных SQL.

Преодолеть это можно так:

1. Можно использовать неинициализированную переменную.

2. Можно преобразовать NULL в типизированное значение: USING TO_NUMBER(NULL);

Использование пакета DBMS_SQL

Пакет DBMS_SQL предоставляет возможность использования в PL/SQL динамического SQL для выполнения DML- или DDL-операций.

Выполнение одного динамического оператора с использованием пакета DBMS_SQL состоит, как правило, из следующих шагов:

Связывание текста динамического оператора с курсором и его синтаксический анализ и разбор;
Связывание входных аргументов с переменными, содержащими реальные значения;
Связывание выходных значений с переменными вызывающего блока;
Указание переменных, в которые будут сохраняться выходные значения;
Выполнение оператора;
Извлечение строк;
Получение значений переменных, извлеченных запросом;
Закрытие курсора.

Ниже приведен перечень функций и процедур пакета DBMS_SQL:

Функции
EXECUTE	Executes a given cursor
EXECUTE_AND_FETCH	Executes a given cursor and fetch rows
FETCH_ROWS	Fetches a row from a given cursor
IS_OPEN	Returns TRUE if given cursor is open
LAST_ERROR_POSITION	Returns byte offset in the SQL statement text where the error occurred
LAST_ROW_COUNT	Returns cumulative count of the number of rows fetched
LAST_ROW_ID	Returns ROWID of last row processed
LAST_SQL_FUNCTION_CODE	Returns SQL function code for statement
OPEN_CURSOR	Returns cursor ID number of new cursor
TO_CURSOR_NUMBER	Takes an OPENed strongly or weakly-typed ref cursor and transforms it into a DBMS_SQL cursor number
TO_REFCURSOR	Takes an OPENed, PARSEd, and EXECUTEd cursor and transforms/migrates it into a PL/SQL manageable REF CURSOR (a weakly-typed cursor) that can be consumed by PL/SQL native dynamic SQL switched to use native dynamic SQL
Процедуры
BIND_ARRAY	Binds a given value to a given collection
BIND_VARIABLE	Binds a given value to a given variable
CLOSE_CURSOR	Closes given cursor and frees memory
COLUMN_VALUE	Returns value of the cursor element for a given position in a cursor
COLUMN_VALUE_LONG	Returns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONG
DEFINE_ARRAY	Defines a collection to be selected from the given cursor, used only with SELECT statements
DEFINE_COLUMN	Defines a column to be selected from the given cursor, used only with SELECT statements
DEFINE_COLUMN_CHAR	Defines a column of type CHAR to be selected from the given cursor, used only with SELECT statements
DEFINE_COLUMN_LONG	Defines a LONG column to be selected from the given cursor, used only with SELECT statements
DEFINE_COLUMN_RAW	Defines a column of type RAW to be selected from the given cursor, used only with SELECT statements
DEFINE_COLUMN_ROWID	Defines a column of type ROWID to be selected from the given cursor, used only with SELECT statements
DESCRIBE_COLUMNS	Describes the columns for a cursor opened and parsed through DBMS_SQL
DESCRIBE_COLUMNS2	Describes the specified column, an alternative to DESCRIBE_COLUMNS
DESCRIBE_COLUMNS3	Describes the specified column, an alternative to DESCRIBE_COLUMNS
PARSE	Parses given statement
VARIABLE_VALUE	Returns value of named variable for given cursor

Когда следует использовать DBMS_SQL

Хотя встроенный динамический SQL гораздо проще применять, а программный код более короткий и понятный, но все же бывают случаи, когда приходится использовать пакет DBMS_SQL.
Это следующие случаи:

Разбор очень длинных строк.
Если строка длиннее 32К, то EXECUTE IMMEDIATE не сможет ее выполнить;
Получение информации о столбцах запроса;
Минимальный разбор динамических курсоров.
При каждом выполнении EXECUTE IMMEDIATE динамическая строка разбирается заново (производится синтаксический анализ, оптимизация и построение плана выполнения запроса), поэтому в некоторых ситуациях это обходится слишком дорого, и тогда DBMS_SQL может оказаться эффективнее.

Новые возможности Oracle 11g

В Oracle 11g появились средства взаимодействия между встроенным динамическим SQL и DBMS_SQL: появилась возможность преобразования курсоров DBMS_SQL в курсорные переменные и наоборот.

Функция DBMS_SQL.TO_REFCURSOR

Преобразует курсор, полученный вызовом DBMS_SQL.OPEN_CURSOR в курсорную переменную, объявленную с типом SYS_REFCURSOR.

Функция DBMS_SQL.TO_CURSOR

Преобразует переменную REF CURSOR в курсор SQL, который затем может передаваться подпрограммам DBMS_SQL.

SQL Injection

SQL Injection – один из типов несанкционированного доступа к данным.

В результате выполнения SQL-инъекций становится возможным выполнять действия, которые не предполагались создателем процедуры.

Технику SQL Injection можно разделить на три группы:

— Statement modification;

— Statement injection;

— Data Type Conversion.

Statement modification

Statement modification – изменение динамического SQL-запроса таким образом, что он будет работать не так, как планировал разработчик.

Пусть имеется следующая функция:

create or replace function SQL_INJECTION(p_ename in varchar2) return varchar2 is

v_ret varchar2(200);

v_qry varchar2(200);

begin

v_qry := ‘select job from scott.emp where ename = »’ || p_ename || »»;

dbms_output.put_line(v_qry);

execute immediate v_qry into v_ret;

return v_ret;

end SQL_INJECTION;

Если вызвать ее с параметром p_ename => »’ union select to_char(sal) from emp where ename = »KING’, то получим доступ к зарплате сотрудника KING:

SQL> select sql_injection(p_ename => »’ union select to_char(sal) from scott.emp where ename = »KING’) king_salary from dual;

KING_SALARY

———————————————————————————

5000

Запрос при этом будет выполняться такой:

select job from scott.emp where ename = »
union
select to_char(sal) from scott.emp where ename = ‘KING’;

Statement injection

Statement injection — добавление еще одного DML- или DDL-оператора (или даже нескольких) к динамическому SQL-оператору.

Рассмотрим такую процедуру:

CREATE OR REPLACE PROCEDURE stmt_injection_demo(user_name IN VARCHAR2) IS

v_block VARCHAR2(4000);

BEGIN

— Следующий динамический блок уязвим для техники statement injection

— из-за использования конкатенации

v_block := ‘BEGIN

DBMS_OUTPUT.PUT_LINE(»user_name: ‘ || user_name || »’);

END;’;

dbms_output.put_line(‘PL/SQL Block: ‘ || v_block);

EXECUTE IMMEDIATE v_block;

END stmt_injection_demo;

Если вызвать ее с параметром user_name => ‘Andy»); update emp set sal = 2500 where ename = upper(»SMITH’, то в результате ее работы будет не только выведено на печать «user_name: Andy», но и еще будет увеличено значение поля sal у сотрудника SMITH.

Data Type Conversion

Еще один малоизвестный способ SQL-инъекций связан с использованием NLS-параметров сессии.

Создадим функцию data_type_conversion, которая по дате приема на работу выдает имя сотрудника:

CREATE OR REPLACE FUNCTION data_type_conversion(p_hiredate IN DATE) RETURN VARCHAR2 IS

v_ret VARCHAR2(200);

v_qry VARCHAR2(200);

BEGIN

v_qry := ‘select ename from scott.emp where hiredate = »’ || p_hiredate || »»;

dbms_output.put_line(v_qry);

EXECUTE IMMEDIATE v_qry INTO v_ret;

RETURN v_ret;

END data_type_conversion;

Результат вызова этой функции:

SQL> select DATA_TYPE_CONVERSION(date ‘1982-01-23’) result from dual;

RESULT

———————————————————————————

MILLER

Если же задать формат даты, как указано ниже, и выполнить select-оператор:

SQL> ALTER SESSION SET NLS_DATE_FORMAT=’»» OR empno = »7499″‘;

SQL> select DATA_TYPE_CONVERSION(date ‘1982-01-23’) result from dual;

, то получим следующий результат:

RESULT

———————————————————————————

ALLEN

Результат мы получим не тот, что ожидалось – из-за того, что наш запрос теперь стал выглядеть так:

select ename from scott.emp where hiredate = » OR empno = ‘7499’;

Методы защиты от SQL-инъекций

Если в приложении используется динамический SQL,то следует использовать следующие методы, которые не позволят злоумышленнику преодолеть наложенные ограничения:

Связывание переменных;
Если в функции SQL_INJECTION оператор для динамического выполнения конструировать не с помощью конкатенации, а с использоыванием связанной переменной, то это не позволит злоумышленнику изменить логику запроса:
v_qry := ‘select job from scott.emp where ename = :p_ename’;

execute immediate v_qry into v_ret using p_ename;

Проверка на соответствие ожидаемым значениям
Если пользователь передал номер департамента для выполнения операции DELETE, то сначала можно проверить, что такой департамент существует.

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

Для безопасного использования строковых литералов полезно использовать функцию DBMS_ASSERT.ENQUOTE_LITERAL, которая к переданной строке добавляет лидирующий и завершающий апострофы, одновременно контролируя отсутствие апострофов внутри строки.

Использование внутреннего преобразования формата

Если в процедуре, использующей динамический SQL, нет возможности использовать связанные переменные, и формирование оператора выполняется с помощью конкатенации, то в таком случае необходимо параметры преобразовывать в текст, используя внутреннее преобразование формата, которое не будет зависеть от настроек NLS, заданных внутри сессии.

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

Преобразование в строковый формат следует использовать для переменных с типом DATE и NUMBER.

Источник

2009 г.

Особенности обработки ошибок сервера базы данных Oracle

К.п.н. Владимир Лихачёв, Калужский педагогический университет им К.Э.Циолковского.

Oracle Magazine — Русское издание

Введение

Для программ, работающих с базами данных, важным является не только корректность обработки их ошибок, но и формирование информативных сообщений об этих ошибках. Наличие таких сообщений позволяет быстрее выявлять причины и исправлять ошибки. Особенно это актуально при работе с программой конечного пользователя, так как ему в большинстве случаев не известна не только структура конкретной БД, но и теоретические основы реляционных баз данных.

Как ни странно, ситуация с формированием сообщений об ошибках в программах довольно часто сильно отличается от обработки самих ошибок. При обработке ошибок обычно удается выработать общую стратегию, что позволяет локализовать их обработку в одной или нескольких функциях. Аналогичный подход для сообщений об ошибках может быть реализован на основе того, что в сообщении об ошибке сервер Oracle указывает тип ошибки и объект базы данных, который явился причиной её возникновения. Такими объектами обычно являются ограничения, как, например, первичные, уникальные и внешние ключи, уникальные индексы, ограничения “not null” и др. Из системных таблиц и представлений базы данных может быть получена подробная информация об этих ограничениях и определены значения, изменение которых и привело к возникновению ошибки. Но проблема заключается в том, что реализация такого механизма формирования сообщений об ошибках в реальных приложениях встречает целый ряд сложностей:

Зависимость сообщения об ошибке от назначения программы. Даже для программ, работающих с одной и той же базой данных, может потребоваться формирование различных сообщений об одной и той же ошибке. Например, в программе для редактирования данных пользователем сообщение должно быть: “Товар с таким названием уже зарегистрирован! Проверьте название товара!”. А в программе импорта данных требуется сообщение с совершенно другим содержанием: “Импортируемые данные дублируются – проверьте дату, за которую выполняется импорт данных!”.
Сложность формирования сообщений для некоторых ошибок, вызванных ограничениями базы данных. Например, в ограничениях CHECK для таблиц могут использоваться довольно сложные запросы и условия. Поэтому формирование сообщений на основе их анализа может оказаться довольно сложной задачей.
Использование в клиентских программах пользовательских названий таблиц и столбцов, отличных от их имен в БД. Например, таблица имеет имя “GOODS”, а в клиентском приложении данные этой таблицы могут отображаться в справочнике как “Товары” или “Продукция”.

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

Рассматриваемый в статье метод формирования информативных сообщений об ошибках для пользователя является довольно универсальным, может быть реализован как в клиентских приложениях, так и на стороне сервера Oracle. Он может использоваться в различных типах программ, как, например:

Программы, использующие специальный интерфейс для ввода и изменения данных БД. В большинстве случаев информативные сообщения об ошибках могут быть получены на основе анализа структуры базы данных. Это позволит информировать пользователя об их причине с минимальными затратами усилий со стороны разработчиков и программного обеспечения.
Программы с возможностью построения пользователем произвольных SQL-запросов. Формирование сообщений на основе анализа структуры базы данных может быть особенно актуально для программ, которые ориентированы на широкий круг пользователей, в том числе и с низким уровнем знаний в этой области. Это позволит сделать более понятными для пользователя сообщения об ошибках в SQL-запросах.
Предметные платформы. Использование методов, описанных в статье, позволит самой предметной платформе формировать информативные сообщения об ошибках базы данных на основе анализа её структуры. Это даст возможность сократить код на языке платформы, используемый для обработки ошибочных ситуаций. А ошибки, которые требуют специальных сообщений, но оказались без таковых, будут достаточно информативными для того, чтобы намного упростить выявление их причины.

Описанные выше проблемы формирования сообщений могут быть решены, если сообщения об ошибках условно разделить на две группы:

универсальные сообщения, которые формируются на основе анализа структуры базы данных;
специальные сообщения, которые определяются индивидуально для каждой ошибки.

Описываемый в статье метод формирования сообщений об ошибках БД может быть применён для многих серверов реляционных баз данных. Пример его использования для баз данных сервера Firebird рассматривается в статье [1].
Если клиентское приложение разработано на Object Pascal (Delphi, Kylix, Free Pascal), то для выявления причин непредвиденных ошибок могут быть полезны возможности библиотеки JEDI [2].

1. Универсальные сообщения об ошибках, вызванных ограничениями БД

Как уже говорилось выше, основная идея создания универсальных сообщений заключается в том, чтобы на основе данных из сообщения об ошибке от Oracle и о структуре базы данных сформировать достаточно информативное и понятное для конечного пользователя сообщение.
Предположим, в таблицу “GOODS” (скрипт 1.1) пользователь пытается добавить товар с названием (столбец “TITLE”), которое уже имеется в таблице.


CREATE TABLE DEMO.GOODS (
   CODE INTEGER NOT NULL , 
   TITLE VARCHAR2(50 byte) NOT NULL , 
   PRICE NUMBER(16, 2) NOT NULL , 
  CONSTRAINT CK_PRICE CHECK (PRICE > 0), 
  CONSTRAINT PK_GOODS PRIMARY KEY(CODE));
COMMENT  ON TABLE  DEMO.GOODS is 'Товары';
COMMENT  ON COLUMN  DEMO.GOODS.CODE is 'Код товара';
COMMENT  ON COLUMN  DEMO.GOODS.TITLE is 'Название';
COMMENT  ON COLUMN  DEMO.GOODS.PRICE is 'Цена';


CREATE UNIQUE INDEX DEMO.IDX_GOODS_TITLE ON DEMO.GOODS (TITLE);

Скрипт 1.1. Создание таблицы “GOODS”.

Сервер в этом случае сгенерирует ошибку, так как столбец “TITLE”, в котором хранится название товара, включено в уникальный индекс “DEMO.IDX_GOODS_TITLE”:

ORA-00001: нарушено ограничение уникальности (DEMO.IDX_GOODS_TITLE)

Вместо этого сообщения для пользователя может быть сформировано, например, одно из сообщений:

Значение поля “Название” в таблице “Товары” должно быть уникальным!
Товар с таким названием уже зарегистрирован! Проверьте название товара!
В справочнике товаров не могут быть товары с одинаковыми названиями!

Хотя эти сообщения и различаются, но в них всех указывается информация об объекте, для которого нарушено ограничение уникальности – это поле “Название” таблицы “Товары”.

Одна из проблем формирования такого типа сообщений, заключается в том, что пользовательские названия полей и таблиц, отличаются от имен таблиц и столбцов в базе данных. Чтобы пользователю было понятно сообщение об ошибке, в нем должны использоваться именно пользовательские названия. Для сопоставления имен таблиц и полей и их пользовательских названий может использоваться отдельная таблица или комментарии для таблиц и столбцов. Последний вариант можно считать более предпочтительным, так как это позволяет одновременно документировать базу данных. Именно поэтому в скрипте 1.1 в качестве комментариев для таблицы и её столбцов приведены их пользовательские названия. Если сравнить выше приведённые сообщения и комментарии для таблицы и столбцов, то можно заметить, что формирование первого сообщения является наиболее простым вариантом. Для формирования двух других сообщений может потребоваться лексический синтез, но это уже отдельная задача. Хочется обратить внимание, что в дальнейшем в статье приводится только один из возможных вариантов сообщения для каждого случая ошибки. На практике выбор стиля сообщения и его содержания может зависеть от целого ряда факторов и будет определяться разработчиком системы.

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

Далее рассматривается формирование универсальных сообщений для наиболее часто встречающихся ошибок, обусловленных ограничениями БД.

2. Не указано значение поля, обязательного для заполнения (ограничение NOT NULL)

Эта ошибка генерируется сервером в нескольких случаях:

нарушено ограничение “not null”, установленное для столбца;
не указано значение столбца, входящего в уникальный индекс, главный или уникальный ключи.

Во всех этих случаях сервер генерирует ошибку:

ORA-01400: невозможно <вставить/заменить> NULL в ("<Схема>"."<Таблица>"."<Столбец>")

Для получения описания таблицы и столбца из сообщения об ошибке, можно использовать запрос 2.1.

select  tc.comments astable_comment, cc.comments ascolumn_comment
from all_tab_columns c, all_tab_comments tc, all_col_comments cc
where  c.owner      = :owner 
  and c.table_name = :table_name and c.column_name = :column_name 
  and tc.owner = c.owner and tc.table_name = c.table_name 
  and cc.owner = c.owner
  and cc.table_name = c.table_name and cc.column_name = c.column_name

Запрос 2.1. Получение описания таблицы и столбца

В качестве параметров запроса “owner”, ”table_name”, ”column_name” необходимо указать соответственно имя схемы, таблицы и столбца из сообщения об ошибке. Запрос возвращает комментарии для таблицы и столбца.

Используя результаты этого запроса, может быть сформировано сообщение об ошибке, например, следующего содержания:

Необходимо указать значение столбца “<Описание поля>” в таблице “<Описание таблицы>” при <добавлении новой/изменении> записи.

3. Нарушена уникальность значения поля или набора столбцов

Необходимость ввода уникального значения столбца может требоваться в основном в трех случаях:

столбец входит в главный ключ;
столбец включен в уникальный ключ;
столбец входит в уникальный индекс.

Во всех трех случаях Oracle Database генерирует одну и ту же ошибку:

ORA-00001: нарушено ограничение уникальности (<Схема>.<Ограничение>)

В сообщении об ошибке указывается ограничение, которое вызвало ошибку. Для получения информации о столбцах, входящих в главный или уникальный ключи,
можно использовать запрос 3.1, для получения информации об индексе — запрос 3.2.

select  dcs.constraint_type, cc.table_name, tc.comments astable_comment, 
       cc.column_name, ccom.comments as column_comment

from all_cons_columns cc 
  join all_tab_comments tc 
         on  (tc.owner = cc.owner and tc.table_name = cc.table_name)
  join all_col_comments ccom 
         on (ccom.owner = cc.owner and ccom.table_name = cc.table_name 
                and  ccom.column_name = cc.column_name )
  join all_constraints dcs 
         on (dcs.constraint_name = cc.constraint_name)

where  cc.owner = :owner and cc.constraint_name = :key_name

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

select  ic.table_name, 
          tc.comments astable_comment, 
          ic.column_name, 
          ccom.comments ascolumn_comment
   from all_ind_columns ic 
       join all_tab_comments tc 
           on (tc.owner = ic.table_owner and tc.table_name = ic.table_name)
       join all_col_comments ccom
           on (ccom.owner = ic.table_owner 
                            and ccom.table_name = ic.table_name 
                            and ccom.column_name = ic.column_name )
    where  table_owner = :owner and index_name = :index_name

Запрос 3.2. Получение информации о столбцах таблицы, входящих в индекс.

В качестве параметров запросам передаётся имя схемы (“owner“), имя ключа (“key_name“) или индекса (“index_name“). Запросы возвращают имена и комментарии для таблиц и столбцов, входящих в ограничение. Запрос 3.1 возвращает так же тип ограничения (“constraint_type”): “P” – главный ключ, “U” – уникальный ключ. Количество записей, возвращаемых запросами, соответствует количеству столбцов в ограничении уникальности.

На основе полученной информации об ограничении уникальности для пользователя могут быть сформированы варианты
сообщений об ошибке, например, приведенные в разделе 1.

4. Ошибки, вызываемые ограничениями внешних ключей

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

В подчинённую таблицу добавляется запись, в которой для столбца, входящего во внешний ключ, нет соответствующего значения в главной таблице. Аналогичная ситуация происходит при изменении значения столбца подчиненной таблицы в случае, если нового значения столбца нет в главной таблице. Oracle Database в этом случае генерирует ошибку:
```
ORA-02291: нарушено ограничение целостности (<Схема>.<Внешний ключ>) 
- исходный ключ не найден
```
В главной таблице выполняется попытка изменения значения столбца, на которое имеется ссылка в подчиненной таблице. Для этого случая Oracle Database генерирует ошибку:
```
ORA-02292: нарушено ограничение целостности (<Схема>.<Внешний ключ>) 
- обнаружена порожденная запись
```
В главной таблице выполняется попытка удаления данных, на которые имеется ссылка в подчиненной таблице. Если в определении связи между таблицами указано ограничение “NO ACTION” для операции удаления данных, то Oracle не позволяет удалять данные из главной таблицы, если в подчинённой таблице есть записи связанные с удаляемой записью. Для этой ситуации Oracle Database генерирует ошибку, аналогичную предыдущему случаю.

Для получения информации о столбцах главной и подчиненной таблиц, входящих во внешний ключ, можно использовать приведенный ниже запрос 4.1.

select    a.constraint_name,  
            a.table_name, 
            tc1.comments astable_comment,
            a2.column_name, 
            cc1.comments ascolumn_comment,
            b.owner asr_owner,   
            b.table_name asr_table_name, 
            tc2.comments asr_table_comment,
            b2.column_name asr_column_name,
            cc2.comments asr_column_comment
     from   all_constraints a, 
                all_constraints b, 
                all_cons_columns a2,
                all_cons_columns b2, 
                all_tab_comments tc1, 
                all_col_comments cc1,
                all_tab_comments tc2, 
                all_col_comments cc2
     where   a.owner = :owner  
                and a.constraint_type = 'R'
                and a.constraint_name = :foreign_key
                and b.constraint_type in ('P','U') 
                and b.constraint_name = a.r_constraint_name
                and b.owner = a.r_owner   
                and a2.constraint_name = a.constraint_name 
                and a2.table_name = a.table_name 
                and a2.owner = a.owner
                and b2.constraint_name = b.constraint_name 
                and b2.table_name = b.table_name 
                and b2.owner = b.owner 
                and b2.position = a2.position
                and tc1.owner = a.owner 
                and tc1.table_name = a.table_name
                and cc1.owner = a2.owner
                and cc1.table_name  = a2.table_name 
                and cc1.column_name = a2.column_name
                and tc2.owner = b.owner 
                and tc2.table_name = b.table_name
                and cc2.owner = b2.owner 
                and cc2.table_name  = b2.table_name 
                and cc2.column_name = b2.column_name

Запрос 4.1. Получение информации о внешнем ключе.

Запрос имеет два параметра: “owner” и “foreign_key” – схема и внешний ключ, о котором необходимо получить информацию. Он возвращает информацию о столбцах, входящих во внешний ключ: «table_name», «table_comment» — имя и описание подчиненной таблицы; «column_name», «column_comment» — имя и описание столбца подчиненной таблицы. Столбцы запроса с префиксом “r_” возвращают информацию о главной таблице. Количество записей возвращаемых запросом соответствует количеству столбцов, входящих во внешний ключ.

На основе этой информации могут быть сформированы сообщения об ошибках внешних ключей для пользователя.

5. Специальные сообщения об ошибках, вызванных ограничениями БД

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

Можно выделить две группы специальных сообщений об ошибках. Первый тип специальных сообщений предназначен для использования во всех приложениях, которые работают c общей базой данных. Их можно условно назвать “специальные сообщения об ошибках уровня базы данных”. Вторая группа сообщений специфична для конкретного приложения. Они могут быть необходимы, когда различные приложения должны выдавать пользователю различные сообщения об одной и той же ошибке. Их можно условно назвать “специальные сообщения об ошибках уровня приложения”. Информацию о первой группе сообщений удобно хранить в самой базе данных и использовать для этого отдельную таблицу. Сообщения, специфичные для программы могут храниться в её ресурсах, например, в виде отдельного файла или также в БД. Идентификация специальных сообщений может выполняться на основе кода ошибки, имени схемы и одного или нескольких ключевых слов из сообщения об ошибке.

6. Сообщения об ошибках ограничений CHECK для таблиц

При возникновении ошибки, вызванной ограничением CHECK для таблицы, сервер генерирует ошибку:

ORA-02290: нарушено ограничение целостности CHECK (<Схема>.<Имя ограничения>)

Как уже говорилось выше, для таких ошибок часто удобно использовать специальные сообщения. Например, для ограничения «CK_PRICE» таблицы “GOODS” может использоваться специальное сообщение, хранимое в таблице специальных сообщений:

Цена товара в справочнике “Товары” должна быть больше нуля.

7. Комплексное использование специальных и универсальных сообщений об ошибках

Гибкий механизм формирования информативных сообщений об ошибках для пользователя реализуется в несколько этапов (рис. 1):

Вывод специального сообщения об ошибке уровня приложения. Сначала программа выполняет поиск сообщения об ошибке среди специальных сообщений для данного приложения. Если такое сообщение найдено, оно выводится, и формирование сообщения на этом завершается.
Вывод специального сообщения об ошибке уровня базы данных. Если на этапе 1 сообщение не было найдено, выполняется поиск специального сообщения об ошибке уровня базы данных. Если найдено, то оно выводится пользователю и формирование сообщения об ошибке на этом заканчивается.
Вывод сообщения на основе анализа структуры базы данных (универсального сообщения). В случае, если на предыдущих этапах специальных сообщений не обнаружено, то оно формируется на основе анализа структуры базы данных. Оно выводится пользователю и на этом формирование сообщения завершается.
Вывод сообщения от сервера базы данных. В случае, если на трех предыдущих этапах сообщение для пользователя не было сформировано,
то отображается сообщение об ошибке от Oracle. Такая ситуация может возникнуть по нескольким причинам. Например, при возникновении пользовательской
ошибки, которая была преднамеренно сгенерирована в хранимой процедуре или триггере c помощью функции RAISE_APPLICATION_ERROR,
и изменение содержания сообщения о которой не требуется.

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

В ряде случаев такие сообщения могут быть даже более информативными, чем сформированные на предыдущих этапах.
Например, вместо ограничения CK_PRICE для таблицы DEMO.GOODS (скрипт 1.1) можно в триггере перед вставкой и обновлением записи выполнять
необходимую проверку и генерировать сообщение для пользователя в уже “готовом” виде:

CREATE OR REPLACE TRIGGER DEMO.TRIGGER_GOODS BEFORE
INSERT
OR UPDATE OF PRICE ON DEMO.GOODS FOR EACH ROW BEGIN
  IF :NEW.PRICE <= 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Цена товара "' || :NEW.TITLE || '
       " должна быть больше 0 руб (указана цена '|| :NEW.PRICE ||' руб)');
  END IF;
END TRIGGER_GOODS;

В случае цены товара меньшей или равной нулю сервер сгенерирует ошибку, например:

ORA-20001: Цена товара "Лейка" должна быть больше 0 руб (указана цена 0 руб)

Клиентское приложение может сразу передать это сообщение пользователю
без изменения.

Другой причиной может быть появление ошибки, для которой формирование
сообщения не предусмотрено.

Рис. 1. Последовательность формирования сообщения об ошибке базы данных.

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

Заключение

Целью данной статьи является показать основные идеи метода, который может использоваться для формирования информативных сообщений об ошибках базы данных Oracle для конечного пользователя.
Хотя за рамками статьи остались некоторые моменты реализации, хочется надеяться, что описанный в статье подход позволит уменьшить трудозатраты при разработке программного обеспечения, повысить его надежность и качество.

Литература

В.Н. Лихачёв «Общий метод формирования сообщений об ошибках при работе с базами данных и его использование для БД Firebird»
// RSDN Magazine. — 2008. -№ 4. (http://www.rsdn.ru/article/db/FBErrors.xml)
В.Н. Лихачёв «Локализация ошибок в приложениях Delphi c помощью библиотеки Jedi Code Library» // RSDN Magazine. — 2005. — № 3. — C. 23-27.
(http://www.rsdn.ru/article/Delphi/DelphiJCL.xml)

Источник

Знатоки баз данных узнают имя Oracle как мощную силу в мире реляционных систем управления базами данных (СУБД). Oracle производит очень мощные решения для СУБД на протяжении десятилетий и остается лидером в этой области. Многие конечные пользователи продуктов баз данных могут быть сбиты с толку или озадачены, когда они используют готовое решение, а оно выдает сообщение об ошибке. Одним из распространенных сообщений об ошибке, выдаваемых Oracle, является ошибка ORA-06512.

Oracle — это система управления базами данных, которая существует уже сорок лет в различных формах. Первоначально она использовала схему SCOTT, названную в честь одного из первых сотрудников Oracle. Вы даже впервые вошли в Oracle с именем пользователя «scott» и паролем «tiger», названным в честь кота Скотта. Сейчас существует несколько схем, используемых в зависимости от того, для чего вы используете Oracle.

Если вы хотите узнать больше о Oracle с нуля, эта страница очень полезна .

Исправление ошибок ORA-06512

В Oracle ошибка ORA-06512 — это ошибка общего исключения, которая говорит вам, где что-то идет не так. Это одна из наименее специфичных ошибок, выдаваемых Oracle, поскольку она сообщает только о наличии проблемы, но не о том, что именно идет не так.

Например, типичное сообщение об ошибке может выглядеть так:

«ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at «DATABASE_NAME», line 66

ORA-06512: at line 1′′

Первая строка говорит о типе ошибки, в данном случае запрос возвращает больше данных, чем ожидает запрос, поэтому он не знает, как это обработать. Код ‘ORA-01422’ — это фактический код ошибки, на который вам нужно обратить внимание. ORA-06512 — это просто общий код ошибки.

Вторая строка говорит вам, где происходит ошибка. DATABASE_NAME — это имя базы данных, в которой вы работаете в данный момент. Строка 66 — это строка, в которой происходит ошибка, и именно ее нужно проверить, чтобы исправить ошибку.

Третья строка в синтаксисе ошибки говорит вам, откуда происходит вызов. Проверьте первую строку и вы увидите обращение к DATABASE_NAME.

Чтобы исправить эту конкретную ошибку, вам нужно исправить проблему, вызванную ORA-01422, а именно ‘exact fetch возвращает больше запрошенного количества строк’ или добавить обработчик исключений, чтобы сказать Oracle игнорировать ее. Поскольку исправление основной проблемы всегда предпочтительнее, то лучше поступить следующим образом.

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

Ожидая больше одной строки:

for X in ( select * from t where … )

loop

— обработать запись X здесь

end loop;

Это должно устранить ошибку в запросах к базе данных, где возвращается более одной строки.

Если вы ожидаете возврата только одной строки, вы можете попробовать:

begin

select * into ….

from t where ….

process….

exception

when NO_DATA_FOUND then

error handling code when no record is found

when TOO_MANY_ROWS then

error handling code when too many records are found

end;

Этот второй метод должен доставить только один ряд без выброса ошибки ‘ORA-01422: exact fetch returns more than requested number of rows’ и, следовательно, оригинальной ошибки ORA-06512.

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

declare

c1 cursor for select * from t where …

begin

open c1;

fetch c1 into ….

if ( c1%notfound ) then

error handling for no record found

end if;

close c1;

end;

(Если вы знаете SQL, вас могут немного смутить эти командные строки… Oracle использует не Transact-SQL, а собственное процедурное расширение языка SQL, PL/SQL. Хотя он похож на Transact-SQL, PL/SQL делает много умных вещей и является очень мощным инструментом сам по себе. Вы можете найти этот FAQ по PL/SQL полезным при попытке изучить Oracle.)

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

У вас есть советы или рекомендации по Oracle, которыми вы можете поделиться? Расскажите нам о них в комментариях!

YouTube видео: Как исправить ошибку ORA-06512 в Oracle DB

Источник

PL/SQL блок:

DECLARE
… — объявляющая секция
BEGIN
… — выполняющая секция
EXCEPTION
… — секция обработки исключительных ситуаций
END;
/

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

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

Исключения бывают:

— стандартные
— определенные пользователем

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

Исключительные ситуации, определяемые пользователем,
устанавливаются явно при помощи оператора RAISE.

Обрабатываются исключения так:

EXCEPTION
    WHEN имя_ex1 THEN
        …; — обработать
    WHEN имя_ex2 THEN
        …; — обработать
    WHEN OTHERS THEN
        …; — обработать
END;
/

Имена исключений не должны повторяться т.е. каждое исключение может
обрабатываться максимум только одним обработчиком в секции EXCEPTION

Один обработчик может обслуживать несколько исключительных ситуаций
и их нужно перечислить в условии WHEN через OR

EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
INSERT INTO log_table(info) VALUES (‘A select error occurred.’);
END;
/

Два исключения одновременно один обработчик обработать не может:

WHEN имя_ex1 AND имя_ex2 — > ERR

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

DECLARE
    e_my_ex EXCEPTION;
    …
BEGIN
    IF (…) THEN
        RAISE e_my_ex;
    END IF;
    …

EXCEPTION
WHEN e_my_ex THEN
…
END;
/

После перехвата более специализированных исключений:

WHEN … THEN
…
WHEN … THEN

мы можем перехватить все остальные исключения с помощью:

WHEN OTHERS THEN
…

Обработчик OTHERS рекомендуется помещать на самом высоком уровне программы:
(В самом высшем блоке)
для обеспечения распознавания всех возможных ошибок.

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

Не используйте в промышленном коде такое:

WHEN OTHERS THEN NULL;

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

Обработчик OTHERS должен регистрировать ошибку и возможно предоставлять
дополнительную информацию для дальнейшего исследования.

WHEN OTHERS THEN
INSERT INTO log_table(info) VALUES (‘Another error occurred.’);
END;
/

Информацию об ошибках можно получить при помощи двух встроенных функций:

— SQLCODE
— SQLERRM

первая возвращает код текущей ошибки а вторая текст сообщения об ошибке

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

SQLCODE возвращает 1
а
SQLERRM «User-defined Exception»

WHEN OTHERS THEN
    v_ErrorCode := SQLCODE;
    v_ErrorText := SUBSTR(SQLERRM, 1, 200);
    INSERT INTO log_tab(code, message, info) VALUES (v_ErrorCode, v_ErrorText, ‘Oracle error.’);
END;
/

В таблице log_tab поле message ограничено 200 символами
и чтобы не произошло ошибки при вставке, мы урезаем длину
сообщения до 200 символов с помощью SUBSTR
А то максимальная длина сообщения может достигать 512 символов.

Функция SQLERRM может принимать один числовой аргумент.
При этом она возвратит текст сообщения об ошибке, код которой равен заданному числу.

Аргумент должен быть всегда отрицательным числом.
Если аргумент равен 0, то будет возвращено сообщение:
ORA-0000: normal, succesful completion

При положительном аргументе не равном 100 будет возвращено сообщение:
non-ORACLE Exception

А при

SQLERRM(100) — > ORA-1403: no data found

Это исключение ANSI

Остальные коды ошибок Oracle все отрицательные.

Для получения информации об ошибке можно также использовать функцию
FORMAT_ERROR_STACK из пакета DBMS_UTILITY

Её можно непосредственно использовать в операторах SQL:

WHEN OTHERS THEN
    INSERT INTO log_tab(code, message, info) VALUES (NULL,
                                                     SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 200),
                                                    ‘Oracle error occurred.’);
END;
/

Ещё одна функция.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

она аналогична FORMAT_ERROR_STACK
но не подвержена ограничению длины сообщения в 2000 байт.
Она возвращает полностью весь стек ошибок на момент инициирования исключительной ситуации.

Любое именованное исключение можно связать с конкретной ошибкой ORACLE.

Например, в ORACLE есть стандартная ошибка ORA-1400, которая возникает при пропуске значения
или вставке значения NULL в столбец с ограничением NOT NULL.

ORA-1400: mandatory NOT NULL column missing or NULL during insert.

Мы хотим создать свое пользовательское именованное исключение и связать его с этой стандартной ошибкой ORA-1400

DECLARE
e_my_ex EXCEPTION;
PRAGMA EXCEPTION_INIT(e_my_ex, -1400);

BEGIN
WHEN e_my_ex THEN
INSERT INTO log_tab(info) VALUES (‘ORA-1400 occurred.’);
END;
/

Теперь мы перехватываем её по имени с помощъю WHEN или THEN

Все стандартные исключительные ситуации также ассоциируются с соответствующими им ошибками Oracle
при помощи прагмы EXCEPTION_INIT в пакете STANDARD

VALUE_ERROR — > ORA-6501
TO_MANY_ROWS — > ORA-1422
ZERO_DIVIDE — > ORA-1476
……….
и т.д.

Так что если вам не хватает некоего имени конкретной ошибки ORA-NNNN,
то придумайте свое имя и свяжите его с ошибкой с помощью прагмы : EXCEPTION_INIT

Для собственных пользовательских исключений можно придумать свои коды ошибок, которые разрешено брать из диапазона:
-20000 до -20999
и придумать свой текст сообщения

RAISE_APPLICATION_ERROR(номер, текст, [флаг]);

TRUE — пополнить список ранее произошедших ошибок
FALSE — новая ошибка заместит текущий список ошибок (по умолчанию)

set serveroutput on

variable a NUMBER;
variable b NUMBER;

exec :a := 0;
exec :b := 10;

DECLARE
    l_a NUMBER := :a;
    l_b NUMBER := :b;
    l_c NUMBER;

BEGIN
    IF l_a = 0 THEN
        raise_application_error(-20005, ‘Divizor is 0’);
    END IF;
    l_c := l_b / l_a;
    dbms_output.put_line(‘The result: ‘||l_c);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END;
/

Поскольку у исключения нет имени, то его может обработать только обработчик OTHERS

Но такое исключение можно и поименовать
и с помощью прагмы связать с нашим кодом.

DECLARE

    my_ex EXCEPTION;
    …..
    …..
    PRAGMA EXCEPTION_INIT(my_ex, -20005);

BEGIN
    IF (…) THEN
        raise_application_error(-20005, ‘Divizor is 0’);
    …..
    …..

EXCEPTION
WHEN my_ex THEN
dbms_output.put_line(SQLERRM);
END;
/

Теперь это исключение можно обработать по имени с помощью:

WHEN my_ex THEN

EXCEPTION PROPAGATION

enclosing block — обьемлющий блок

Если в текущем блоке имеется обработчик данной исключительной ситуации,
то он выполняется и блок успешно завершается.
Управление передаётся вышестоящему блоку.

Если обработчик отсутствует, исключительная ситуация передается в обьемлющий блок и инициируется там.
Если обьемлющего блока не существует, то исключение будет передано вызывающей среды (например SQL*Plus).

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

BEGIN

    p(…); — вызов процедуры
EXCEPTION
    WHEN OTHERS THEN
        — исключение инициированное p()
        — будет обработано здесь
END;
/

Исключения инициируемые в секции обьявлений (DECLARE) не обрабатываются секцией EXCEPTION
текущего блока, а передаются в EXCEPTION обьемлющего блока.

Тоже самое, если исключение инициируется в секции EXCEPTION,
то обработка данного исключения передается в обьемлющий блок.

Исключительную ситуацию можно обработать в текущем блоке и сразу снова установить
то же самое исключение, которое будет передано в обьемлющую область:

DECLARE

A EXCEPTION;
BEGIN

    RAISE A;
EXCEPTION
    WHEN A THEN
        INSERT INTO log_tab(info) VALUES (‘Exception A occurred.’);
        COMMIT;
        RAISE;
END;
/

Тут commit гарантирует, что результаты insert будут зафиксированы
в базе данных в случае отката транзакции.

С помощью пакета UTL_FILE можно избежать необходимости commit
или используйте автономные транзакции.

Область действия исключительной ситуации

BEGIN
    DECLARE
        e_ex EXCEPTION; — видно по имени только внутри блока
    BEGIN
        RAISE e_ex;
    END;
EXCEPTION
    — тут исключение не видно по имени e_ex
    — и его можно обработать с помощью обработчика OTHERS

    WHEN OTHERS THEN
        — инициируем это исключение повторно
        RAISE; — Теперь это исключение передается вызывающей среде
END;
/

Если сообщение об ошибке, определяемой пользователем, нужно передать из блока,
рекомендуется описывать исключительную ситуацию и модуле так,
чтобы она была видима вне этого блока.
Или воспользуйтесь функцией : RAISE_APPLICATION_ERROR

Как описать исключение, которое будет видно вне блока?

Нужно создать пакет Globals и описать в нем пользовательское исключение.
Такая исключительная ситуация будет видима и во внешнем блоке.

CREATE OR REPLACE PACKAGE Globals AS
e_ex EXCEPTION;
END Globals;

BEGIN
    BEGIN
        RAISE Globals.e_ex;
    END;

EXCEPTION
    WHEN Globals.e_ex THEN
        — инициируем повторно
        — для передачи в вызывающую среду
        RAISE;
END;
/

В пакете Globals можно также объявлять:

— таблицы
— переменные
— типы

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

Используйте обработчик OTHERS на самом верхнем уровне программы.
И пусть он регистрирует факт и время возникновения ошибки.
И ни одна ошибка не останется без внимания.

DECLARE

    v_ErrorNumber NUMBER;
    v_ErrorText   VARCHAR2(200);
BEGIN
    …
    …

EXCEPTION
WHEN OTHERS THEN
…

v_ErrorNumber := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM, 1, 200);

        INSERT INTO log_tab(code, message, info)
        VALUES (v_ErrorNumber, v_ErrorText,
                ‘Oracle error …at ‘ || to_char(sysdate, ‘DD-MON-YYHH24:MI:SS’));
END;
/

Можно использовать и утилиту DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
она регистрирует первоначальное местовозникновения исключения.

Как определить, где произошла ошибка?

BEGIN

    SELECT …
    SELECT …
    SELECT …
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    — какой select инициировал ошибку?
END;
/

Можно создать счетчик, указывающий на sql — оператор:

DECLARE

v_sel_count NUMBER := 1;
BEGIN

    SELECT …
    v_sel_count := 2;
    SELECT …
    v_sel_count := 3;
    SELECT …

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        INSERT INTO log_tab(info)
        VALUES (‘no data found in select ‘||v_sel_count);
END;
/

Можно разместить каждый select в собственном врутреннем блоке

BEGIN

    BEGIN
        SELECT …
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            INSERT INTO log_tab(info)
            VALUES (‘no data found in select 1’);
    END;

    BEGIN
        SELECT …
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            INSERT INTO log_tab(info)
            VALUES (‘no data found in select 2’);
    END;

    BEGIN
        SELECT …
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            INSERT INTO log_tab(info)
            VALUES (‘no data found in select 3’);
    END;

END;
/

Или использовать : DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
и потом анализировать файл трассировки.

Пусть в нашей программе Oracle выдает ошибку ORA-01844: not f valid month

перехватить его можно так:

EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1843 THEN

Да, код плохо читаем.
Сделаем его более лучшим:

PROCEDURE my_procedure
IS
invalid_month EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_month, -1843);

BEGIN
….
EXCEPTION
WHEN invalid_month THEN

так уже более понятней.

Источник

Обнаружение ошибок в носителях

Обнаружение ошибок в блоках данных

Настройка параметров инициализации

Применение команды ANALYZE

Применение утилиты DBVERIFY

Применение пакета DBMS_REPAIR

Инициатива HARD

Вас заинтересует / Intresting for you:

Основные концепции и терминология обработки исключений

Определение исключений

Объявление именованных исключений

Связывание имени исключения с кодом ошибки

Директива EXCEPTION_INIT

Рекомендации по использованию EXCEPTION_INIT

Именованные системные исключения

Область действия исключения

Инициирование исключений

Команда RAISE

Процедура RAISE_APPLICATION_ERROR

Обработка исключений

Встроенные функции ошибок

Подробнее о DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Только номер строки, пожалуйста

Полезные применения SQLERRM

Объединение нескольких исключений в одном обработчике

Необработанные исключения

Передача необработанного исключения

Потеря информации об исключении

Примеры передачи исключения

Продолжение выполнения после исключений

Написание раздела WHEN OTHERS

Построение эффективной архитектуры управления ошибками

Определение стратегии управления ошибками

Стандартизация обработки разных типов исключений

Преднамеренные исключения

Нежелательные и непредвиденные исключения

Как извлечь пользу из этой классификации

Коды ошибок, связанные с конкретным приложением

Стандартизация обработки ошибок

Работа с «объектами» исключений

Создание стандартного шаблона для обобщенной обработки ошибок

Оптимальная организация обработки ошибок в PL/SQL

Вас заинтересует / Intresting for you:

Исключения

Предупреждающие сообщения при компиляции

Обработка исключений в PL/SQL

Создание собственных исключений

Связываем исключение с кодом ошибки

Именованные системные исключения

Инициирование исключений

Оператор RAISE

Использование процедуры RAISE_APPLICATION_ERROR

Использование функций обработки ошибок

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

Эскалация необработанного исключения

На что стоит обратить внимание

Инструкции NDS

Инструкция EXECUTE IMMEDIATE

Инструкция OPEN FOR

Режимы использования параметров

Дублирование формальных параметров

Передача значений NULL

Использование пакета DBMS_SQL

Когда следует использовать DBMS_SQL

Новые возможности Oracle 11g

SQL Injection

Statement modification

Statement injection

Data Type Conversion

Методы защиты от SQL-инъекций

Использование внутреннего преобразования формата

Особенности обработки ошибок сервера базы данных Oracle

Введение

1. Универсальные сообщения об ошибках, вызванных ограничениями БД

2. Не указано значение поля, обязательного для заполнения (ограничение NOT NULL)

3. Нарушена уникальность значения поля или набора столбцов

4. Ошибки, вызываемые ограничениями внешних ключей

5. Специальные сообщения об ошибках, вызванных ограничениями БД

6. Сообщения об ошибках ограничений CHECK для таблиц

7. Комплексное использование специальных и универсальных сообщений об ошибках