Новые возможности в динамическом 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-баз данных.