Oracle Database 12.2 New Features content

Материалы нашего семинара по новым возможностям Oracle Database 12c Release 2 можно скачать по следующей ссылке: Oracle Database 12c Release 2

RACChecker - check application for RAC compatible

Тема разработки и адаптации приложений под RAC очень обширна и многогранна и затрагивает практически все аспекты создания прикладного ПО для СУБД Oracle Database. Но, тем не менее, для того чтобы гарантировать что ваше приложение корректно будет работать в RAC, необходимо убедиться, что оно не использует технологии и механизмы которые в RAC в не работают, либо работают с особенностями. Подробно требования к приложению описаны в документе "Oracle RAC Database aware Applications - Developer’s Checklist". Вот перечень этих технологий:

• каналы (пакет DBMS_PIPE) - не синхронизируются между узлами кластера;
  
  
• объекты БД типа DIRECTORY (каталоги в файловой системе) должны располагаться на разделяемом сторадже и должны быть видны всем узам кластера;
  
  
• вместо V$-представлений нужно использовать соответствующие GV$-представления;
  
  
• задания управляемые через пакет DBMS_JOB - крайне не рекомендуется использовать в RAC, поскольку задания этого пакета не поддерживают сервисы.
  
  
  Методы борьбы с вышеперечисленными технологиями мы подробно рассматривали на культовом семинаре "RAC Deep Dive for Developers"
  
  Довольно часто приложение имеет большие объемы PL/SQL-кода (сотни тысяч и даже миллионы строк кода), и вспомнить о том, в каком месте используется тот же DBMS_PIPE крайне сложно: код давно отлажен и работает, а его автор уже давно не работает в компании.
  Для того, что облегчить анализ серверной части кода (хранимых процедур PL/SQL) мною была разработана утилита RACChecker. Эта утилита поможет быстро ответить на вопрос: готово ли, в минимальной степени, ваше приложение при переходе в RAC.
  RACChecker анализирует ваш исходный код и находит объекты и строки кода, где вы используете технологии, которые в RAC не работают.
  
  

C:\RAC\Utils\RACChecker>RACChecker.exe help=y

RAC Checker: Release 12.2.0.1.0 - Production on 15.04.2015 11:07:13

Utility for check support Oracle DB for RAC and Exadata

Copyright (c) 2016 Igor Melnikov.  All rights reserved.

You can control how RACChecker runs by entering the RACChecker command followed
by various arguments. To specify parameters, you use keywords:

     Format:  RACChecker parameter=value TYPE=value

     Example: RACChecker USERID=scott/tiger@orcl TYPE=PIPE
              RACChecker USERID=demo/demo@demo   TYPE=ALL

Keyword          Description (Default)
--------------------------------------------------
SCHEMAS          schemas in which check ALL-for all schemas (ALL)
HELP             print this message: Y/N (N)
TYPE             object type: PIPE,JOB,ALL (ALL)
REPORT_FILE      file name for output report
PARFILE          parameter file name
SEQUENCES        Show NON-cached sequences (Y)
MIN_SEQ_CACHE    Check for sequences on minimum cache size (20)
SEQ_ORDERED      Check for sequences which are ordered (N)
SAVE_SOURCE      Save sources for "bad" objects (N)
DIR_SOURCE       Directory where sources will be save
SEQ_DDL_OPT      Generate sequence ddl-optimization for RAC: CACHE,ORDER,BOTH,NONE (NONE)
CHECK_DIRS       Check directory objects (for shared dir issue) (N)
CHECK_V$VIEWS    Check v$-views usage (Y)
USERID           Oracle connection string

Я думаю, что из списка параметров очевидно их назначение.
Следует обратить внимание лишь на следующие моменты:

• пользователь в строке подключения (параметр USERID) должен иметь права на чтение словаря (dictionary);
  
  
• утилита опционально может находить некэшируемые последовательности (с ними тоже возможны проблемы в RAC);
  
  
• возможна выгрузка DDL-скриптов для плохих объектов с помощью параметра SAVE_SOURCE=Y;
  
  
• для своей работы утилита требует установленной среды выполнения .NET Framework 4.5, а также ODP.NET Provider 12.1.0.2.4 - рекомендуется установить версию поставляемую с Instant Client - она небольшая по размеру.
  

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

Утилита может находить факт использования неработающих в RAC технологий (например: пакет DBMS_PIPE), даже если PL/SQL-код зашифрован (wrapped). Утилита на "лету" определяет что код зашифрован, и в этом случае анализирует не исходный код, а зависимости от пакетов dbms_pipe,dbms_job.
Утилита НЕ может обнаружить факт использования этих пакетов только в одном случае: если они используются через динамический PL/SQL (EXECUTE IMMEDIATE или DBMS_SQL) и код зашифрован.

Утилита определяет факт использования пакетов внутри однострочных коментариев. Это не приводит к ложному срабатыванию. Многострочные комментарии, к сожалению, не поддерживаются. При анализе последовательнойстей можно задать минимальный размер их кеша (параметр MIN_SEQ_CACHE). В этом случае будут выведены все последовательности, размер кэша которых меньше этого минимума.

За время своего существования утилита RACChecker постоянно развивалась, и помогла очень многим заказчикам перенести свои существующие приложения в среду Oracle Real Application Cluster.

C помощью RACChecker вы быстро определите проблемные места при переходе в RAC!

Ссылка для скачивания: RACChecker.

Real-Time Database Operation Monitoring в Oracle Database 12c

Технология мониторинга SQL-запросов в режиме реального времени (Real-Time SQL Monitoring ), которая появилась в Oracle Database 11g, позволяет администраторам и разработчикам взглянуть внутрь каждого долго выполняющего запроса.
Для каждого долго выполняющегося запроса можно увидеть его реальный план выполнения, количество обработанных строк, расхождение ожидаемой и фактической кардинальности (количество ожидаемых и количество фактических строк) на каждом шаге плана запроса. Также можно увидеть различные метрики SQL-запроса: CPU, I/O requests, throughput, PGA, temp space.
В том числе можно производить мониторинг статистики ввода-вывода для каждого шшага запроса.

В свое время технология Real-Time SQL Monitoring была большим шагом вперед для ответа на вопрос "Что реально происходит в базе данных?".

Рис 1. Мониторинг выполнения запроса с помощью Real-Time SQL Monitoring

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

Рассмотрим такой типичный случай.
При возникновении проблемы с производительностью расчета зарплаты, конечный пользователь обращается к администратору СУБД. DBA должен среды сотен (возможно и тысяч!) выполняющихся запросов, выделить запросы относящиеся к расчету зарплаты, выяснить какие из них сейчас выполняются и, затем, наконец, разобраться в причинах медленной работы этой бизнес-операции.
Для идентификации запросов часто приходится обращаться к разработчикам приложения и анализировать его исходный код. Все это часто приводит к взаимным обвинениям администраторов и разработчиков.
Не правда-ли: знакомая картина?

СУБД Oracle Database 12c предлагает решение и этой проблемы, позволяя производить мониторинг на уровне составных бизнес-операций.
Эта технология получила название Real-Time Database Operation Monitoring, и позволяет осуществлять мониторинг в режиме реального времени в терминах бизнес-процессов (бизнес-операций) выполняющихся в данный момент в СУБД.
Очевидно, что СУБД ничего "не знает" про бизнес-процессы, и для этого приложению необходимо каким-либо образом сообщить об этом базе данных: сгруппировать обращения к СУБД в бизнес-операции.

Для того, чтобы выделить в потоке обращений к СУБД бизнес-операцию, предлагается специальный API (Application Program Interface), реализованный в виде PL/SQL-пакета DBMS_SQL_MONITOR.
Пакет DBMS_SQL_MONITOR стандартно поставляется в составе СУБД и устанавливается по умолчанию - то есть входит в состав стандартных системных пакетов Oracle Database 12с.
Для выделения начала бизнес-операции предназначена процедура BEGIN_OPERATION данного пакета, для окончания бизнес-операции - END_OPERATION.

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

 CREATE PROCEDURE calc_salary(v_pDep IN NUMBER) IS
   v_xDBOpId          NUMBER;
 BEGIN
   v_xDBOpId := dbms_sql_monitor.begin_operation('Расчет зарплаты',
                                                 forced_tracking => 'Y');
   prepare_calc();
   main_calc();
   finish_calc();

   dbms_sql_monitor.end_operation('Расчет зарплаты', v_xDBOpId); 
 END;

Параметр forced_tracking процедуры BEGIN_OPERATION аналогичен хинту MONITOR в запросе: при его установке (forced_tracking => 'Y'), мониторинг бизнес-операции производится в любом случае. В противном случае - мониторинг производится только в случае, если бизнес-операции по продолжительности выполнения занимает 5 и более секунд ЦПУ или ввода-вывода. Запустим наш модифицированный бизнес-процесс расчета зарплаты и посмотрим, как он теперь отображается на экране SQL Monitoring консоли администрирования Enterprise Manager 12c. В списке выполняющихся запросов появился запрос c "говорящим" именем "Расчет зарплаты":

Рис 2. "Расчет зарплаты" в списке запросов на экране SQL Monitoring
Перейдя внутрь выполняющейся бизнес-операции - для этого нужно кликнуть мышью на SQL_ID операции (в нашем случае: "Расчет зарплаты"), можно увидеть страницу детальной информации: какие запросы из состава бизнес-операции уже выполнились, и какой запрос является текущим - выполняется в данный момент:

Рис 3. Детальная информация о выполнении бизнес-операции
"Провалившись" внутрь любого запроса в бизнес-операции, можно увидеть знакомый по предыдущим версиям экран SQL-мониторинга запросов.

После завершения бизнес-операции входящие в него запросы точно также доступы для анализа, как это и было раньше (в СУБД Oracle Database 11g) для уже завершившихся SQL-запросов.

Рис 4. Бизнес-операция "Расчет зарплаты" завершена

Для того, чтобы сгруппировать запросы в бизнес-операции из приложений Java и С++/C-приложений, соответствующий клиентский API доступа к БД (JDBC и OCI, соответственно) также был расширен.
Вот так, например, выглядит выделение бизнес-операции в Java-приложении:

Connection conn = DriverManager.getConnection(myUrl, myUsername, myPassword);
conn.setClientInfo("E2E_CONTEXT.DBOP", “Расчет зарплаты");
Statement stmt = conn.createStatement();
stmt.execute(v_xSQLQuery1);
... ... ... ...
... ... ... ...

conn.setClientInfo("E2E_CONTEXT.DBOP", null);

Если Вы разрабатываете приложение сразу для разных версий СУБД Oracle Database, то можете воспользоваться условной компиляцией PL/SQL, чтобы иметь один и тотже код в независимости от версии БД:

 create procedure calc_salary(v_pDepartmentId in number) is
    v_xDBOpId          NUMBER;
  begin
    $if dbms_db_version.ver_le_11_2 $then

    $else
      v_xDBOpId := dbms_sql_monitor.begin_operation('Расчет зарплаты',
                                                    forced_tracking => 'Y');
    $end
  
    v_gCurrentDepartmentId := v_pDepartmentId;
  
    prepare_calc();
    main_calc();
    finish_calc();
    
    $if dbms_db_version.ver_le_11_2 $then
    $else 
      dbms_sql_monitor.end_operation('Расчет зарплаты', v_xDBOpId);  
    $end
  end;

Объявление переменной v_xDBOpId нет смысла обрамлять в символ условной компиляции,поскольку при компиляции в СУБД Oracle Database 11g, PL/SQL-оптимизатор удалит её из исходного кода, поскольку она является "мертвой" - нигде не используется.

Продолжение следует ...

Новые возможности в динамическом SQL в Oracle 12c (передача переменных PL/SQL типов)

И, наконец, последняя новая возможность в динамическом SQL, которая появилась в Oracle Database 12c.

Последняя, не по значимости, а по порядку изложения!
Стоит отметить, что она имеет очень большую практическую ценность, - и, по большому счету, ее очень не хватало.

Существенным ограничением до Oracle Database 12c являлась невозможность передачи в динамический код переменных, которые имеют PL/SQL-типы.
Если рассмотреть нижеприведенный анонимный блок, выполненный в среде Oracle Database 11g (11.2.0.4):

[oracle@localhost]$ sqlplus rscott/rtiger

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 3 00:07:27 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DECLARE
  b BOOLEAN;
BEGIN
  EXECUTE IMMEDIATE
    'begin :x := true; end;'
  USING
    OUT b;
END;
/
    OUT b;
        *
ERROR at line 7:
ORA-06550: line 7, column 9:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

SQL> 

можно увидеть, что ошибка при передаче в динамический блок кода возникает даже когда переменная типа BOOLEAN передается именно в PL/SQL-блок.

Очевидно, что исполняющая среда PL/SQL предполагает, что исполняться будет именно SQL-выражение.

Для того, чтобы обойти это ограничение, приходилось применять вот примерно такой дорогостоящий workaround, связанный с преобразованиями значения из PL/SQL-типа в SQL-тип, и затем обратно:

SQL> create function CharToBoolean(v_pValue in char) return boolean is
begin
  if v_pValue = 'Y' then
    return true;
  end if;

  return false;
end;
/

Function created.

SQL> create function BooleanToChar(v_pValue in boolean) return char is
begin
  if v_pValue then
    return 'Y';
  end if;

  return 'N';
end;
/

Function created.

SQL> DECLARE
  b BOOLEAN;
  s char(1);
BEGIN
  EXECUTE IMMEDIATE
    'begin :x := BooleanToChar(true); end;'
  USING
    OUT s;

  b := CharToBoolean(s);
END;
/
PL/SQL procedure successfully completed.

SQL> 

Конечно-же, это усложняло понимание кода и дальнейшее его сопровождение.

Наконец-то, это ограничение снято в Oracle Database 12c, и приведеный выше первоначальный код теперь выполняется без ошибок:

[oracle@localhost Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 3 00:10:28 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> DECLARE
  b BOOLEAN;
BEGIN
  EXECUTE IMMEDIATE
    'begin :x := true; end;'
  USING
    OUT b;
END;
/

PL/SQL procedure successfully completed.

SQL> 

Причем теперь, стало возможным передавать в динамический блок кода не только переменные типа BOOLEAN, но также PL/SQL-записи и PL/SQL-массивы:

SQL> create or replace package Account_service is
  type
    TArrayOfVarchar2 is table of Varchar2(32) index by pls_integer;

end;
/
Package created.

set serveroutput on
DECLARE
  v_xArray Account_service.TArrayOfVarchar2;
BEGIN
  v_xArray(1) := 'First'; v_xArray(2) := 'Second';

  EXECUTE IMMEDIATE
    'begin
       for v_xIndex in 1..:v_xCount
       loop
         dbms_output.put_line(:v_pArray(v_xIndex));
       end loop;
     end;'
  USING
    IN v_xArray.Count,v_xArray;
END;
/

First
Second

PL/SQL procedure successfully completed.
 
SQL>

Также поддерживается ситуация, когда передача параметра, имеющего PL/SQL, производится в SQL-выражение:

SQL> create or replace function printArray(v_pArray in Account_service.TArrayOfVarchar2) return number is
begin
  for v_xIndex in 1..v_pArray.count
  loop
    dbms_output.put_line(v_pArray(v_xIndex));
  end loop;

  return 1;
end;
/

Function created.

SQL>DECLARE
  v_xArray Account_service.TArrayOfVarchar2;
  n        number; 
BEGIN
  v_xArray(1) := 'First'; v_xArray(2) := 'Second';

  EXECUTE IMMEDIATE
    'SELECT printArray(:v_pArray) FROM dual'
  INTO 
    n
  USING
    IN v_xArray;
END;
/

First
Second

PL/SQL procedure successfully completed.

SQL>

К сожалению, хэш-массивы (PL/SQL-массивы c INDEX BY VARCHAR2) пока не поддерживаются.

И необходимо помнить, что динамический блок кода не является замыканием (closure), то есть используемый тип должен быть определен на уровне всей схемы - в спецификации пакета.

Поддержка передачи (и возвращения тоже!) в динамический SQL переменных имеющих PL/SQL-типы - это новая впечатляющая возможность Oracle Database 12c, которая позволяет упростить Ваш код, тем самым сделать его более читабельным и легким в сопровождении.

Новые возможности в динамическом SQL в Oracle 12c (возврат "неявного" курсора)

В некоторых СУБД есть возможность вернуть из хранимой функции непосредственно набор данных. Например, в СУБД MS SQL Server возможен такой код хранимой процедуры на T-SQL:

CREATE PROCEDURE my_proc1
AS
BEGIN
 SELECT * FROM accounts_amounts WHERE Amount >= 20000
END

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

Как вы хорошо знаете, такой возможности в СУБД Oracle Database нет: хранимая процедура не может вернуть просто запрос. Необходим возврат ссылки на курсор (ref cursor): через OUT-переменную, либо как результат возвращаемый хранимой функцией.

Например:

CREATE OR REPLACE PACKAGE my_types IS
  TYPE
   Taccounts_amounts_cur is ref cursor;
END;
/

CREATE PROCEDURE my_proc1(v_xRes in out my_types.Taccounts_amounts_cur) IS
BEGIN
 OPEN v_xRes FOR 
   SELECT * FROM accounts_amounts WHERE Amount >= 20000;
END;
/

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

В СУБД Oracle Database 12c такая возможность появилась!
Это, конечно, потребовало адаптации клиентских библиотек доступа в Oracle Client. Начиная с Oracle Database Client 12c приложения, которые используют любой интерфейс доступа к СУБД (Oracle Call Interface, ODP.Net, JDBC), могут получать из СУБД такие "неявные" курсоры. Собственно на стороне СУБД, чтобы вернуть такой курсор, необходимо использовать новую процедуру RETURN_RESULT из пакета DBMS_SQL.

Эта процедура перегружена, и присутствует в двух вариантах:

• для возврата слаботипизированной ссылки на курсор -
  PROCEDURE RETURN_RESULT(rc IN OUT SYS_REFCURSOR, to_client IN BOOLEAN DEFAULT TRUE);
• для возврата курсора отрытого с помощью динамического SQL в пакете DBMS_SQL -
  PROCEDURE RETURN_RESULT(rc IN OUT INTEGER, to_client IN BOOLEAN DEFAULT TRUE);

Параметр to_client определяет возможность возвращения курсора непосредственно сразу клиентcкому приложению, либо в другую хранимую процедуру PL/SQL. Хранимая процедура может вернуть таким образом не один курсор, а несколько - может вызвать процедуру RETURN_RESULT несколько раз. Определим хранимую процедуру, которая возвращает курсор подобным образом в клиентское приложение:

SQL> create or replace procedure test_dyn3 is
  v_xCursor    integer;
  v_xRowCount  integer;
begin
  v_xCursor := dbms_sql.open_cursor;

  dbms_sql.parse(c                  => v_xCursor,
                 statement          => 'select * from dual',
                 language_flag      => DBMS_SQL.NATIVE);

  v_xRowCount := dbms_sql.execute(c => v_xCursor);

  dbms_sql.return_result(v_xCursor,true);
end;
/

Procedure created.

Попробуем вызвать нашу процедуру в среде утилиты SQL*Plus:

SQL> exec test_dyn3;

PL/SQL procedure successfully completed.

ResultSet #1

D
-
X

SQL>

Как Вы можете убедиться: утилита SQL*Plus версии 12с "понимает" такой вид курсора, и просто выводит его содержимое на свою консоль.
Эта возможность появилась в версии 12с этой утилиты. Если курсор возвращается таким образом из динамического SQL, то чтобы его получить внутри вызываемого блока PL/SQL, предназначена еще одна новая процедура пакета DBMS_SQL - GET_NEXT_RESULT. Эта процедура также перегружена и имеет два варианта:

• для получения слаботипизированной ссылки на курсор -
  PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT SYS_REFCURSOR);
• для получения курсора отрытого с помощью динамического SQL в пакете DBMS_SQL -
  PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT INTEGER);;

Для того чтобы, иметь возможность получения таких "неявных" курсоров, вызов процедуры OPEN_CURSOR должен быть выполнен с новым параметром treat_as_client_for_results равным значению TRUE, по умолчанию значение этого параметра равно FALSE. Немного перепишем нашу процедуру test_dyn3 указав в функции возврата курсора значение параметра to_client равным FALSE:

SQL> create or replace procedure test_dyn3 is
  v_xCursor    integer;
  v_xRowCount  integer;
begin
  v_xCursor := dbms_sql.open_cursor;

  dbms_sql.parse(c                  => v_xCursor,
                 statement          => 'select * from dual',
                 language_flag      => DBMS_SQL.NATIVE);

  v_xRowCount := dbms_sql.execute(c => v_xCursor);

  dbms_sql.return_result(v_xCursor,false);
end;
/

Procedure created.

Теперь вызовем нашу процедуру через динамический SQL, и попробуем получить и вывести на экран содержимое курсора:

SQL> DECLARE
  v_xCursor          integer;
  v_xRowCount        integer;

  v_xReturnCursor    integer;
  v_xResult          dual.dummy%type;
  v_xReturnRefCursor sys_refcursor;
BEGIN
  v_xCursor := dbms_sql.open_cursor(treat_as_client_for_results => true);

  dbms_sql.parse(v_xCursor, 'BEGIN test_dyn3(); END;', dbms_sql.native);

  v_xRowCount := DBMS_SQL.EXECUTE(v_xCursor);
 
  dbms_sql.get_next_result(v_xCursor, v_xReturnCursor);

--Для удобства превращаем полученный курсор в ссылку на курсор (sys_refcursor)
  v_xReturnRefCursor := dbms_sql.to_refcursor(v_xReturnCursor);

  FETCH v_xReturnRefCursor 
    INTO v_xResult;
 
  dbms_output.put_line('Result: ' || v_xResult);
 
  dbms_sql.close_cursor(v_xCursor);
END;
/
Result: X

PL/SQL procedure successfully completed.

SQL>

Мы рассмотрели еще одну новую возможность Oracle Database 12c в области динамического SQL - возможность непосредственного возврата курсоров из хранимых процедур.
На мой взгляд, эта новая возможность в основном востребована при миграции приложений с других СУБД, которые уже поддерживают эту технологию, на СУБД Oracle Database. Поскольку в этом случае значительно снижается объем работ по адаптации кода приложения под СУБД Oracle.

Новые возможности в динамическом SQL в Oracle 12c (определение схемы по умолчанию)

Следующая новая возможность в динамическом SQL тоже связана с процедурой PARSE пакета DBMS_SQL.
До Oracle Database 12c, если в тексте запроса или анонимного PL/SQL-блока в имени объекта не была явна указана схема, то будет использована текущая схема.
Для программных единиц PL/SQL (пакетов, процедур, функций и т.д.) созданных с правами создателя, это будет схема в которой происходила компиляция, для созданных с правами вызывающего - схема пользователя, под которым происходит вызов динамического SQL.

Начиная с Oracle Database 12c, можно четко контролировать схему в имени объекта, для которых она была не указана в динамическом SQL:

[oracle@rac1 ~]$ sqlplus rscott/rtiger

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 9 16:24:34 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management options

SQL> create or replace package body account_service as

  procedure deleteAccount(v_pId in number) is
    v_xCursor    integer;
    v_xRowCount  integer;
    v_xPLSQL_Str varchar2(1600);
  begin
    v_xPLSQL_Str := 'delete from accounts where id = :v_pId';

    v_xCursor := dbms_sql.open_cursor;
  
    dbms_sql.parse(c                  => v_xCursor,
                   statement          => v_xPLSQL_Str,
                   language_flag      => DBMS_SQL.NATIVE,
                   schema             => 'DATA_OWNER');
    dbms_sql.bind_variable(v_xCursor, ':v_pId', v_pId);       

    v_xRowCount := dbms_sql.execute(c => v_xCursor);

    dbms_sql.close_cursor(c => v_xCursor);    
  end;

end;
/

Package body created.

Важно отметить, что параметр SCHEMA в процедуре PARSE отвечает лишь за имя схемы, в которой происходит поиск объекта при парсинге. Выполнение динамического SQL происходит с правами той программной единицы PL/SQL, в которой происходит вызов пакета DBMS_SQL - с правами создателя, либо с правами вызывающего.

Новые возможности в динамическом SQL в Oracle 12c (выполнение кода в PDB)

Говоря о новых возможностях Oracle Database 12c, нельзя не упомянуть динамический SQL.
В этой области тоже появилось много новых и интересных возможностей.

  1. Выполнение кода в контексте произвольной PDB-базы
Безусловно, самой главной новой технологией, которая пришла вместе с Oracle 12c, являются мультиарендная архитектура баз данных (Multitenant database).
Поддержка контейнерной архитектуры не могла пройти мимо динамического SQL: в пакете DBMS_SQL появилась возможность выполнить произвольный код в контексте указанной подключаемой базы данных (PDB). В процедуре PARSE появился новый параметр CONTAINER, который указывает PDB-базу, в которой нужно выполнить соответствующий динамический SQL или PL/SQL-код:

  dbms_sql.parse(c                  => v_xCursor,
                 statement          => v_xPLSQL_Str,
                 language_flag      => DBMS_SQL.NATIVE,
                 container          => 'CRM');

В вышеприведенном примере, код содержащийся в переменной v_xPLSQL_Str будет выполнен в PDB-базе CRM.
Важно отметить следующее: такой динамический код может быть выполнен только когда текущая сессия находится в БД-контейнере (CDB$ROOT), и пользователь должен иметь права на доступ к соответствующей PDB, то есть должен быть common-пользователем.

Создадим common-пользователя и дадим ему доступ к всем PDB в контейнере:

[oracle@dbim cdb1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 2 07:04:01 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE USER global$rscott1
   IDENTIFIED BY rtiger container=all;

User created.

SQL> grant connect, resource to global$rscott container=all;

Grant succeeded.

Для имени common-пользователя был использован нестандартный префикс (GLOBAL$, а не C##), поскольку параметр common_user_prefix был переопределен:

SQL> show parameter common

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix       string  global$

Возможность переопределения префикса в именах common-пользователей появилась в патчсете 12.1.0.2.

Предположим, что в контейнере находятся три PDB-базы: ERM, CRM и DW. В каждой из этих БД есть процедура создания учетной записи клиента определенная в пакете account_service:

create or replace package account_service as

  v_gLastAuditMessage varchar2(128);

  procedure newAccount(v_pName        in varchar2,
                       v_pDescription in varchar2);

end;

И после создание учетной записи клиента происходит сохранение информации аудита в глобальной переменной v_gLastAuditMessage:

create or replace package body account_service as

  procedure newAccount(v_pName        in varchar2,
                       v_pDescription in varchar2) is
  begin
    --код специфичный для каждой PDB 
    ...  ...  ...  ...  ...  ...  

    v_gLastAuditMessage := 'Account "' || v_pName || '" created in pdb ' || sys_context('USERENV', 'CON_NAME');
  end;
  
end;

В root-контейнере мы можем создать процедуру создания учетных записей клиентов сразу в нескольких PDB, при этом реально в каждой PDB-базе будет выполняться именно свой прикладной код.

Рис.1 Инкапсуляция прикладной логики в cdb$root c помощью динамического SQL
Исходный код, иллюстрирующий вышеописанный подход, может выглядеть следующим образом:

[oracle@dbim cdb1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 2 07:45:16 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>conn global$rscott/rtiger
Connected.

SQL> --in Root container:

SQL> create or replace type TArrayOfString as table of varchar(32);
/

Type created.

SQL> create or replace package account_service as

  procedure newAccount(v_pName        in varchar2,
                       v_pDescription in varchar2,
                       v_pContainers  in TArrayOfString);

end;
/

Package created.


--Бизнес-логика выполняется в каждой PDB:
SQL> create or replace package body account_service as

  procedure newAccount(v_pName        in varchar2,
                       v_pDescription in varchar2,
                       v_pContainers  in TArrayOfString) is
    v_xCursor    integer;
    v_xRowCount  integer;
    v_xPLSQL_Str varchar2(1600);
  begin
    v_xPLSQL_Str := 'begin account_service.newAccount(:v_pName, :v_pDescription); end;';

    v_xCursor := dbms_sql.open_cursor;
  
    for v_xContainerIndex in 1..v_pContainers.count
    loop
      dbms_sql.parse(c                  => v_xCursor,
                     statement          => v_xPLSQL_Str,
                     language_flag      => DBMS_SQL.NATIVE,
                     container          => v_pContainers(v_xContainerIndex));

      dbms_sql.bind_variable(v_xCursor, ':v_pName', v_pName);       
      dbms_sql.bind_variable(v_xCursor, ':v_pDescription', v_pDescription);

      v_xRowCount := dbms_sql.execute(c => v_xCursor);
    end loop;

    dbms_sql.close_cursor(c => v_xCursor);    
  end;

end;
/
Package body created.

Попробуем создать учетные записи клиентов сразу в трех подключаемых БД в контейнере:

SQL> exec account_service.newAccount('Scott Tiger','Scott Tiger demo',new TArrayOfString('ERP','CRM','DW'));

PL/SQL procedure successfully completed.

Теперь переключимся в конкретную PDB-бд и проверим контекст сессии (состояние переменной v_gLastAuditMessage):

SQL>  alter session set container=ERP; 

Session altered.

SQL>  set serveroutput on 
SQL>  exec dbms_output.put_line(account_service.v_gLastAuditMessage); 
Account "Scott Tiger" created in pdb ERP

PL/SQL procedure successfully completed.

SQL> alter session set container=CRM;

Session altered.

SQL>  set serveroutput on 
Account "Scott Tiger" created in pdb CRM

PL/SQL procedure successfully completed.

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

В Oracle 12c динамический SQL позволяет на уровне контейнера CDB$Root проводить своеобразную "оркестровку" вашего прикладного кода реализованного внутри PDB-баз данных.