DBMS_UTILITY.EXPAND_SQL_TEXT

DBMS_UTILITY.EXPAND_SQL_TEXT

This is a nice and easy feature implemented on 12c.
It consists in expand the sql_texts when you are using views.

Find below the example:

1.) Create some tables to Test:

create table dep
(id number,
 dep_name varchar2(100));
 
 ALTER TABLE dep
ADD CONSTRAINT dep_pk PRIMARY KEY (id);
 
insert into dep values (1,'dep 1');
insert into dep values (2,'dep 2');
insert into dep values (3,'dep 3');
commit;

create table emp
(id number,
emp_name varchar2(100),
id_dep number);

 ALTER TABLE emp
ADD CONSTRAINT emp_pk PRIMARY KEY (id);

ALTER TABLE emp
ADD CONSTRAINT emp_dep_fk
   FOREIGN KEY (id_dep)
   REFERENCES dep (id);

insert into emp values (1, 'emp 1', 1);
insert into emp values (2, 'emp 2', 1);
insert into emp values (3, 'emp 3', 1);

insert into emp values (4, 'emp 4', 2);
insert into emp values (5, 'emp 5', 2);
insert into emp values (6, 'emp 6', 2);

insert into emp values (7, 'emp 7', 3);
insert into emp values (8, 'emp 8', 3);
insert into emp values (9, 'emp 9', 3);

commit;
 
2.) Create a view

create view v_qtd_emp_by_dep as
select count(*) qtd_emp, a.dep_name
from dep a, emp b
where
a.id=b.id_dep
group by a.dep_name;

15:12:19 SQL> select * from v_qtd_emp_by_dep;

      QTD_EMP DEP_NAME
------------- ----------
            3 dep 2
            3 dep 3
            3 dep 1
            
3.) EXPAND_SQL_TEXT

SET SERVEROUTPUT ON 
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => 'select * from v_qtd_emp_by_dep',
    output_sql_text => l_clob
  );

  DBMS_OUTPUT.put_line(l_clob);
END;
/

SELECT "A1"."QTD_EMP" "QTD_EMP","A1"."DEP_NAME" "DEP_NAME" 
FROM  
(SELECT COUNT(*) "QTD_EMP","A3"."DEP_NAME" "DEP_NAME" 
FROM SYS."DEP" "A3",SYS."EMP" "A2" 
WHERE "A3"."ID"="A2"."ID_DEP" 
GROUP BY "A3"."DEP_NAME") "A1"

PL/SQL procedure successfully completed.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s