PL SQL Find Dependencies on Table Field

Question

I am needing to find all references to a table's field, and I have hundreds of stored procedures to search through. The goal is to find where it is being used in where clauses and add an extra value to the in statement. For example,

where myTable.Field_X in (1,2,3)

needs to become

myTable.Field_X in (1,2,3,4).

So I'm curious if there is a system table, like dba_dependencies, or something that I can query that would show me what procs, views, or functions are referencing the field? Thanks for any help you can give.

Answer 1

What version of Oracle are you using? If you are using at least 11.1, which introduced column-level dependency tracking, and you're not afraid to leverage some undocumented data dictionary tables, you can create a dba_dependency_columns view that will give you this information. But that will show every piece of code that depends on that column not just those where the column is used in the WHERE clause of a statement.

You can search dba_source for the text of procedures, functions, and triggers to look for code that has that sort of WHERE clause. That gets a little tricky, though-- if someone put the list of values on a different line or if there is an inline view where that column name is aliased to something else, it can be tricky to find via a text search. For views, you'd need to use dbms_metadata to generate the DDL for the view and search that in a loop.