Two Cool Utilities in DBMS_UTILITY

Oracle Database DBMS_UTILITY package has several useful subprograms. GET_DEPENDENCY shows the objects depended on the object. Here is an example (the procedure uses DBMS_OUTPUT to show the result, hence SET SERVEROUTPUT is required in SQL*Plus):

SQL> set serveroutput on
SQL> exec dbms_utility.get_dependency('VIEW','HR','EMP_DETAILS_VIEW');
-
DEPENDENCIES ON HR.EMP_DETAILS_VIEW
------------------------------------------------------------------
*VIEW HR.EMP_DETAILS_VIEW()

PL/SQL procedure successfully completed.

SQL> exec dbms_utility.get_dependency('TABLE','HR','EMPLOYEES');
-
DEPENDENCIES ON HR.EMPLOYEES
------------------------------------------------------------------
*TABLE HR.EMPLOYEES()
    VIEW HR.EMP_DETAILS_VIEW()
    TRIGGER HR.SECURE_EMPLOYEES()
    TRIGGER HR.UPDATE_JOB_HISTORY()

PL/SQL procedure successfully completed.

SQL>

Oracle Database 12c has a new procedure to provide you with the SQL behind the SQL. DBMS_UTILITY.EXPAND_SQL_TEXT. Useful to expand the views and see the base tables involved, as well as to reveal the real SQL, especially when clauses like “FETCH and OFFSET” are used.

There are two CLOB parameters to this procedure – input SQL text, and output SQL text. Try this in your Oracle Database 12c…

SQL> set long 32000
SQL> variable x1 CLOB
SQL> exec dbms_utility.expand_sql_text('SELECT TABLE_NAME FROM DBA_TABLES',:x1);
SQL> print x1

Related