0

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
THeParadigmShifter
  • 23
  • 8
  • You are probably going to have to parse the SQL text for that. You could try to look at the `USER_DEPENDENCIES` (or `ALL_DEPENDENCIES`) table but there can be dependencies on a table for its column data types without even using an `INSERT` or `SELECT` ([db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=641396bdc02d1ae929f5b3917580ca90)) so you cannot rely on that to tell you what you want to know. – MT0 Apr 06 '22 at 08:16
  • Alright could you help me with the query i can use to just get the references of tables used for my package whether inserted/selected or referenced for data type – THeParadigmShifter Apr 06 '22 at 08:29

1 Answers1

0

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Could you elaborate what you mean by parse the SQL text for this package. And also how to achieve that – THeParadigmShifter Apr 06 '22 at 09:11
  • Also do I need SQL parsing for a procedure too ? – THeParadigmShifter Apr 06 '22 at 09:47
  • Or if i could use this SQL block for procedure what parameters should i pass – THeParadigmShifter Apr 06 '22 at 09:47
  • @THeParadigmShifter Parse the SQL text for the package means: in the simple case, something like [this answer](https://stackoverflow.com/a/251914/1509264) then look a splitting the package into procedures and searching for table names; or in the complex case, build an abstract syntax tree for the package and its contained statements then extract the table names ([maybe look at his answer](https://stackoverflow.com/questions/31752034/how-do-i-access-the-ast-abstract-syntax-tree-for-a-pl-sql-stored-procedure)). This is something that would take a lot of effort and is left as an exercise for you. – MT0 Apr 06 '22 at 09:56
  • If you want to search procedures that are outside of packages then change the `START WITH` clause to `OWNER = 'YOUR_USER' AND TYPE = 'PROCEDURE' AND NAME = 'YOUR_PROCEDURE'`. If you are trying to search individual procedures inside of packages then the entire package is aggregated together within the `ALL_DEPENDENCIES` table and it is impossible to get more granularity than the dependencies across the entire package. – MT0 Apr 06 '22 at 09:59
  • Can you give me the snippet that is be used for a particular procedure inside a particular package i tried with and command but it's not showing up – THeParadigmShifter Apr 06 '22 at 11:06
  • @THeParadigmShifter As I said, changing the `START WITH` clause to use `TYPE = 'PROCEDURE'` is for globally defined procedures that are **outside** of a package. If you want to parse a package and find dependencies **inside** a specific package procedure then you will need to use the SQL text and that is far too complicated a solution to knock up in 10-20 minutes as a StackOverflow answer (as a reliable solution would be to generate an abstract syntax tree and then parse that); if you want that as a solution then you are going to need to code it yourself or wait for someone else to answer. – MT0 Apr 06 '22 at 11:15
  • Oh understood i was just thinking if that's possible to do some AND clauses and check that out. Well thanks a lot you helped a lot today. Will try to figure out a SQL parser solution and hopefully someone else gives an answer that would be great too. – THeParadigmShifter Apr 06 '22 at 11:26
  • @THeParadigmShifter If it is a one-off activity then you are probably going to be quicker to do it by hand and can start by using the output from the query above (possibly removing the `DISTINCT` clause and the `WHERE` filter to get all the intermediate steps) and go through every dependency by hand and create a flow chart (or similar). The only value to writing a parser is if you need to do this repeatedly (and then you may want to question why you need to do it rather than having decent documentation of the packages to start with). – MT0 Apr 06 '22 at 11:36