Язык pl sql oracle обработка ошибок. Oracle errors: ошибки базы, объяснение и действия по устранению. Группы ошибок Oracle

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

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

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

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

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

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

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

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

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

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

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":

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

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

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

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

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

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

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

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

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

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

select tc.comments as table_comment, cc.comments as column_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 as table_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 as table_comment, ic.column_name, ccom.comments as column_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. Ошибки, вызываемые ограничениями внешних ключей

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

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

В главной таблице выполняется попытка изменения значения столбца, на которое имеется ссылка в подчиненной таблице. Для этого случая Oracle Database генерирует ошибку:

В главной таблице выполняется попытка удаления данных, на которые имеется ссылка в подчиненной таблице. Если в определении связи между таблицами указано ограничение "NO ACTION" для операции удаления данных, то Oracle не позволяет удалять данные из главной таблицы, если в подчинённой таблице есть записи связанные с удаляемой записью. Для этой ситуации Oracle Database генерирует ошибку, аналогичную предыдущему случаю.

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

select a.constraint_name, a.table_name, tc1.comments as table_comment, a2.column_name, cc1.comments as column_comment, b.owner as r_owner, b.table_name as r_table_name, tc2.comments as r_table_comment, b2.column_name as r_column_name, cc2.comments as r_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. Вывод специального сообщения об ошибке уровня приложения. Сначала программа выполняет поиск сообщения об ошибке среди специальных сообщений для данного приложения. Если такое сообщение найдено, оно выводится, и формирование сообщения на этом завершается.

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

Исключения

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

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

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

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

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

В PL/SQL можно выдавать пользователям информацию об ошибке двумя способами. Первый способ – использовать команду SQLCODE, которая возвращает код ошибки. Этот код представляет собой отрицательное число, обычно равное номеру ошибки ORA, которая выводится при завершении приложения, если исключение осталось необработанным. Второй способ – возвращать текстовое сообщение, описывающее ошибку. Неудивительно, что соответствующая команда называется SQLERRM. В обработчике исключения можно использовать как SQLCODE, так и SQLERRM. Замечание: не у всех системных исключений есть имена.

Таблица 1 . Системные исключения

Системное исключение, код ошибки	Причина возбуждения
CURSOR _ ALREADY _ OPEN ORA -06511	Попытка открыть уже открытый курсор
DUP_VAL_ON_INDEX ORA- 00001	Попытка вставить повторяющееся значение в столбец, имеющий уникальный индекс, а следовательно, ограничение уникальности
INVALID _ CURSOR ORA-01001	Попытка применить команду FETCH к неоткрытому курсору или попытка закрыть курсор, который не открывался
NO_DATA_FOUND ORA-01403	Попытка выполнить SELECT INTO, когда SELECT возвращает нулевое количество строк (а также другие причины, описание которых выходит за рамки этой книги)
PROGRAM _ ERROR ORA-06501	Внутренняя ошибка. Обычно означает, что вам нужно обратиться в службу поддержки Oracle
STORAGE _ ERROR ORA-06500	Программе не хватает системной памяти
TIMEOUT_ON_RESOURCE ORA-00051	Программа слишком долго ожидала доступности некоторого ресурса
TOO_MANY_ROWS ORA-01422	SELECT INTO в PL/SQL вернул более одной строки
VALUE _ ERROR ORA-06502	PL/SOL встретил неправильное преобразование или усечение данных, или неправильное ограничение на данные
ZERO _ DIVIDE ORA-01476	Попытка деления на нуль
	Все прочие исключения и внутренние ошибки, которые не охватываются исключениями, определенными в базовом блоке. Используется в тех случаях, когда вы точно не знаете, какое именованное исключение предстоит обрабатывать, и хотите обрабатывать любые возбуждаемые исключения

Теперь вернемся к самому первому примеру этой главы и используем в нем SQLCODE и SQLERRM. Ниже приведен исходный код примера и результаты его запуска (рис.1).

Num_a NUMBER:= 6;

Num_b NUMBER;

Num_b:= 0;

Num_a:= Num_a / Num_b;

Num_b:= 7;

dbms_output.put_line(" Value of Num_b "|| Num_b);

EXCEPTION

WHEN ZERO_DIVIDE THEN

err_num NUMBER:= SQLCODE;

err_msg VARCHAR2(512) := SQLERRM;

dbms_output.put_line("ORA Error Number " || err_num);

dbms_output.put_line("ORA Error message " || err_msg);

dbms_output.put_line("Value of Num_a " || Num_a);

dbms_output.put_line("Value of Num_b " || Num_b);

SQL> set serveroutput on

SQL> DECLARE

2 num_a NUMBER:= 6;

3 num_b NUMBER;

4 BEGIN

5 num_b:= 0;

6 num_a:= num_a / num_b;

7 num_b:= 7;

8 dbms_output.put_line(" Value of num_b "|| num_b);

9 EXCEPTION

10 WHEN ZERO_DIVIDE

11 THEN

13 err_num NUMBER:= SQLCODE;

14 err_msg VARCHAR2(512) := SQLERRM;

15 BEGIN

16 dbms_output.put_line("ORA Error Number "|| err_num);

17 dbms_output.put_line("ORA Error message " || err_msg);

18 dbms_output.put_line("Value of num_a " || num_a);

19 dbms_output.put_line("Value of num_b " || num_b);

20 END;

21 END;

ORA Error Number -1476

ORA Error Message ORA-01476: divisor is equal to zero

Value of num_а 6

Value of num_b 0

PL/SQL procedure successfully completed.

Рис. 1. Использование SQLCODE и SQLERRM при обработке системных исключений

Рассмотрим конструкцию по отлову исключений(ошибок) в Oracle

when others then - отлавливает все ошибки, которые могут возникнуть, если до нее не отловили ранее

set serveroutput on;

declare
a INTEGER:= 1;
b INTEGER:= 0;
c CHAR(1) := "c";
begin
--Не вызовет ошибки
DBMS_OUTPUT.PUT_LINE(a / a);
--Вызовет деление на 0
--DBMS_OUTPUT.PUT_LINE(a / b);
--Вызовет все остальные ошибки
--DBMS_OUTPUT.PUT_LINE(a / c);
exception
when zero_divide then
DBMS_OUTPUT.PUT_LINE("zero_divide!");
when others then
DBMS_OUTPUT.PUT_LINE("when others then!");
end;

Перевели табличку с описанием ошибок: http://oracleplsql.ru/named-system-exceptions.html

/*
DUP_VAL_ON_INDEX Вы пытались выполнить операторы insert или update поля, изменение значения которого нарушит ограничение уникальности поля.
TIMEOUT_ON_RESOURCE Возбуждается при возникновении таймаута, когда ORACLE ожидает ресурса.
TRANSACTION_BACKED_OUT Откат удаленной части транзакции.
INVALID_CURSOR Вы пытаетесь сослаться на курсор, который еще не существует. Это могло произойти потому, что вы выполняете выборку (fetch) курсора, который был закрыт (close) или не был открыт (open).
NOT_LOGGED_ON Вы пытаетесь выполнить вызов в Oracle, не подключившись к Oracle.
LOGIN_DENIED Вы пытаетесь войти в Oracle с неверными имя пользователя / пароль.
NO_DATA_FOUND Вы пробовали один из следующих вариантов:
1. Вы выполнили SELECT INTO и запрос не вернул ни одной строки.
2. Вы ссылаетесь на неинициализированную строку в таблице.
3. Вы читаете после конца файла пакета UTL_FILE.
TOO_MANY_ROWS Вы пытались выполнить SELECT INTO и запрос вернул более одной строки.
ZERO_DIVIDE Вы пытались поделить число на ноль.
INVALID_NUMBER Вы пытаетесь выполнить оператор SQL который пытается преобразовать строку в число.
STORAGE_ERROR Вы исчерпали доступную память или память повреждена.
PROGRAM_ERROR Это общее сообщение Обратитесь в службу поддержки Oracle, возбуждается по причине обнаружения внутренней ошибки.
VALUE_ERROR Вы пытались выполнить операцию и была ошибка преобразования, усечения, или ограничения числовых или символьных данных.
CURSOR_ALREADY_OPEN Вы попытались открыть курсор, который уже открыт.
*/

Это учебное пособие объясняет, как использовать встроенные исключительные ситуации в Oracle/PLSQL c синтаксисом и примерами.

Описание

Встроенные исключительные ситуации это исключительные ситуации, которые имеют определенные имена в PL/SQL. Они определены в стандартном пакете в PL/SQL и не могут быть определены программистом.
Oracle имеет стандартный набор встроенных исключительных ситуаций:

Исключительные ситуации ORACLE	Ошибки Oracle	Пояснения
DUP_VAL_ON_INDEX		Вы пытались выполнить операторы insert или update поля, изменение значения которого нарушит ограничение уникальности поля.
TIMEOUT_ON_RESOURCE		Возбуждается при возникновении таймаута, когда ORACLE ожидает ресурса.
TRANSACTION_BACKED_OUT		Откат удаленной части транзакции.
INVALID_CURSOR		Вы пытаетесь сослаться на курсор, который еще не существует. Это могло произойти потому, что вы выполняете выборку (fetch) курсора, который был закрыт (close) или не был открыт (open).
NOT_LOGGED_ON		Вы пытаетесь выполнить вызов в Oracle, не подключившись к Oracle.
LOGIN_DENIED		Вы пытаетесь войти в Oracle с неверными имя пользователя / пароль.
NO_DATA_FOUND	ORA-01403	Вы пробовали один из следующих вариантов: 1. Вы выполнили SELECT INTO и запрос не вернул ни одной строки. 2. Вы ссылаетесь на неинициализированную строку в таблице. 3. Вы читаете после конца файла пакета UTL_FILE.
TOO_MANY_ROWS	ORA-01422	Вы пытались выполнить SELECT INTO и запрос вернул более одной строки.
ZERO_DIVIDE		Вы пытались поделить число на ноль.
INVALID_NUMBER		Вы пытаетесь выполнить оператор SQL который пытается преобразовать строку в число.
STORAGE_ERROR	ORA-06500	Вы исчерпали доступную память или память повреждена.
PROGRAM_ERROR	ORA-06501	Это общее сообщение Обратитесь в службу поддержки Oracle, возбуждается по причине обнаружения внутренней ошибки.
VALUE_ERROR	ORA-06502	Вы пытались выполнить операцию и была ошибка преобразования, усечения, или ограничения числовых или символьных данных.
CURSOR_ALREADY_OPEN	ORA-06511	Вы попытались открыть курсор, который уже открыт.

Синтаксис

Рассмотри синтаксис встроенных исключительных ситуаций в процедуре и функции.

Синтаксис для процедуры

CREATE PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
BEGIN
executable_sectionEXCEPTION
WHEN exception_name1 THEN

WHEN exception_name2 THEN

WHEN exception_name_n THEN

WHEN OTHERS THEN

END ;

Синтаксис для функции

CREATE FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
BEGIN
executable_sectionEXCEPTION
WHEN exception_name1 THEN

WHEN exception_name2 THEN

WHEN exception_name_n THEN

WHEN OTHERS THEN

END ;

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

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE add_new_supplier (supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2) IS BEGIN INSERT INTO suppliers (supplier_id, supplier_name) VALUES (supplier_id_in, supplier_name_in); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN raise_application_error (-20001,"You have tried to insert a duplicate supplier_id."); WHEN OTHERS THEN raise_application_error (-20002,"An error has occurred inserting a supplier."); END;

CREATE OR REPLACE PROCEDURE add_new_supplier

(supplier_id_inIN NUMBER ,supplier_name_inIN VARCHAR2 )

BEGIN

INSERT INTO suppliers(supplier_id,supplier_name)

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

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

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

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

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

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

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

EXCEPTION

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

операторы ;

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

операторы ;

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

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

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

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

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

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

Имя исключения	Номер ошибки	Описание
CURSOR_ALREADY_OPEN		Попытка открыть курсор, который уже открыт
DUP_VAL_ON_INDEX
		Попытка выполнить неразрешенную операцию с курсором (закрытие неоткрытого курсора)
		Попытка конвертировать символьное значение в численное в утверждении SQL, если символьное значение не представляет собой символьное представление числа
		Попытка соединиться с базой данных с неправильным именем пользователя и/или паролем
		Утверждение SELECT INTO не вернуло ни одной строки
		Попытка обратиться к базе данных, не соединившись с ней
		Внутренняя ошибка PL/SQL
		Недостаточно памяти
SYS_INVALID_ROWID		Попытка конвертировать символьное значение в ROWID, если символьное значение не представляет собой корректное символьное представление ROWID
TIMEOUT_ON_RESOURCE		Время ожидания ресурса истекло
		Утверждение SELECT INTO вернуло более одной строки
		Ошибка вычислений, преобразования типов, нарушение размерности
		Попытка деления на ноль

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

v_sal emp.sal%TYPE;

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

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

WHEN NO_DATA_FOUND THEN

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

WHEN TOO_MANY_ROWS THEN

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

WHEN OTHERS THEN

DBMS_OUTPUT.put_line("Other error occurred");

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

SQL> VARIABLE v_job VARCHAR2(20)

SQL> EXECUTE:v_job:= "President"

SQL> @d:\users\except

Salary of President is 5000