Finding a list of table names in the Oracle table DBA_SOURCE

Carbon 0
Viewed 26

I'm trying to find a list of tables in a procedure that match a list of target tables. These tables all contain a same named column. Anyway, I've tried two queries with no luck. Here they are

select atc.TABLE_NAME from ALL_TAB_COLS atc join  all_SOURCE als on atc.table_name
 like '%'||als.text||'%' and als.name = 'SP_SLD_GEN_GIC_REINV_DET_FI' where
atc.COLUMN_NAME = 'C_CURR';

select atc.TABLE_NAME 
from ALL_TAB_COLS atc where atc.COLUMN_NAME = 'C_CURR'
and atc.table_name like (select '%'||als.text||'%' from all_SOURCE als
where als.name = 'SP_SLD_GEN_REINV_DET_FI')
order by atc.TABLE_NAME;

Neither returns anything. What I want is a list of all the tables found in Source from the procedure 'SP_SLD_GEN_REINV_DET_FI', containing table names that match the list of tables names from ALL_TAB_COLS where containing the column named 'C_CURR'.

I know when you use 'like' your usually comparing to a hard string like '%AUTUMN%'. Here, you can see I'm trying to embed the returned string in a set of '%' to complement using 'LIKE'. I'm not getting it. Is there a way to do this.

Or can I do this using ISNTR()?

Thanks!

oracle sql
1 Answers

Used DBA_DEPENDANCIES instead. This works. See below:

select TABLE_NAME
    from ALL_TAB_COLS where COLUMN_NAME = 'C_CURR'
    and table_name in (
    select referenced_name
    from   SYS.DBA_dependencies
    where  referenced_type = 'TABLE'
    and    referenced_owner = 'SLDPROD'
    and    name in ('SP_SLD_GEN_GIC_REINV_DET_FI')
    );
Carbon0
Related