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):

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…


Leave a Reply

%d bloggers like this: