Главная

Oracle 12c. Продолжаем обзоры новых возможностей. SQL Text expansion

Только что обнаружил у Т. Кайта в блоге описание очень полезной, на мой взгляд, новой возможности 12-ки.
Назвается SQL Text expansion. По сути она позволяет получить конечный запрос, после всех преобразований и дополнений.

1) будут раскрыты все подлежащие view. Т.е. скажем у вас, на первый взгляд, простой запрос, из одного представления, но если вы копнёте глубже, то обнаружите, что внутри это представление состоит из сложного запроса, а тот, основан на других view, которые, в свою очередь, на ещё множестве представлений. Наверняка многие из нас с этим сталкивались и знают каких усилий стоит собрать в голове всю картину воедино, бегая по описаниям каждого представления и пытаясь представить как всё это собирается в один запрос…

2) Особых усилий при отладке логики и производительности стоит работа с virtual private database (dbms_rls). Теперь новая возможность позволяет сильно облегчить жизнь (кто сталкивался — тот однозначно оценит!).

Теперь появилась новая функция, EXPAND_SQL_TEXT из пакета DBMS_UTILITY.
Несколько примеров (взято с сайта Т.Кайта).

Давайте посмотрим какой на самом деле SQL будет выполнен для получения данных из сандартной all_users

ops$tkyte%ORA12CR1> variable x clob
 ops$tkyte%ORA12CR1> begin
   2          dbms_utility.expand_sql_text
   3          ( input_sql_text => 'select * from all_users',
   4            output_sql_text => :x );
   5  end;
   6  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte%ORA12CR1> print x
 
X
 --------------------------------------------------------------------------------
 SELECT "A1"."USERNAME" "USERNAME","A1"."USER_ID" "USER_ID","A1"."CREATED" "CREATED","A1"."COMMON" "COMMON" FROM  (SELECT "A4"."NAME" "USERNAME","A4"."USER#" "USER_ID","A4"."CTIME" "CREATED",DECODE(BITAND "A4"."SPARE1",128),128,'YES','NO') "COMMON" FROM "SYS"."USER$" "A4","SYS"."TS$" "A3","SYS"."TS$" "A2" WHERE "A4"."DATATS#"="A3"."TS#" AND "A4"."TEMPTS#"="A2"."TS#" AND "A4"."TYPE#"=1) "A1"

Ну и пример работы с VPD:

-- Создаём тестовый пример

ops$tkyte%ORA12CR1> create table my_table
  2  (  data        varchar2(30), 
  3     OWNER       varchar2(30) default USER
  4  )
  5  /
Table created.

ops$tkyte%ORA12CR1> create or replace
  2  function my_security_function( p_schema in varchar2,
  3                                 p_object in varchar2 )
  4  return varchar2
  5  as
  6  begin
  7     return 'owner = USER';
  8  end;
  9  /
 
Function created.
 
ops$tkyte%ORA12CR1> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'MY_TABLE',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => user,
  7       policy_function => 'My_Security_Function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
-- А теперь показываем как работает новая фича.

ops$tkyte%ORA12CR1> begin
  2          dbms_utility.expand_sql_text
  3          ( input_sql_text => 'select * from my_table',
  4            output_sql_text => :x );
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte%ORA12CR1> print x
 
X
 --------------------------------------------------------------------------------
 SELECT "A1"."DATA" "DATA","A1"."OWNER" "OWNER" FROM  (SELECT "A2"."DATA" "DATA", "A2"."OWNER" "OWNER" FROM "OPS$TKYTE"."MY_TABLE" "A2" WHERE "A2"."OWNER"=USER@!)  "A1"

Автор: DenKrep

Источник