Использование DBMS_SQL вместо динамического PL/SQL

DBMS SQL или SQL NDSЕсли перед вами станет вопрос, какую из описанных технологий следует выбрать, начать следует со встроенного динамического SQL (вместо DBMS_SQL), поскольку он гораздо проще в применении, а программный код получается более коротким и обычно содержит меньшее количество ошибок. Кроме того, такой код проще в сопровождении, а обычно и выполняется более эффективно.

И все же в некоторых ситуациях приходится использовать пакет DBMS_SQL. Эти ситуации описаны ниже.



 

Получение информации о столбцах запроса

Пакет DMBS_SQL позволяет описывать столбцы динамического курсора, возвращая ин­формацию о каждом столбце в ассоциативном массиве записей. Перед разработчиком открывается возможность написания универсального кода работы с курсорами; она особенно полезна, если вы пишете динамический SQL категории 4 и не уверены, сколько именно столбцов задействовано в выборке.

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

DECLARE
   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   cols DBMS_SQL.DESC_TAB;
   ncols PLS_INTEGER;
BEGIN
   -- Разбор запроса 
   DBMS_SQL.PARSE (cur, 'SELECT hire_date, salary FROM employees', DBMS_SQL.NATIVE);
   -- Получение информации о столбцах
   DBMS_SQL.DESCRIBE_COLUMNS (cur, ncols, cols);
   -- Вывод каждого из имен столбцов
   FOR colind IN 1 .. ncols
   LOOP
      DBMS_OUTPUT.PUT_LINE (cols (colind).col_name);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR (cur);
END;

Чтобы упростить использование DESCRIBE_C0LUMNS, я создал пакет, который скрывает большинство технических подробностей и упрощает использование этой функциональ­ности. Спецификация пакета выглядит так:

PACKAGE desccols 
IS
   FUNCTION for_query (sql_in IN VARCHAR2)
      RETURN DBMS_SQL.desc_tab;
   FUNCTION for_cursor (cur IN PLS_INTEGER)
      RETURN DBMS_SQL.desc_tab;
   PROCEDURE show_columns (
      col_list_in IN DBMS_SQL.desc_tab
   );
END desccols;

Функция for_query используется в том случае, если вы хотите получить информацию о столбцах динамического запроса, но в остальном не собираетесь использовать DBMS_SQL. Использование пакета продемонстрировано в следующем сценарии:

DECLARE
   cur INTEGER	:= DBMS_SQL.open_cursor;
   tab DBMS_SQL.desc_tab;
BEGIN
   DBMS_SQL.parse (cur
      , 'SELECT last_name, salary, hiredate FROM employees'
      , DBMS_SQL.native );
   tab := desccols.for_cursor (cur); 
   desccols.show (tab);
   DBMS_SQL.close_cursor (cur);
   tab := desccols.for_query ('SELECT * FROM employees');
   desccols.show (tab);
END;
/

 

 

Поддержка требований категории 4

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

Когда вы сталкиваетесь с категорией 4? Конечно, при построении интерфейсной части для поддержки несистематизированных запросов, генерируемых пользователями, или при построении обобщенной программы построения отчетов, которая динамически строит формат отчета и содержимое во время выполнения. Разберем одну из вариаций на эту тему: построение процедуры PL/SQL для вывода содержимого таблицы — любой таблицы, заданной пользователем во время выполнения. Здесь мы ограничимся рас­смотрением аспектов, относящихся непосредственно к динамическому SQL; за полной реализацией обращайтесь к файлу intab.sp на сайте книги.

 

Процедурный интерфейс

В своей реализации я буду использовать PL/SQL и DBMS_SQL. Но прежде чем браться за написание кода, необходимо создать спецификацию. Как будет вызываться эта процедура? Какую информацию должен предоставить пользователь (в данном случае разработчик)? Что должен ввести пользователь для получения желаемого вывода? В следующей таблице перечислены входные данные моей процедуры intab (сокращение от «in table»).

Параметр Описание
Имя таблицы Обязательно (главная информация, используемая программой)
Секция WHERE Не обязательно. Позволяет ограничить строки, возвращаемые запрос. Если условие
не задано, возвращаются все строки. Параметр также может использоваться для
передачи условий ORDER BY и HAVING, следующих непосредственно за WHERE
Фильтр имен
столбцов
Не обязательно. Если вы не хотите выводить все столбцы таблицы, передайте список, разделенный запятыми; в этом случае будут использоваться только указанные
вами столбцы

 

С этими данными спецификация моей процедуры принимает следующий вид:

PROCEDURE intab (
   table_in IN VARCHAR2 , 
   where_in IN VARCHAR2 DEFAULT NULL ,
   colname_like_in IN VARCHAR2 := '%'
);

Ниже приводятся примеры вызовов intab с результатами. Начнем с вывода всего со­держимого таблицы emp:

SQL> EXEC intab ('emp');

примеры вызовов intab с результатами

 


А теперь перейдем к работникам отдела 10, ограничивая максимальную длину текстовых столбцов 20 символами:

SQL> EXEC intab	('emp',	'deptno = 10 ORDER BY	sal');

работникам отдела 10

 

Переходим к совершенно другой таблице с другим количеством столбцов:

SQL> EXEC intab ('dept')
_ Contents of dept
------------------------------------
DEPTNO DNAME LOC
------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


Обратите внимание: пользователю не приходится передавать информацию о структуре

таблицы. Моя программа получает эту информацию самостоятельно — собственно, имен­но эта особенность процедуры intab делает ее примером динамического SQL категории 4.

 

Последовательность построения intab

Вывод содержимого произвольной таблицы происходит по следующей схеме:

  1. Построение и разбор команды select (с использованием open_cursor и parse).
  2. Связывание всех локальных переменных с их «заместителями» в запросе (с ис­пользованием bind_variable).
  3. Определение каждого курсора для этого запроса (с использованием define_ column).
  4. Выполнение и выборка строк базы данных (с использованием execute и fetch_ rows).
  5. Получение значений из выбранной строки таблицы и их включение в выводимый текст (с использованием column_value). Затем строка выводится выводом проце­дуры put_line пакета dbms_output.

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

 

Построение SELECT

Чтобы извлечь данные из таблицы, необходимо построить команду SELECT. Структура этого запроса определяется различными входными данными процедуры (имя таблицы, условие WHERE и т. д.) и содержимым словаря данных. Помните, что пользователь не должен предоставлять список столбцов — вместо этого я должен идентифицировать столбцы таблицы и извлечь список из представления словаря данных. Я решил ис­пользовать представление ALL_TAB_COLUMNS процедуры intab, чтобы пользователь мог просматривать содержимое не только таблиц, владельцем которых он является (доступных в user_tab_columns), но и любых таблиц, для которых он обладает доступом select. Для получения информации о столбцах таблицы используется следующий курсор:

CURSOR col_cur
   (owner_in IN VARCHAR2,
    table_in IN VARCHAR2)
IS
   SELECT column_name, data_type,
         data_length,
         data_precision, data_scale
   FROM all_tab_columns
   WHERE owner = owner_in
     AND table_name = table_in;

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

  •  Чтобы ответить на этот вопрос, необходимо подумать над тем, как эти данные будут использоваться. Как выясняется, у них есть много разных вариантов использова­ния — например:
  •  Чтобы построить список выборки для запроса, я буду использовать имена столбцов.
  •  Чтобы вывести содержимое таблицы в удобном виде, необходимо вывести над данными заголовки столбцов, которые должны быть размещены по горизонтали в правильных позициях. Следовательно, для каждого столбца необходимо знать имя и длину данных.
  •  Чтобы осуществить выборку данных в динамическом курсоре, необходимо опреде­лить столбцы курсора вызовами DEFINE_COLUMN. Для этого нужно знать типы и длины данных.
  •  Чтобы извлечь данные из выбранной строки с использованием COLUMN_VALUE, я дол­жен знать типы данных всех столбцов, а также их количество.
  •  Чтобы вывести данные, я должен построить строку со всем данными (преобразуя числа и даты функцией TO_CHAR). Данные необходимо дополнить по ширине для размещения под заголовками столбцов, как это было сделано в строке заголовка. Моя программа многократно использует информацию столбцов, однако повторных обращений к словарю данных хотелось бы избежать. Соответственно, при получении данных столбцов из представления ALL_TAB_COLUMNS я сохраню эти данные в трех разных коллекциях PL/SQL, описанных в следующей таблице.

 

Коллекция Описание
colname Имена столбцов
coltype Типы данных столбцов (строки с описаниями типов)
collen Количество символов, необходимых для вывода данных столбца

 

Таким образом, для третьего столбца таблицы emp элемент colname(3) содержит строку «SAL», элемент coltype(3) содержит строку «NUMBER», а элемент collen(3) равен 7 и т. д.

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

Вся логика применяется в цикле FOR с курсором, который перебирает все столбцы табли­цы (в соответствии с определением ALL_COLUMNS). Этот цикл (приведенный в следующем примере) заполняет мою коллекцию PL/SQL:

FOR col_rec IN col_cur (owner_nm, table_nm)
LOOP
   /* Построение списка выборки для запроса. */
   col_list := col_list || ', ' || col_rec.column_name;
   /* Сохранение типа данных и длины для вызовов DEFINE_COLUMN. */
   col_count := col_count + 1;
   colname (col_count) := col_rec.column_name;
   coltype (col_count) := col_rec.data_type;

   /* Построение строки заголовка. */
   col_header :=
      col_header || ' ' || RPAD (col_rec.column_name, v_length);
END LOOP;

После завершения цикла список выборки построен, коллекции PL/SQL заполнены информацией о столбцах, необходимой для вызовов DBMS_SQL . DEFINE_COLUMN и DBMS_SQL . COLUMN_VALUE, а также создана строка заголовка. Неплохо для одного цикла!

Теперь нужно разобрать запрос и построить разные столбцы объекта динамического курсора.

 

Определение структуры курсора

Фаза разбора достаточно тривиальна. Я просто собираю команду SQL из обработанных компонентов — прежде всего, из только что построенного списка столбцов (переменная col_list):

DBMS_SQL.PARSE
   (cur,
   'SELECT ' || col_list ||
   ' FROM ' || table_in || ' ' || where_clause,
   DBMS_SQL.NATIVE);

Я не собираюсь ограничиться разбором — курсор нужно выполнить. Но перед этим не­обходимо его структурировать. В DBMS_SQL при открытии курсора вы всего лишь полу­чаете дескриптор для блока памяти. При разборе команды SQL эта команда связывается с упомянутой памятью. Однако необходимо сделать следующий шаг — определить столбцы в курсоре, чтобы он мог использоваться для хранения полученных данных. Для динамического SQL категории 4 этот процесс достаточно сложен. Я не могу жестко запрограммировать ни количество, ни типы вызовов DBMS_SQL.DEFINE_COLUMN в своей программе; часть информации появляется лишь на стадии выполнения. К счастью, в случае intab имеются коллекции с данными обо всех столбцах. Остается лишь создать вызов DBMS_SQL.DEFINE_COLUMN для каждого столбца, определенного в коллекции colname. Но прежде чем переходить к коду, стоит сказать пару слов о DBMS_SQL.DEFINE_COLUMN. Заголовок этой встроенной процедуры выглядит так:

PROCEDURE DBMS_SQL.DEFINE_COLUMN
   (cursor_handle IN INTEGER,
   position IN INTEGER,
   datatype_in IN DATE|NUMBER|VARCHAR2)

При ее использовании следует учитывать три обстоятельства:

  •  Во втором аргументе передается число. DBMS_SQL.DEFINE_COLUMN работает не с имена­ми столбцов, а с последовательными позициями столбцов в списке.
  •  Третий аргумент задает тип данных столбца курсора. В нем передается выражение соответствующего типа. Иначе говоря, DBMS_SQL.DEFINE_COLUMN передается не строка (скажем, «VARCHAR2»), а переменная, определенная с типом VARCHAR2.
  •  При определении столбца символьного типа необходимо задать максимальную длину значений, которые могут загружаться в курсор.

В контексте процедуры intab строка коллекции соответствует N-й позиции списка столбцов. Тип данных хранится в коллекции coltype, но его необходимо преобразовать в вызов DBMS_SQL.DEFINE_COLUMN с использованием соответствующей локальной пере­менной. Все это происходит в следующем цикле FOR:

FOR col_ind IN 1 .. col_count
LOOP
   IF is_string (col_ind)
   THEN
      DBMS_SQL.DEFINE_COLUMN
      (cur, col_ind, string_value, collen (col_ind));

   ELSIF is_number (col_ind)
   THEN
      DBMS_SQL.DEFINE_COLUMN (cur, col_ind, number_value);
   ELSIF is_date (col_ind)
   THEN
      DBMS_SQL.DEFINE_COLUMN (cur, col_ind, date_value);
   END IF;
END LOOP;

После завершения цикла будут выполнены вызовы DEFINE_C0LUMN для каждого столб­ца, определенного в коллекциях. (В моей версии это все столбцы таблицы. В своей усовершенствованной реализации вы можете ограничиться подмножеством этих столбцов.) Затем я выполняю курсор и перехожу к выборке записей. Фаза исполне­ния для категории 4 не отличается от других, более простых категорий. Используйте вызов вида:

fdbk := DBMS_SQL.EXECUTE (cur);

где fdbk — объект обратной связи, возвращаемый при вызове EXECUTE.

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

 

Выборка и отображение данных

Я использую цикл FOR с курсором для получения каждой строки данных, идентифи­цированной моим динамическим курсором. Если текущей является первая строка, я вывожу заголовок (тем самым предотвращается вывод заголовка для запроса, не возвращающего данных). Для каждой полученной строки данных я генерирую строку результата и вывожу ее:

LOOP
   fdbk := DBMS_SQL.FETCH_ROWS (cur);
   EXIT WHEN fdbk = 0;
   IF DBMS_SQL.LAST_ROW_COUNT = 1
   THEN
      /* Здесь выводится информация заголовка */
   END IF;
   /* Построение текста с информацией столбцов */
   DBMS_OUTPUT.PUT_LINE (col_line);
END LOOP;

Текст с выводимыми значениями строится в цикле FOR. Встроенная функция DBMS_SQL. COLUMN_VALUE вызывается для каждого столбца в таблице (информация о котором хранится в моих коллекциях). Как видно из листинга, я использую функции is_* для определения типа столбца, а следовательно, и выбора переменной, которая получит значение.

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

col_line := NULL;
FOR col_ind IN 1 .. col_count
LOOP
   IF is_string (col_ind)
   THEN
      DBMS_SQL.COLUMN_VALUE (cur, col_ind, string_value);
   ELSIF is_number (col_ind)
   THEN
      DBMS_SQL.COLUMN_VALUE (cur, col_ind, number_value);
      string_value := TO_CHAR (number_value);
   ELSIF is_date (col_ind)
   THEN
      DBMS_SQL.COLUMN_VALUE (cur, col_ind, date_value);
      string_value := TO_CHAR (date_value, date_format_in);
   END IF;
   /* Значение дополняется пробелами в строке
   под заголовками столбцов. */
   col_line :=
      col_line || ' ' ||
      RPAD (NVL (string_value, ' '), collen (col_ind));
END LOOP;

Итак, в вашем распоряжении появляется универсальная процедура для вывода содержи­мого таблиц базы данных из программ PL/SQL. И снова за подробностями обращайтесь к файлу intab.sp; файл intab_dbms_sql.sp содержит версию этой процедуры, адаптированную для новых возможностей баз данных и более полно документированную.

 

Минимальный разбор динамических курсоров

Один из недостатков EXECUTE IMMEDIATE заключается в том, что при каждом выполне­нии динамическая строка обрабатывается заново, что обычно подразумевает разбор, оптимизацию и построение плана выполнения. Для большинства ситуаций, требующих применения динамического SQL, затраты на эти действия компенсируются другими преимуществами NDS (в частности, предотвращением вызовов PL/SQL API, необхо­димых для DBMS_SQL). Однако в некоторых случаях разбор обходится слишком дорого, и тогда DMBS_SQL может оказаться лучшим решением.

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

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

1 DECLARE
2   l_cursor pls_INTEGER;
3   l_result pls_INTEGER;
4 BEGIN
5   FOR i IN 1 .. counter
6   LOOP
7      l_cursor := DBMS_SQL.open_cursor;
8      DBMS_SQL.parse
9         (l_cursor, 'SELECT ... where col = ' || i , DBMS_SQL.native);
10     l_result := DBMS_SQL.EXECUTE (l_cursor);
11     DBMS_SQL.close_cursor (l_cursor);
12  END LOOP;
13 END;

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

 

Строки Описание
7 Получение курсора — обычного указателя на память, используемую DBMS_SQL
8-9 Разбор динамического курсора после присоединения единственного изменяемого
элемента (переменная i)
10 Выполнение запроса
11 Закрытие курсора

 

Все происходящее вполне допустимо (и конечно, обычно за выполнением запроса сле­дуют операции выборки), однако в целом DBMS_SQL используется неверно. Рассмотрим следующую модификацию тех же действий:

DECLARE
   l_cursor PLS_INTEGER;
   l_result PLS_INTEGER;
BEGIN
   l_cursor := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (l_cursor, 'SELECT ... WHERE col = :value'
      , DBMS_SQL.native);
   FOR i IN 1 .. counter
   LOOP
      DBMS_SQL.bind_variable (l_cursor, 'value', i);
      l_result := DBMS_SQL.EXECUTE (l_cursor);
   END LOOP;
   DBMS_SQL.close_cursor (l_cursor);
END;

В этом варианте использования DBMS_SQL я объявляю курсор только один раз, потому что могу использовать его с каждым вызовом DBMS_SQL.PARSE. Я также перемещаю вызов разбора за пределы курсора. Поскольку структура команды SQL остается неизменной, курсор не нужно разбирать заново для каждого значения i. Итак, я выполняю разбор только один раз, после чего в цикле подставляю в курсор новое значение переменной и выполняю курсор. Когда все будет сделано (после завершения цикла), курсор закрывается. Возможность явного и раздельного выполнения каждого шага предоставляет разработ­чику невероятную гибкость (а заодно создает проблемы со сложностью кода DBMS_SQL). Если вы стремитесь именно к этому, с DBMS_SQL трудно соперничать.

Если вы используете DBMS_SQL в своих приложениях, я рекомендую воспользоваться пакетом из файла dynalloc.pkg на сайте книги. Этот пакет помогает:

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

 

 

Новые возможности Oracle11g

В Oracle11g появились средства взаимодействия между встроенным динамическим SQL и DBMS_SQL: теперь вы можете пользоваться преимуществами обеих технологий, чтобы совместить оптимальное быстродействие с простейшей реализацией. А если говорить конкретно, появилась возможность преобразования курсоров DBMS_SQL в курсорные переменные, и наоборот.

 

Функция DBMS_SQL.TO_REFCURSOR

Функция DBMS_SQL.TO_REFCURSOR преобразует номер курсора (полученный вызовом DBMS_SQL.OPEN_CURSOR) в слаботипизованную курсорную переменную (объявленную с типом SYS_REFCURSOR или слабым пользовательским типом REF CURSOR). Далее вы мо­жете извлекать данные из курсорной переменной в локальные переменные или даже передать курсорную переменную во внешнюю среду для выборки данных.

Прежде чем передавать курсор SQL функции DBMS_SQL.TO_REFCURSOR, необходимо вы­звать для него OPEN, PARSE и EXECUTE; в противном случае произойдет ошибка. После того как курсор будет преобразован, вы уже не сможете работать с ним средствами DBMS_SQL, в том числе и закрывать его. Все операции должны выполняться только через курсорную переменную.

Зачем нужна эта функция? Как упоминалось ранее, DBMS_SQL иногда оказывается предпо­чтительным или единственным решением для некоторых операций динамического SQL. Допустим, мы знаем, какие конкретные столбцы следует выбрать, но предложение WHERE запроса содержит неизвестное (на стадии компиляции) количество формальных параме­тров, что не позволяет использовать EXECUTE IMMEDIATE с фиксированной секцией USING. Реализация могла бы базироваться на DBMS_SQL от начала до конца, но использование DBMS_SQL для выборки строк и значений отдельных полей требует слишком большого объ­ема работы. Гораздо проще было бы использовать традиционную статическую выборку и даже BULK COLLECT. Именно такое решение продемонстрировано в следующем примере:

DECLARE
   TYPE strings_t IS TABLE OF VARCHAR2 (200);

   l_cv	sys_refcursor;
   l_placeholders  strings_t	:= strings_t ('dept_id');
   l_values	strings_t	:= strings_t ('20');
   l_names	        strings_t;

   FUNCTION employee_names (
        where_in	        IN	VARCHAR2
      ,	bind_variables_ in	IN	strings_t
      ,	placeholders_in	        IN	strings_t
   )
      RETURN sys_refcursor
   IS
      l_dyn_cursor NUMBER;
      l_cv	     sys_refcursor;
      l_dummy	     PLS_INTEGER;

   BEGIN

      /* Разбор курсора для получения фамилий после присоединения
      секции WHERE.
      ВНИМАНИЕ: если вам когда-либо потребуется писать подобный код,
      ОБЯЗАТЕЛЬНО примите меры по снижению риска внедрения SQL.
      */

      l_dyn_cursor := DBMS_SQL.open_cursor;
      DBMS_SQL.parse (l_dyn_cursor
         , 'SELECT last_name FROM employees WHERE ' || where_in 
         , DBMS_SQL.native 
      );

      /*
      Каждая переменная связывается с именованным формальным
      параметром; при переменном количестве формальных параметров
      выполнить этот шаг посредством EXECUTE IMMEDIATE не удастся!
      */

      FOR indx IN 1 .. placeholders_in.COUNT
      LOOP
         DBMS_SQL.bind_variable (l_dyn_cursor
            , placeholders_in (indx)
            , bind_variables_in (indx)
                              );
      END LOOP;

      /* 
      Выполнение запроса 
      */

      l_dummy := DBMS_SQL.EXECUTE (l_dyn_cursor);

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

      l_cv := DBMS_SQL.to_refcursor (l_dyn_cursor);

      /*
      Курсор не должен закрываться средствами DBMS_SQL; все операции
      с курсором на этой стадии выполняются ТОЛЬКО через
      курсорную переменную.
      */

      DBMS_SQL.close_cursor (l_dyn_cursor);

      RETURN l_cv;
   END employee_names;
BEGIN
   l_cv := employee_names ('DEPARTMENT_ID = :dept_id', l_values, l_placeholders);
   FETCH l_cv BULK COLLECT INTO l_names;
   CLOSE l_cv;
   FOR indx IN 1 .. l_names.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_names(indx));
   END LOOP;
END;
/

Другой пример ситуации, в которой может пригодиться эта функция: допустим, в про­грамме выполняется динамический SQL, требующий применения DBMS_SQL, но результат должен быть передан клиенту промежуточного уровня (например, приложению Java или .NET). Передать курсор DBMS_SQL невозможно, но ничто не мешает вернуть курсорную переменную.

 

 

Функция DBMS_SQL.TO_CURSOR

Функция DBMS_SQL.TO_CURSOR преобразует переменную REF CURSOR (сильно- или слаботи­пизованную) в номер курсора SQL, который затем может передаваться подпрограммам DBMS_SQL. Курсорная переменная должна быть открыта до ее передачи функции DBMS_SQL.TO_CURSOR.

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

Эта функция будет удобна в том случае, если количество подставляемых в команду SQL переменных известно во время компиляции, но вы не знаете количество элементов, участвующих в выборке (очередной пример динамического SQL категории 4!). Следующий пример демонстрирует ее практическое применение:

PROCEDURE show_data (
   column_list_    in VARCHAR2
   , department_id_in IN employees.department_id%TYPE
)
IS
   sql_stmt CLOB;
   src_cur SYS_REFCURSOR;
   curid NUMBER;
   desctab DBMS_SQL.desc_tab;
   colcnt NUMBER;
   namevar VARCHAR2 (50);
   numvar NUMBER;
   datevar DATE;
   empno NUMBER := 100;
BEGIN
   /* Конструирование запроса с внедрением списка выбираемых столбцов
   и одной подставляемой переменной.
   ВНИМАНИЕ: подобная конкатенация создает угрозу внедрения SQL!
   */
   sql_stmt :=
      'SELECT '
      || column_list_in
      || ' FROM employees WHERE department_id = :dept_id';
   /* Открытие курсорной переменной с привязкой единственного значения.
   Реализуется НАМНОГО проще, чем с применением DBMS_SQL!
   */
   OPEN src_cur FOR sql_stmt USING department_id_in;
   /*
   Однако курсорная переменная не может использоваться для выборки данных, потому что
   количество выбираемых элементов неизвестно во время компиляции. С другой стороны,
   проблема идеально решается средствами DBMS_SQL и процедуры DESCRIBE_COLUMNS, поэтому
   курсорная переменная преобразуется в курсор DBMS_SQL, после чего выполняются 
   необходимые действия.
   */
   curid := DBMS_SQL.to_cursor_number (src_cur);
   DBMS_SQL.describe_columns (curid, colcnt, desctab);
   FOR indx IN 1 .. colcnt 
   LOOP
      IF desctab (indx).col_type = 2
      THEN
         DBMS_SQL.define_column (curid, indx, numvar);
      ELSIF desctab (indx).col_type = 12
      THEN
         DBMS_SQL.define_column (curid, indx, datevar);
      ELSE
         DBMS_SQL.define_column (curid, indx, namevar, 100);
      END IF;
   END LOOP;
   WHILE DBMS_SQL.fetch_rows (curid) > 0
   LOOP
      FOR indx IN 1 .. colcnt
      LOOP
         DBMS_OUTPUT.put_line (desctab (indx).col_name || ' = ');
         IF (desctab (indx).col_type = 2)
         THEN
            DBMS_SQL.COLUMN_VALUE (curid, indx, namevar);
            DBMS_OUTPUT.put_line	('	'	||	namevar);
         ELSIF (desctab (indx).col_type = 12)
         THEN
            DBMS_SQL.COLUMN_VALUE (curid, indx, numvar);
            DBMS_OUTPUT.put_line	('	'	||	datevar);
         ELSE /* Предполагается строка.	*/
            DBMS_SQL.COLUMN_VALUE (curid, indx, namevar);
            DBMS_OUTPUT.put_line	('	'	||	namevar);
         ELSEND IF;
      END LOOP;
   END LOOP;
   DBMS_SQL.close_cursor (curid);
END;

 

Усовершенствованная модель безопасности DBMS_SQL

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

В Oraclell для защиты от атак такого рода в пакете DBMS_SQL были введены три до­полнительные меры безопасности:

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

 

Непредсказуемые номера курсоров

До выхода Oracle Database 11g, вызовы DBMS_SQL.OPEN_CURSOR возвращали последовательно увеличивающиеся числа (обычно из диапазона от 1 до 300). Такая предсказуемость могла позволить атакующему перебрать целые числа и проверить, не являются ли они номерами действительных, открытых курсоров. Обнаруженный курсор мог использоваться для атаки.

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

SQL>BEGIN
2      FOR indx IN	1 .. 5
3      LOOP
4         DBMS_OUTPUT.put_line (DBMS_SQL.open_cursor ());
5      END LOOP;
6   END;
7   /

1693551900
1514010786
1570905132
182110745
1684406543

 

Отказ в доступе к DBMS_SQL при использовании недопустимого номера курсора (ORA-24971)

Чтобы защититься от подбора действительных курсоров атакующим, Oracle теперь немедленно отказывает в доступе к пакету DBMS_SQL при попытке использования не­действительного курсора.

Рассмотрим следующий блок:

DECLARE
   l_cursor NUMBER;
   l_feedback NUMBER;
   PROCEDURE set_salary 
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Set salary = salary...');
      l_cursor := DBMS_SQL.open_cursor ();
      DBMS_SQL.parse (l_cursor
         , 'update employees set salary = salary'
         , DBMS_SQL.native );
      l_feedback := DBMS_SQL.EXECUTE (l_cursor);
      DBMS_OUTPUT.put_line (' Rows modified = ' || l_feedback);
      DBMS_SQL.close_cursor (l_cursor);
   END set_salary;

BEGIN
   set_salary ();
   BEGIN
      l_feedback := DBMS_SQL.EXECUTE (1010101010);
   EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack ());
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace ());
   END;
   set_salary ();
EXCEPTION
   WHEN OTHERS 
   THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack ());
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace ());
END;

Здесь выполняется действительная команда UPDATE, которая «обновляет» salary теку­щим значением для всех строк таблицы employees в локальной процедуре set_salary. Я вызываю эту процедуру, затем пытаюсь выполнить недействительный курсор, после чего снова вызываю set_salary. Результаты выполнения этого блока:

Set salary = salary...
   Rows modified = 106

ORA-29471: DBMS_SQL access denied 
ORA-06512: at "SYS.DBMS_SQL", line 1501 
ORA-06512: at line 22 
Set salary = salary...
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 980
ORA-06512: at line 9 
ORA-06512: at line 30

Процедура set_salary сработала в первый раз, но после попытки выполнения недей­ствительного курсора при повторном запуске set_salary было инициировано исклю­чение ORA-29471. Собственно, любая попытка вызова программы DBMS_SQL приведет к выдаче этой ошибки.

Восстановить доступ к DBMS_SQL можно только одним способом: выходом и повторным подключением. Да, жестко! Но это вполне разумно с учетом потенциально опасной ситуации, приведшей к ошибке.

База данных также блокирует доступ к DBMS_SQL, если программа, в которой был открыт курсор, инициировала исключение (которое может быть не связано с динамическим SQL). Если ошибка «поглощается» программой (то есть не инициируется заново), определить ее причину будет нелегко.

 

Запрет операций DBMS_SQL при изменении действующего пользователя (ORA-24970)

Oracle Database 11g предоставляет новую перегруженную версию функции OPEN_CURSOR с аргументом:

DBMS_SQL.OPEN_CURSOR (уровень_безопасности IN INTEGER) RETURN INTEGER;

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

  •  0 — отключение проверок безопасности для операций DBMS_SQL с курсором. Это озна­чает, что вы можете выполнить выборку из курсора, а затем выполнить подстановки и заново выполнить курсор с другим фактическим идентификатором пользователя
  • или ролью (отличными от действовавших на момент первого разбора). Этот уровень безопасности не действует по умолчанию.
  •  1 — фактические идентификаторы пользователя и роли вызывающей стороны DBMS_SQL для подстановки и выполнения операций с курсором должны совпадать с тем, которые действовали для вызывающей стороны при последней операции разбора с этим курсором.
  •  2 — фактические идентификаторы пользователя и роли вызывающей стороны DBMS_ SQL для всех операций подстановки, выполнения, определения, описания и выборки с курсором должны совпадать с тем, которые действовали для вызывающей стороны при последней операции разбора с этим курсором.

Пример возможных ошибок, обусловленных новыми проверками безопасности Oracle:

  1. Создайте процедуру user_cursor в схеме hr, как показано ниже. Обратите внима­ние: эта программа выполняется с правами создателя; это означает, что при вызове этой программы другой схемой текущим или фактическим пользователем ста­новится hr. Откройте курсор и разберите запрос для all_source с этим курсором. Затем верните номер курсора dbms_sql в аргументе out:
PROCEDURE user_cursor (
   security_level_in	IN	PLS_INTEGER
   , cursor_out	        IN      OUT NUMBER
)
AUTHID DEFINER 
IS
BEGIN
   cursor_out := DBMS_SQL.open_cursor (security_level_in);
   DBMS_SQL.parse (cursor_out
      , 'select count(*) from all_source'
      , DBMS_SQL.native );
END;

       2. Предоставьте разрешение на запуск программы пользователю SCOTT:

GRANT EXECUTE ON use_cursor TO scott

        3. Подключитесь с правами scott. Запустите программу use_cursor из схемы hr с безопасностью уровня 2 и получите динамический курсор SQL. Наконец, по­пытайтесь выполнить этот курсор из схемы SCOTT:

SQL> DECLARE
2	l_cursor	NUMBER;
3	l_feedback	number;
4	BEGIN
5	hr.use_cursor (2, l_cursor);
6	l_feedback	:= DBMS_SQL.execute_and_fetch (l_cursor);
7	END;
8	/
DECLARE
*
ERROR at line 1:
ORA-29470: Effective userid or roles are not the same as when cursor was parsed 
ORA-06512: at "SYS.DBMS_SQL", line 1513 
ORA-06512: at line 6

Oracle выдает ошибку ORA-29470, потому что курсор был открыт и разобран в схеме hr (из-за присутствия authid definer), но выполнялся в схеме SCOTT.

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

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3107 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6991 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5731 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 10835 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Войдите чтобы комментировать

apv аватар
apv ответил в теме #9220 16 сен 2018 14:33
Да, DBMS_SQL порой приходится использовать, и эти ситуации хорошо показаны в Вашей статье. Спасибо.