SQL> create table t(a number);

Table created.

SQL>
SQL> create or replace function f
  2  return number
  3  as
  4  v number;
  5  begin
  6  select count(*) into v from t;
  7  return v;
  8  end;
  9  /

Function created.

SQL>
SQL> show errors;
No errors.
SQL>
SQL> select name,referenced_name,referenced_type from user_dependencies where 
name='F' and referenced_owner='SCOTT';

NAME                           REFERENCED_NAME                                                  REFERENCED_TYPE    
------------------------------ ---------------------------------------------------------------- ------------------ 
F                              T                                                                TABLE              

SQL> create or replace function get_sql_dependencies (p_sql in varchar2)
  2  return sys.odcivarchar2list
  3  pipelined
  4  is
  5    pragma autonomous_transaction;
  6  begin
  7    execute immediate 'create or replace view "get_sql_dependencies_view" as '||p_sql;
  8    for rec in (
  9     select REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
 10     from user_dependencies
 11     where name = 'get_sql_dependencies_view'
 12    ) loop
 13     pipe row (rec.REFERENCED_OWNER||'.'||reC.REFERENCED_NAME||' ('||rec.REFERENCED_TYPE||')');
 14    end loop;
 15  end;
 16  /

Function created.

SQL> select * from table(get_sql_dependencies ('select * from t1, t2 where col1a=col2a'));
COLUMN_VALUE
------------------------------------------------------------------------------------
MICHEL.T1 (TABLE)
MICHEL.T2 (TABLE)

SCOTT@orcl > create or replace
  2            view v1
  3              as
  4                select  1
  5                  from  dual
  6  /
              select  1
                      *
ERROR at line 4:
ORA-00998: must name this expression with a column alias


SCOTT@orcl > 

SQL> create or replace function get_sql_dependencies (p_sql in varchar2)
  2  return sys.odcivarchar2list
  3  authid current_user
  4  pipelined
  5  is
  6    pragma autonomous_transaction;
  7  begin
  8    execute immediate 'create or replace view "get_sql_dependencies_view" as '||p_sql;
  9    for rec in (
 10     select REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
 11     from user_dependencies
 12     where name = 'get_sql_dependencies_view'
 13    ) loop
 14     pipe row (rec.REFERENCED_OWNER||'.'||reC.REFERENCED_NAME||' ('||rec.REFERENCED_TYPE||')');
 15    end loop;
 16  end;
 17  /

Function created.

SQL> create role r;

Role created.

SQL> grant create table, create view to r;

Grant succeeded.

SQL> grant execute on GET_SQL_DEPENDENCIES to r;

Grant succeeded.

SQL> create user test identified by test default tablespace ts_d01 quota unlimited on ts_d01;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> grant r to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> create table t (col int);

Table created.

TEST> select * from table(michel.get_sql_dependencies ('select * from t'));
COLUMN_VALUE
--------------------------------------------------------------------------
TEST.T (TABLE)

TEST> select object_name, object_type from user_objects;
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
get_sql_dependencies_view      VIEW
T                              TABLE

SQL> create or replace function get_sql_dependencies (p_sql in varchar2)
  2      return sys.odcivarchar2list
  3      pipelined
  4      is
  5        pragma autonomous_transaction;
  6      BEGIN
  7        execute immediate 'DELETE FROM plan_table';
  8        execute immediate 'explain plan for '||p_sql;
  9        COMMIT;
 10        for rec in (
 11        SELECT DISTINCT OBJECT_OWNER, object_name, OBJECT_TYPE
 12          FROM plan_table WHERE object_type = 'TABLE'
 13         UNION
 14        SELECT DISTINCT table_owner, TABLE_NAME, '(Via Index)'
 15          FROM plan_table
 16          join all_indexes ON index_name = object_name
 17       ) loop
 18       pipe row (
 19         rec.OBJECT_OWNER||'.'||reC.object_name||' ('||rec.OBJECT_TYPE||')'
 20       );
 21      end loop;
 22    end;
 23  /

Function created.

SQL>
SQL>
SQL> SELECT * FROM TABLE(get_sql_dependencies('select * from dual'));

COLUMN_VALUE
--------------------------------------------------------------------------------

SYS.DUAL (TABLE)