If you need to analyze query which is based on complex view it can take a lot of time to do it.

New procedure EXPAND_SQL_TEXT of package DBMS_UTILITY helps to recursively replace any view references in the input SQL query with the corresponding view subquery.

Test data

create table test_tbl
(id number,
 id1 number);

create view test_v1
as select * from test_tbl;

create view test_v2
as
select * from test_v1;

create view test_v3
as
select id+id1 ok from test_v2;

What if you need to analyze following query

select * from test_v3

With new procedure it’s piece of cake

set serveroutput on
declare
  v_in_view  clob := 'select * from TEST_V3';
  v_out_view clob;
begin
  dbms_utility.expand_sql_text(v_in_view, v_out_view);
  dbms_output.put_line(v_out_view);
end;
/

anonymous block completed
SELECT "A1"."OK" "OK" FROM  
(SELECT "A2"."ID"+"A2"."ID1" "OK" 
FROM (SELECT "A3"."ID" "ID","A3"."ID1" "ID1" 
FROM (SELECT "A4"."ID" "ID","A4"."ID1" "ID1" 
FROM TOMASZ."TEST_TBL" "A4") "A3") "A2") "A1"

be smart do it with new feature WITH FUNCTION directly in sql

with
  function get_data(p_in_view clob)
  return clob
  is
    v_out_view clob;
  begin
    dbms_utility.expand_sql_text(p_in_view, v_out_view);

    return v_out_view;
  end;
select get_data('select * from TEST_V3') from dual
/

GET_DATA('SELECT*FROMTEST_V3')
----------------------------------------------
SELECT "A1"."OK" "OK" FROM  (SELECT "A2"."ID"+ ...

Have a fun 🙂

Tomasz