Extract all tables inserted/selected inside a procedure of a package

Question

I have a package which has many procedures defined inside it. I need to get all the tables that are being referenced which are either inserted into or selected inside this procedure. Note some procedures might have procedures inside them from other packages. Also any possibility i can differentiate which is being inserted and from table data is just being selected. Not sure how to build the SQL query for the same. Take example Package name: APP procedure name: app_get_data

Answer 1

If you just want to get the table dependencies for a package (and recursively from other dependent packages) then you can use the ALL_DEPENDENCIES table and a hierarchical query:

SELECT DISTINCT *
FROM   ALL_DEPENDENCIES
WHERE  REFERENCED_TYPE = 'TABLE'
START WITH
       OWNER = 'YOUR_USER'
AND    TYPE  = 'PACKAGE'
AND    NAME  = 'YOUR_PACKAGE'
CONNECT BY NOCYCLE
       (   PRIOR REFERENCED_OWNER = OWNER
       AND PRIOR REFERENCED_NAME  = NAME
       AND PRIOR REFERENCED_TYPE  = TYPE
       )
OR     (   PRIOR OWNER = OWNER
       AND PRIOR NAME  = NAME
       AND PRIOR TYPE  = 'PACKAGE'
       AND TYPE        = 'PACKAGE BODY'
       );

If you want to take it further and break it down into specific procedures in a package and into whether a SELECT, INSERT, UPDATE, MERGE or, for example, if the column was just referenced for its data type then you are probably going to have to parse the SQL text for that package.

db<>fiddle here