12c – SQL Text Expansion

No Oracle Database 12c, foi introduzida uma nova funcionalidade, o SQL Text Expansion.

E na package DBMS_UTILITY, foi adicionada a procedure EXPAND_SQL_TEXT.

SQL> variable retorno clob


SQL> begin
dbms_utility.expand_sql_text( input_sql_text => 'select * from emp', output_sql_text => :retorno );
end;
/

PL/SQL procedure successfully completed.



SQL> print retorno



RETORNO

--------------------------------------------------------------------------------
SELECT "A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."JOB" "JOB","A1"."MGR" "MG
R","A1"."HIREDATE" "HIREDATE","A1"."SAL" "SAL","A1"."COMM" "COMM","A1"."DEPTNO"
"DEPTNO" FROM "SCOTT"."EMP" "A1"

Vamos verificar o efeito com uma política de VPD:

SQL> SELECT count(*)

FROM emp e, dept d

WHERE d.deptno = e.deptno;



COUNT(*)

----------

14

Criando a política de VPD, para mostrar apenas os dados referentes ao departamento 30:

SQL> CREATE OR REPLACE FUNCTION hide_sal_comm ( v_schema IN VARCHAR2, v_objname IN VARCHAR2) RETURN VARCHAR2 AS

 con VARCHAR2 (200);


BEGIN

 con := 'deptno=30';

 RETURN (con);

END hide_sal_comm;

/
Function created.

SQL> BEGIN
DBMS_RLS.ADD_POLICY ( object_schema => 'scott', object_name => 'emp',
policy_name => 'hide_sal_policy', policy_function => 'hide_sal_comm');

END;

/

PL/SQL procedure successfully completed.


SQL> SELECT count(*)
FROM emp e, dept d

WHERE d.deptno = e.deptno;

COUNT(*)
----------

6

Verificando o SQL gerado:

SQL> variable retorno clob


SQL> begin
dbms_utility.expand_sql_text( input_sql_text => 'SELECT count(*) FROM emp e, dept d WHERE d.deptno = e.deptno', output_sql_text => :retorno );
end;
/

PL/SQL procedure successfully completed.



SQL> print retorno



RETORNO

--------------------------------------------------------------------------------
SELECT COUNT(*) "COUNT(*)" FROM (SELECT "A3"."EMPNO" "EMPNO","A3"."ENAME" "ENAM
E","A3"."JOB" "JOB","A3"."MGR" "MGR","A3"."HIREDATE" "HIREDATE","A3"."SAL" "SAL"
,"A3"."COMM" "COMM","A3"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "A3" WHERE "A3"."DEPTNO"=30) "A2","SCOTT"."DEPT" "A1" WHERE "A1"."DEPTNO"="A2"."DEPTNO"

Abraço,

Alex Zaballa.