8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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

Views can be a useful way to hide complexity from developers, but that can in turn cause problems. It's easy to write apparently simple statements, that result in extremely complex SQL being sent to the server. The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands references to views, turning them into subqueries in the original statement. A simple example of this is shown below.

Related articles.

Setup

The examples in this article use the following tables.

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.

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;

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.

SELECT * FROM emp_v;

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 EXPAND_SQL_TEXT 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.

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>

The SELECT * has been expanded into the full column list from the view. We can see the the EMP_V 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:

Hope this helps. Regards Tim...

Back to the Top.