title: Finding table and package references in oracle
body: <p>I have a list of tables and a list of packages. I need to come up with the below two lists</p>
  
  <ul>
  <li>What are the packages that uses the given set of tables</li>
  <li>List of tables that are referenced by each of the given package</li>
  </ul>
  
  <p>The packages uses dynamic sql, hence I may not be able to depend only on dba_reference table.
  The other way I could think of is using a LIKE clause against the dba_source table. But, I will have to write a OR condition for each of the tables that I need (or of course a function or procedure which can loop through each table)</p>
  
  <p>Is there any better way of doing this?
  Any help is greatly appreciated.</p>
  
  <p><strong>Edit: rephrasing the question -</strong> </p>
  
  <p>I have a package which select/inserts/updates several tables. This has dynamic sql. One example is provided below.</p>
  
  <p>I want to identify all the tables referred in this package. What is the best way to achieve this?</p>
  
  <p>In the below example I want to capture both table1 and table2.</p>
  
  <pre><code>if flag = 'Y'
  then final_sql := 'insert into table1 (...)';
  else final_sql := 'insert into table2 (...)';
  end if;
  execute immediate final_sql;
  </code></pre>