Базы данных -> Oracle -> Заморочки от Oracle, или знать бы, где упасть

Заморочки от Oracle, или знать бы, где упасть

... "Да вот веревкой хочу море морщить
Да вас, проклятое племя, корчить".

А. С. Пушкин,
Сказка о попе и его работнике Балде

Введение

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

Эта заметка продолжает серию статей о возможностях Oracle, но выбор самих возможностей необычен. Речь пойдет о неудобных или даже неприятных возможностях - хотя бы и потенциально неприятных. Признаться, некоторые колебания в целесообразности подобной публикации у меня были, но ведь многие читатели доверяют СУБД Oracle самое святое - бизнес, и поэтому знать, откуда ждать беду они просто обязаны. В более безобидном варианте знание некоторых "заморочек от Oracle" способно улучшить ваше понимание этой системы.

Рекурсивные вызовы

PL/SQL в Oracle относится к языкам, в которых рекурсивные вызовы подпрограмм разрешены. В теле одной подпрограммы можно обратиться к самой себе, или же, например, к другой, а та, в свою очередь к первой. Рекурсия иногда удобна. Пример из учебников - вычисление факториала. Более жизненный пример - размечивание весами древовидной структуры, позволяющее организовать быстрый доступ к хранимому в БД справочнику без привлечения нестандартных и несовместимых конструкций (в Oracle это конструкция CONNECT BY).

Особенность в том, что машина PL/SQL в СУБД Oracle никак не регламентирует глубину повторных обращений, что оставляет лазейку "бесконечного" зацикливания. Ответственность за его возникновение СУБД перекладывает на программистов. Хуже того, организовать такое зацикливание может любой пользователь, обладающий всего только-то привилегией CREATE SESSION:

SQL> CONNECT / AS SYSDBA
Connected.
SQL> CREATE USER adam IDENTIFIED BY eva;

User created.

SQL> GRANT CREATE SESSION TO adam;

Grant succeeded.

SQL> CONNECT adam/eva
Connected.

Прежде чем двигаться дальше, удостоверьтесь, что вы готовы перезагрузить свою БД.

SQL> DECLARE PROCEDURE a IS BEGIN a; END; BEGIN a; END;
/

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

Во-первых, сеанс пользователя ADAM начинает жадно расходовать процессорное время. Другие сеансы связи с СУБД это сразу почувствуют.

Во-вторых, сеанс пользователя ADAM начинает неумолимо захватывать оперативную память во все больших и больших количествах. Пусть не сразу, но другие сеансы связи с СУБД почувствуют и это.

Если ваша база загружена какой-то реальной работой, проделанная простая операция - эффективный способ дезорганизовать эту работу. У меня ни разу не хватило терпения дождаться, когда Oracle исчерпает всю оперативную память, и проверить его способность самому переварить проблему. Но если не препринять заблаговременных мер по нейтрализации подобных диверсий, даже естественная попытка убить вредоносный сеанс не будет простой. Сначала из-за страшно медленной реакции СУБД на ваши действия от имени SYS, а потом из-за страшно медленного освобождения памяти после команды ALTER SYSTEM KILL SESSION.

Вопрос, стоило ли разработчикам Oracle оставлять возможность неограниченной рекурсии, способен разжечь спор. Но сделано то, что сделано: Oracle награждает нас здесь одновременно со свободой действий и риском потери нормальной работы СУБД.

Как сделать функцию невидимой

Следующий пример показывает архитектурное упущение создателей Oracle. Оно обязано одному из недальновидных решений, принятых в первых версиях Oracle, очевидно в угоду скорости утверждения на рынке. Позже, подобно некоторым другим подобным в этом отношении "ранним" решениям Oracle, исправить положение дел уже не представилось возможным.

К счастью, проблема неожидаемой маскировки объекта в БД, о которой пойдет речь, довольно специфична. Однако ж она вполне реальна.

Рассмотрим пример функции в схеме SCOTT:

SQL> CREATE FUNCTION ename RETURN VARCHAR2 AS 
  2  BEGIN RETURN 'I am a function'; END;
  3  /

Function created.

Это не запрещено, несмотря на наличия поля ENAME в одной из таблиц схемы, так как пространства имен функций и столбцов не пересекаются (и правильно !). Но что произойдет, если к ней обратиться при выборке данных из таблицы EMP ? Если выполнить следующий запрос, что мы увидим: столбец или функцию ?

SELECT ename FROM emp;

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

Закономерно возникает вопрос: а как при обращении к EMP выдать значение функции ENAME ? Несложные эксперименты приводят к необходимости указать полное имя функции, снабдив его именем схемы:

SELECT scott.ename, ename FROM emp;

Или же (что в некоторых смыслах более правильно)

SELECT scott.ename, emp.ename FROM emp;

Крайне неудачная система обозначений (в SCOTT.ENAME SCOTT - это имя схемы, а в такой же по виду записи EMP.ENAME EMP - это имя таблицы), но свое дело она делает. К сожалению, этим дело не кончается. Пространства имен схем и пакетов тоже разные, что тоже правильно. Рассмотрим теперь следующее:

CREATE OR REPLACE PACKAGE scott IS FUNCTION ename
 RETURN varchar2; END scott;
/

CREATE OR REPLACE PACKAGE BODY scott IS
   FUNCTION ename RETURN varchar2 IS 
    BEGIN RETURN 'I am a package function'; 
    END ename;
END scott;
/

SELECT scott.ename FROM emp;

Убедитесь, что получим 'I am a package function'. Но теперь, обращаясь к EMP мы никогда не увидим результат самостоятельной (не пакетированной) функции ENAME ! Если в вашем приложении были запросы типа SELECT scott.ename FROM emp, то после создания пакета они начнут выдавать попросту другой ответ.

Способ формирования составных имен не идеален и в других языках. Например, даже в таком архитектурно продуманном языке, как Java, понять по тексту смысл каждой компоненты в имени java.lang.System.out.println без дополнительной информации невозможно. Но другой системы, кроме Oracle, где допускалось бы исчезновение видимости имени одного объекта вследствие вполне законного заведения других, мне неизвестно.

Имена объектов, ключевые и зарезервированные слова

История этой задачки началась с форума Oracle на http://www.sql.ru/, куда я однажды случайно забрел. Один из участников форума пытался перенести в Oracle схему БД, подготовленную в другой системе. В таблице было поле, названное NUMBER. Очевидно, в нем хранилось какое-то число; с этим-то полем в Oracle и случилась незадача. Ее раскрывает следующий (уже мой) пример:

SQL> CREATE TABLE t(number NUMBER);
CREATE TABLE t(number NUMBER)
               *
ERROR at line 1:
ORA-00904: : invalid identifier

Автор вопроса правильно сообразил, что NUMBER - это зарезервированное в Oracle слово (мы помним, что перед обработкой предложения SQL Oracle повышает регистр букв в именах объектов), и ошибка возникает при синтаксическом разборе. В документации про ошибку ORA-00904 сказано: "… [column name] may not be a reserved word". Раз уж говорим о заморочках, обратите внимание на текст сообщения: между двумя двоеточиями по замыслу разработчиков должно стоять то самое неправильное имя, а в нашем случае оно оказалось настолько неправильным, что не попало в результат синтаксического анализатора ! Не самый большой грех в Oracle, но ведь по теме статьи.

Неужели перенося базу в Oracle придется править схему, а значит и приложение ? Если не выход, то лазейку из положения автор вопроса в форум нашел правильно. Заключив имя объекта в Oracle в двойные кавычки мы приобретем право использовать абсолютно любые доступные в кодировке символы, хоть звездочки, хоть точки, хоть пробелы:

SQL> CREATE TABLE t(" ./()*" NUMBER, "NUMBER" NUMBER);

Table created.

SQL>  DESCRIBE t
Name                Null?    Type
------------------- -------- ----------------------
 ./()*                       NUMBER 
NUMBER                       NUMBER

SQL> ALTER TABLE t DROP COLUMN " ./()*";

Table altered.

SQL>  DESCRIBE t
Name                Null?    Type
------------------- -------- ----------------------
NUMBER                       NUMBER

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

SQL> INSERT INTO t VALUES (123);

1 row created.

SQL> UPDATE t SET "NUMBER" = "NUMBER";

1 row updated.

Беда в том, что точно та же команда UPDATE, равно как и любая другая, обращающаяся к столбцу "NUMBER", не работает в PL/SQL:

SQL> BEGIN UPDATE t SET "NUMBER" = "NUMBER"; END;
  2  /
BEGIN UPDATE t SET "NUMBER" = "NUMBER"; END;
             *
ERROR at line 1:
ORA-06550: line 1, column 53:
PL/SQL: ORA-06552: PL/SQL: Compilation unit
 analysis terminated
ORA-06553: PLS-320: the declaration of the type
 of this expression is incomplete or malformed
ORA-06550: line 1, column 25:
PL/SQL: SQL Statement ignored

Это и вызвало недоумение автора вопроса в форум. В Oracle уверяют, что начиная с версии 9 (которая помогала готовить эту статью) обработка запросов в SQL и в PL/SQL ведется одним и тем же модулем СУБД. И тем не менее факт налицо: SQL терпит обращение к полю "NUMBER", а PL/SQL - нет.

Заинтересовавшись, после серии экспериментов я нащупал очередную уступку Oracle, позволившую достичь компромисса, но сообщить найденное решение автору вопроса уже не смог, так как sql.ru оказался для меня слишком сложно устроен: он не только требует регистрации (а придуманные себе кличку и пароль я назавтра же забыл), но и содержит чересчур много "нитей", среди которых "свою" я безнадежно потерял.

Вот что выяснилось: PL/SQL начинает все обрабатывать правильно, как только в названии столбца появляется хотя бы одна малая буква, например, не "NUMBER", а "NuMBER". Лучше, конечно, "number". Возникает вопрос: в чем причина такого странного поведения ?

Переписка с разработчиками PL/SQL несколько прояснила ситуацию. Можно посочувствовать: им приходится расплачиваться по счетам самой логики жизни. В любом языке есть множество зарезервированных слов, которые нельзя использовать для имен объектов. В Oracle первую очередь это ключевые слова: BEGIN, SELECT и другие. Заметьте кстати, что множества зарезервированных слов Oracle в SQL и PL/SQL не совпадают:

SQL> CREATE TABLE t1 (begin NUMBER);

Table created.

SQL> UPDATE t1 SET begin = 1;

0 rows updated.

SQL> BEGIN UPDATE t1 SET begin = 2; END;
  2  /

PL/SQL procedure successfully completed.

SQL> DECLARE begin NUMBER; BEGIN NULL; END;
  2  /
DECLARE begin NUMBER; BEGIN NULL; END;
                                     *
ERROR at line 1:
ORA-06550: line 1, column 38:
PLS-00103: Encountered the symbol "end-of-file"
 when expecting one of the following:
... ... ... ...

(Но, конечно же:

SQL> DECLARE "begin" NUMBER; BEGIN NULL; END;
  2  /

PL/SQL procedure successfully completed.

Однако жизнь бывает сложнее, чем иногда хотелось бы. Вопреки утверждению документации по Oracle (сказали разработчики PL/SQL) не все ключевые слова являются зарезервированными. Вызвано это тем, что язык живет, и в нем могут появляться новые ключевые слова, которые кто-то, когда они еще не были ключевыми, мог использовать для названий объектов. Запрет вдруг их такового употребления способен был бы вызвать возмущение пользователей, принужденных переделывать имеющиеся БД и приложения. Отсюда и отклонения от идеального равенства "ключевые слова" = "зарезервированные слова". Нагладный пример: до версии 9 в Oracle не было типа TIMESTAMP, и многие использовали это слово для именования поля в таблице. В версии 9 эту вольность (по законам новой версии) употребления пришлось оставить, хотя и удалось это не совсем последовательно:

SQL> ALTER TABLE t1 ADD (timestamp TIMESTAMP);

Table altered.

SQL> UPDATE t1 SET timestamp = SYSTIMESTAMP;

SQL> DECLARE t TIMESTAMP; BEGIN t := SYSTIMESTAMP; END;
  2  /

PL/SQL procedure successfully completed.

SQL> DECLARE timestamp NUMBER; BEGIN timestamp := 1; END;
  2  /

PL/SQL procedure successfully completed.

но, правда

SQL> DECLARE timestamp TIMESTAMP; BEGIN timestamp := NULL; END;
  2  /
DECLARE timestamp TIMESTAMP; BEGIN timestamp := NULL; END;
                  *
ERROR at line 1:
... ... ... ...

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

SQL> DECLARE "timestamp" TIMESTAMP; BEGIN "timestamp" := NULL; END;
  2  /

PL/SQL procedure successfully completed.