Почему я не могу использовать переменные связывания в операциях DDL/SCL в динамическом SQL?

Я пытаюсь выполнить команду SQL в динамическом SQL со связанными переменными:

-- this procedure is a part of PL/SQL package Test_Pkg
PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE
      'ALTER SESSION
      SET NLS_CALENDAR = :cal'
      USING IN calendar_;
END Set_Nls_Calendar;

Затем на стороне клиента я пытаюсь вызвать процедуру:

Test_Pkg.Set_Nls_Calendar('Thai Buddha');

Но это получи мне ORA-02248: invalid option for ALTER SESSION.

И мой вопрос: Почему я не могу использовать переменные связывания в операциях DDL/SCL в динамическом SQL?

Ответ 1

В операциях DDL переменные связывания не допускаются. Поэтому следующие утверждения вызовут ошибки:

Пример # 1: оператор DDL. Вызов ORA-01027: привязать переменные, не разрешенные для операций определения данных
```
EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT :def_val )'
  USING 42;
```
Пример # 2: оператор DDL. Вызывает ORA-00904:: недопустимый идентификатор
```
EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( :col_name NUMBER )'
  USING var_col_name;
```
Пример # 3: инструкция SCL. Вызывает ORA-02248: неверный параметр для ALTER SESSION
```
EXECUTE IMMEDIATE
  'ALTER SESSION SET NLS_CALENDAR = :cal'
  USING var_calendar_option;
```

Проблема

Чтобы понять, почему это происходит, нам нужно посмотреть Как обрабатываются динамические SQL-запросы.

Как правило, прикладная программа запрашивает у пользователя текст инструкции SQL и значения переменных хоста, используемых в инструкции. Затем Oracle анализирует инструкцию SQL. То есть Oracle анализирует инструкцию SQL, чтобы убедиться, что она соответствует правилам синтаксиса, а относится к действительным объектам базы данных. Анализ также включает проверку прав доступа к базе данных ¹ резервирование необходимых ресурсов и поиск оптимального пути доступа.

¹_{Акцент, добавленный ответчиком}

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

Пример # 1: Parser не может определить, будет ли значение привязки действительным. Что, если вместо USING 42 программист написал USING 'forty-two'?
Пример # 2: Parser не может определить, будет ли :col_name корректным имя столбца. Что, если имя связанного столбца было 'identifier_that_well_exceeds_thirty_character_identifier_limit'?
Пример # 3: значения для NLS_CALENDAR построены в константах (для данной версии Oracle?). Parser не может определить, будет ли связанная переменная иметь допустимое значение.

Итак, ответ заключается в том, что вы не можете связывать элементы схемы, такие как имена таблиц, имена столбцов в динамическом SQL. Вы также можете связывать встроенные константы.

Решение

Единственный способ добиться динамического связывания элементов/констант схемы - это использовать конкатенацию строк в динамических операциях SQL.

Пример # 1:

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT ' || to_char(42) || ')';

Пример # 2:

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table (' || var_col_name || ' NUMBER )';

Пример # 3:

EXECUTE IMMEDIATE
  'ALTER SESSION SET NLS_CALENDAR = ''' || var_calendar_option || '''';