Oracle Table Usage Across Stored Procedures

Question

I have a 11G database. I need to examine a number of stored procedures to see if they use a particular table. (Both front end and back end sps) I have full access to the database, and I also have a copy of all the individual sps for the project which are stored on a TFS.

I would like a way to generate a list of all the sps that interact with this particular table. I'm unfamiliar with how to go about searching for these. Can anyone advise the most logical way of obtaining this data?

Thanks.

Answer 1

I use PLSQL Developer, in which you can browse to a table (or other object), and view 'Referenced by', to see all objects that refer to the table. That's about as easy as it gets. I can imagine other tools have similar features.

I don't know if this pre-parsed information is readily available in Oracle, but I can imagine so, since those tools seem to work pretty fast.

This information is available in the viewAll_DEPENDENCIES, which these tools probably use.

The source of stored procedures can be found in the USER_SOURCE (or ALL_SOURCE) view, in which the structure of the entire database is stored. Nevertheless, fetching and parsing the code from there would be quite cumbersome.

Answer 2

This question already have an accepted answer but anyhow the query used inside the accepted answer will pick all the user sources which uses the particular table.

Because the question is specific about Procedures you can go for the below query to get the result

SELECT * FROM user_source WHERE text LIKE '%YourTableName%' and TYPE='PROCEDURE';

Answer 3

Here is snippet I wrote to perform impact analysis (ONLY MERGE, INSERT and UPDATE) for a given @schema (upper case only) and @table (upper case only). It will return all the procedure name, procedure code, from line number and to line number along with other details. It can be easily used to include functions objects as well instead of package. am working on a utility that can run across all schema or selected schema (that will include SELECT rows as well). Though this will be good enough for you to start working.

I know you can use the dependency and references available in Oracle to perform similarly. But for package level impact this is good addition. We can also use regex for more complex searches. But like operator is simple and efficient for my needs.

Note, this doesn't work on any dynamic code that may be working in your environment. This is just a appropriate starting point for quick one on impact with static PL/SQL code in your packages.

WITH TableDep as
-- This table returns references where the table is used within the code for UPDATE OR INSERT
(
SELECT 
owner as schemaname,
name as packagename, 
type as typename,
TEXT as refcodeline,
CASE WHEN upper(text) LIKE '%INSERT%' THEN 'INSERT'
     WHEN upper(text) LIKE '%UPDATE%' THEN 'UPDATE'
   WHEN upper(text) LIKE '%MERGE%' THEN 'MERGE'
END AS opr,
:Tablename AS Tablename,
line refline
FROM dba_source WHERE upper(owner) = upper(:OWNER)  
      AND type = 'PACKAGE BODY' 
      AND (
            upper(text) LIKE ('%INSERT INTO '||:Tablename||'%')
            OR 
            upper(text) LIKE ('%UPDATE%'||:Tablename||' %')
            OR
            upper(text) LIKE ('%MERGE%'||:Tablename||' %')
           )
  ),
ProcedureDetails as
-- This code build all procedures within the package for references that is found in above query
(
SELECT 
  owner as schemaname,
  name as packagename, 
  type as typename,
  TEXT,
  trim(REGEXP_SUBSTR(TEXT, '(PROCEDURE [[:print:]]+)\(',1,1,null,1))   as procedure_name,
  line startline,
  LEAD(line, 1) OVER (partition by name order by line)-1 as endline
FROM dba_source
WHERE owner = upper(:OWNER) 
      AND type = 'PACKAGE BODY' 
      AND upper(text) LIKE '%PROCEDURE%(%'
      and exists (SELECt 1 FROM TableDep WHERE TableDep.packagename=name)
)
,ProcCode as
-- This code builds procedures into one cell per program for a given package. Later to find the  effected procedures
(
SELECT 
       ProcTag.packagename ,
       ProcTag.schemaname,
       ProcTag.typename,
       ProcTag.PROCEDURE_NAME,
       ProcTag.startline,
       ProcTag.endline,
       TO_CLOB(rtrim(xmlagg(xmlelement(e,codeline.text).extract('//text()') order by line).GetClobVal(),',')) as Procedure_Code
FROM
    ProcedureDetails ProcTag
    INNER JOIN dba_source codeline ON ProcTag.packagename=codeline.name
                                      AND ProcTag.schemaname=codeline.owner
                                      and ProcTag.typename=codeline.type
                                      and codeline.line between ProcTag.startline and ProcTag.endline
--WHERE PROCEDURE_NAME='PROCEDURE TRANS_KAT_INSO'
    group by 
       ProcTag.packagename ,
       ProcTag.schemaname,
       ProcTag.typename,
       ProcTag.PROCEDURE_NAME,
       ProcTag.startline,
       ProcTag.endline
      )
-- extract all the reference code for the given table selected with it complete procedure code.
SELECT
ProcHeader.Packagename, ProcHeader.schemaname, ProcHeader.typename, ProcHeader.procedure_name, ProcHeader.Procedure_Code ,ProcHeader.startline,ProcHeader.endline,ProcReference.Tablename, ProcReference.opr
FROM 
  ProcCode ProcHeader
  INNER JOIN 
        (
          SELECT DISTINCT ProcCode.Packagename, ProcCode.schemaname, ProcCode.typename, ProcCode.procedure_name , TableDep.Tablename, TableDep.opr
          FROM               ProcCode 
                  INNER JOIN TableDep ON ProcCode.packagename=TableDep.packagename
                                            AND ProcCode.schemaname=TableDep.schemaname
                                            and ProcCode.typename=TableDep.typename
                                            and TableDep.refline between ProcCode.startline and ProcCode.endline
        ) ProcReference
    ON    ProcHeader.Packagename=ProcReference.Packagename
          AND ProcHeader.schemaname=ProcReference.schemaname
          AND ProcHeader.typename=ProcReference.typename
          AND ProcHeader.procedure_name=ProcReference.procedure_name
;

Answer 4

Two things, in PL/SQL there are some changes which will require the recompilation of pl/sql object, other don't. To see the first one, you have the ALL_DEPENDENCIES view. Or DBA_ if you prefer.

If you just want to see where the table name appears in all the pl/sql code, whether a change to the table will require recompilation or not, you can use ALL_SOURCE using a upper and %, but it might take some time.

Answer 5

If I understand this correctly, you're trying to search for occurrence of a table in all stored procs. In that case, you can use this query:

When searching for occurrences of SP in your schema

SELECT * FROM user_source WHERE text LIKE '%tab_name%';

When searching for occurrences of SP in all schemas

SELECT * FROM all_source WHERE text LIKE '%tab_name%';