Особенности обработки ошибок сервера базы данных 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. Ошибки, вызываемые ограничениями внешних ключей

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

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

   ORA-02291: нарушено ограничение целостности (<Схема>.<Внешний ключ>) 
   - исходный ключ не найден

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

   ORA-02292: нарушено ограничение целостности (<Схема>.<Внешний ключ>) 
   - обнаружена порожденная запись

3. В главной таблице выполняется попытка удаления данных, на которые имеется ссылка в подчиненной таблице. Если в определении связи между таблицами указано ограничение “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. Вывод специального сообщения об ошибке уровня приложения. Сначала программа выполняет поиск сообщения об ошибке среди специальных сообщений для данного приложения. Если такое сообщение найдено, оно выводится, и формирование сообщения на этом завершается.
2. Вывод специального сообщения об ошибке уровня базы данных. Если на этапе 1 сообщение не было найдено, выполняется поиск специального сообщения об ошибке уровня базы данных. Если найдено, то оно выводится пользователю и формирование сообщения об ошибке на этом заканчивается.
3. Вывод сообщения на основе анализа структуры базы данных (универсального сообщения). В случае, если на предыдущих этапах специальных сообщений не обнаружено, то оно формируется на основе анализа структуры базы данных. Оно выводится пользователю и на этом формирование сообщения завершается.
4. Вывод сообщения от сервера базы данных. В случае, если на трех предыдущих этапах сообщение для пользователя не было сформировано, то отображается сообщение об ошибке от 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 для конечного пользователя. Хотя за рамками статьи остались некоторые моменты реализации, хочется надеяться, что описанный в статье подход позволит уменьшить трудозатраты при разработке программного обеспечения, повысить его надежность и качество.

Литература

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