📋Steps in this guide1/2

DBMS_UTILITY.EXPAND_SQL_TEXT : Expand SQL References to Views in Oracle Database 12c Release 1 (12.1)

Easily display the true complexity of SQL statements that reference views.

oracle 12cconfigurationintermediate

by OracleDba

12 views

Setup

The examples in this article use the following tables.

Code/Command (click line numbers to comment):

create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname varchar2(14),
  loc varchar2(13)
) ;

create table emp (
  empno number(4) constraint pk_emp primary key,
  ename varchar2(10),
  job varchar2(9),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2) constraint fk_deptno references dept
);

insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

View Expansion

Create a view containing a join. Most of the columns in the select list come from the employees table. The department name comes from the departments table. The two tables are joined together using the department number column. The view has hidden the complexity of the join, allowing us to use an extremely simple query. If we didn't know this was a view, we might be fooled into thinking we've sent a very simple query to the server, but what really happened? On the surface we don't know, unless we understand the view definition. Remember a view is just a stored query. When we include a view in our SQL, the server must expand our statement to include query defined by the view. We can see the real SQL statement processed by the server by expanding the statement. We define a CLOB variable. We call the procedure, passing the SQL statement, and use the CLOB variable as the output variable to hold the expanded SQL. Then we display the SQL. The output was formatted by me to make it more readable. The has been expanded into the full column list from the view. We can see the the reference has been expanded into the full view definition. We have the full select list from the view. Then we have the join between the employees and departments table. There are a couple of things to notice here. The ANSI join from our view definition is now a WHERE-clause join. You will see a lot of odd looking column aliases. This is just to prevent any name clashes. These get resolved back to the proper names in the select list. For more information see: - DBMS_UTILITY.EXPAND_SQL_TEXT - View Expansion : DBMS_UTILITY.EXPAND_SQL_TEXT Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

conn scott/tiger@pdb1

create or replace view emp_v as
select e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.hiredate,
       e.sal,
       e.comm,
       e.deptno,
       d.dname
from   emp e
       join dept d on e.deptno = d.deptno;

select * from emp_v;

set serveroutput on 
declare
  l_clob clob;
begin
  dbms_utility.expand_sql_text (
    input_sql_text  => 'select * from emp_v',
    output_sql_text => l_clob
  );

  dbms_output.put_line(l_clob);
end;
/

SELECT "A1"."EMPNO" "EMPNO",
       "A1"."ENAME" "ENAME",
       "A1"."JOB" "JOB","A1"."MGR" "MGR",
       "A1"."HIREDATE" "HIREDATE",
       "A1"."SAL" "SAL",
       "A1"."COMM" "COMM",
       "A1"."DEPTNO" "DEPTNO",
       "A1"."DNAME" "DNAME"
FROM   (SELECT "A2"."EMPNO_0" "EMPNO",
               "A2"."ENAME_1" "ENAME",
               "A2"."JOB_2" "JOB",
               "A2"."MGR_3" "MGR",
               "A2"."HIREDATE_4" "HIREDATE",
               "A2"."SAL_5" "SAL",
               "A2"."COMM_6" COMM",
               "A2"."QCSJ_C000000000400000_7" "DEPTNO",
               "A2"."DNAME_9" "DNAME"
         FROM  (SELECT "A4"."EMPNO" "EMPNO_0",
                       "A4"."ENAME" "ENAME_1",
                       "A4"."JOB" "JOB_2",
                       "A4"."MGR" "MGR_3",
                       "A4"."HIREDATE" "HIREDATE_4",
                       "A4"."SAL" "SAL_5",
                       "A4"."COMM" "COMM_6",
                       "A4"."DEPTNO" "QCSJ_C000000000400000_7",
                       "A3"."DEPTNO" "QCSJ_C000000000400001",
                       "A3"."DNAME" "DNAME_9"
                FROM   SCOTT."EMP" "A4",
                       SCOTT."DEPT" "A3"
                WHERE  "A4"."DEPTNO"="A3"."DEPTNO") "A2"
       ) "A1"

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!