A short query against a view can hide a long query underneath, especially when views are built on other views. DBMS_UTILITY.EXPAND_SQL_TEXT returns the query with every view replaced by its definition, showing the SQL that the database actually works with.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_utility.expand_sql_text(input_sql_text => clob, output_sql_text => clob_out);
Expand a View
V_LONG_ROUTES selects routes longer than 10,000 km. The query counts those from Dubai.
Setup:
create or replace view v_long_routes as select route_id, origin, destination from routes where distance_km > 10000;
Example:
declare
v_out clob;
begin
dbms_utility.expand_sql_text('select count(*) from v_long_routes where origin = ''DXB''',
v_out);
dbms_output.put_line(v_out);
end;
/Output:
SELECT COUNT(*) "COUNT(*)" FROM (SELECT "A2"."ORIGIN" "ORIGIN" FROM "NIMBUS"."ROUTES" "A2" WHERE "A2"."DISTANCE_KM">10000) "A1" WHERE "A1"."ORIGIN"='DXB' PL/SQL procedure successfully completed.
The view name is replaced by its query as an inline view, with every name fully qualified and aliases generated. Only the columns the query needs are kept. The example drops the view afterward.
Expand a SQL Macro
Table SQL macros are expanded too, which shows the text a macro produces.
Example:
create or replace function routes_from (p_origin varchar2)
return varchar2 sql_macro(table)
is
begin
return 'select route_id, destination from routes where origin = p_origin';
end;
/
declare
v_out clob;
begin
dbms_utility.expand_sql_text('select * from routes_from(''LHR'')', v_out);
dbms_output.put_line(v_out);
end;
/Output:
Function ROUTES_FROM compiled SELECT "A1"."ROUTE_ID" "ROUTE_ID","A1"."DESTINATION" "DESTINATION" FROM (SELECT "A2"."ROUTE_ID" "ROUTE_ID","A2"."DESTINATION" "DESTINATION" FROM (SELECT "A3"."ROUTE_ID" "ROUTE_ID","A3"."DESTINATION" "DESTINATION" FROM "NIMBUS"."ROUTES" "A3" WHERE "A3"."ORIGIN"='LHR') "A2") "A1" PL/SQL procedure successfully completed.
The call routes_from('LHR') becomes the query the macro returned, with the argument in place of the parameter. The example drops the function afterward.
Things to Know
- You need the privileges to query every object in the statement.
- The output is for reading, not for performance tuning: the optimizer may still merge and transform it.
- Use it to review security policies hidden in views, or to see how deeply views are nested.
Related Guides
Conclusion
DBMS_UTILITY.EXPAND_SQL_TEXT replaces views and table SQL macros in a query with the SQL they stand for. Use it to understand nested views and generated SQL before you tune or change them.
