Oracle 12c. We continue to review new features. SQL Text expansion

I just found in T. Kite's blog a description of a very useful, in my opinion, new 12-opportunity.
Called SQL Text expansion . In fact, it allows you to get the final query, after all the transformations and additions.

1) all subject to view will be disclosed. Those. let's say you have, at first glance, a simple query, from one view, but if you dig deeper, you will find that inside this view consists of a complex query, and that one is based on other views, which, in turn, on another set submissions. Surely many of us have come across this and know how much effort it is to collect 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) Special effort when debugging logic and performance is to work with the virtual private database (dbms_rls). Now the new feature allows you to greatly facilitate life (who faced - he will definitely appreciate!).
')
Now there is a new function, EXPAND_SQL_TEXT from the DBMS_UTILITY package.
Some examples (taken from the site of T.Kayta).

Let's see what SQL will actually be executed to retrieve 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"