Oracle 12c. Continuing reviews of new features. SQL Text expansion
I just found in T. Kite's blog a description of a very useful, in my opinion, new 12-ka opportunity.
It is called SQL Text expansion . In fact, it allows you to get the final request, after all the transformations and additions.
1) all subject view will be opened. Those. let's say you, at first glance, have a simple query from one view, but if you dig deeper, you will find that inside this view consists of a complex request, and that one is based on other views, which, in turn, are on a lot more submissions. Surely many of us have come across this and know what efforts it is worth putting together the whole picture in one’s head, running through the descriptions of each presentation and trying to imagine how it all comes together in one request ...
2) Working with virtual private database (dbms_rls) is a special effort when debugging logic and performance. Now a new opportunity allows you to greatly simplify your life (whoever came across will definitely appreciate it!).
Now there is a new function, EXPAND_SQL_TEXT from the DBMS_UTILITY package.
A few examples (taken from T. Kite's website).
Let's see what SQL will actually be executed to receive data from the standard all_users
Well, an example of working with VPD:
It is called SQL Text expansion . In fact, it allows you to get the final request, after all the transformations and additions.
1) all subject view will be opened. Those. let's say you, at first glance, have a simple query from one view, but if you dig deeper, you will find that inside this view consists of a complex request, and that one is based on other views, which, in turn, are on a lot more submissions. Surely many of us have come across this and know what efforts it is worth putting together the whole picture in one’s head, running through the descriptions of each presentation and trying to imagine how it all comes together in one request ...
2) Working with virtual private database (dbms_rls) is a special effort when debugging logic and performance. Now a new opportunity allows you to greatly simplify your life (whoever came across will definitely appreciate it!).
Now there is a new function, EXPAND_SQL_TEXT from the DBMS_UTILITY package.
A few examples (taken from T. Kite's website).
Let's see what SQL will actually be executed to receive data from the standard 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"
Well, an example of working with 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"