1

I have bunch of sql statements which run on oracle database. I want to how we can get the list of tables and columns used by each sql statement.

 select b.country country, 
    a.country code, 
    ASNs, 
    tot_demands, 
    case when has_pp=0 then null else round(100*in_country/has_pp,2) end in_country_pct, 
    case when has_pp=0 then null else round(100*in_asn/has_pp,2) end in_ASN_pct,
    case when has_pp=0 then null else round(100*in_provider/has_pp,2) end in_provider_pct,
    round(100*has_pp/tot_demands,2) has_PP_pct,  
    case when has_pp=0 then null else round(distance/has_pp,2) end avg_distance
 from (
 select country, count(*) ASNs, sum(sum_demand) tot_demands, sum (sum_demand*in_country_pct*has_pp_pct) in_country, sum(sum_demand*in_asn_pct*has_pp_pct) in_asn,sum(sum_demand*in_provider_pct*has_pp_pct) in_provider, sum(sum_demand*has_pp_pct) has_pp, sum(sum_demand*weighted_distance*has_pp_pct) distance
 from ns_pp_inpct 
 where day = (select max(day) from ns_pp_inpct) 
 group by country 
 ) a,
 country_code b
 where a.country=b.code
 order by tot_demands desc
;

I have searched many websites but I didn't find relevant solution. Please help me. If possible, give me solution in python.

  • 1
    If Oracle does not already have some tool for doing this, it could be difficult. The most general answer would be to write some sort of parser. – Tim Biegeleisen Sep 03 '18 at 11:06
  • For sure you can obtain the name of the tables. See the answer at my [question](https://stackoverflow.com/q/35295458/319875) for a nice technique. – Florin Ghita Sep 03 '18 at 11:09
  • 1
    You can try to create view from the statement and then query `ALL_DEPENDENCIES` or `USER_DEPENDENCIES` and see, if it contains the data you need. – sticky bit Sep 03 '18 at 11:15
  • See also the discussion [here](https://stackoverflow.com/questions/49157492/oracle-search-list-of-words-in-string-and-retruen-existing-ones?answertab=active#tab-top) – Marmite Bomber Sep 03 '18 at 13:24
  • ALL_DEPENDENCIES is just having the tables used. – Mammu yedukondalu Sep 04 '18 at 09:47

0 Answers0