I’ve answered this question a few times over the past several months, but I’m just now getting around to putting my answer onto ‘paper.’ The common scenario is someone decides to alter a table.

Immediately the paranoid – is it really paranoia if someone is actually out to get you? – start to worry about how many programs they’ve just borked. The database does track immediate object dependencies.

The known dependencies

However, it’s possible that your code is dependent on an object and isn’t being tracked by the _DEPENDENCIES views. The ‘Dependency’ thread on AskTom has a lot of great information and can give you an idea of what people are looking for in case this is a new topic for you.

Being paranoid, you want to know every possible occurrence of where your code or column name is being used in the database. Thankfully there is a utility in SQL Developer that can help you answer this question. That feature is

Find Database Object

This panel can be opened from the View menu. From there you enter your search term, decide if you want it wildcarded, then enter your search parameters.

Find stuff in the database!

I like the advanced search. I might set it to search everything by default, but I always like to know what my options are.

Don’t worry about case – we auto UPPER the search string. If you want a whole word search, then be sure NOT to check the % check box.

You can limit the search to a single schema, or all schemas – that’s pretty self-explanatory.

But when we get into the code objects, there are several types to choose from:

  • Assignment
  • Call
  • Declaration
  • Definition
  • Reference

These are also straightforward, but I want to share an example of each type. I prefer to always confirm my assumptions. It’s possible that I could be wrong, even if it has happened lately.

Assignment

Search Results:

FETCH emp_cursor INTO emp_record; --variable assignment
emp_tab(i) := emp_record; --variable assignment

SQL Developer is parsing the results and looking for instance of ‘INTO’ or ‘:=’ on the Assignment search type.

Call

Search Results:

 OPEN emp_cursor; --cursor call
 FETCH emp_cursor INTO emp_record; --cursor call
 CLOSE emp_cursor; --cursor call
 OPEN emp_type FOR  --variable call

SQL Developer is keying on ‘OPEN’, ‘FETCH’, or ‘CLOSE’ syntax for the Call search type.

Declaration

Search Results:

TYPE employees_cursor IS REF CURSOR RETURN emp%ROWTYPE; --refcursor declaration
CREATE OR REPLACE PACKAGE "EMPLOYEES_TAPI" --package declaration
EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE, --record declaration
TYPE EMPLOYEES_tapi_tab --nested table declaration
employee_name VARCHAR2 (30); --variable declaration
FUNCTION EMPTY_BLOB RETURN blob; --function declaration
</sql>
 
<strong>Definition</strong>
<a href="http://thatjeffsmith.wpengine.com/wp-content/uploads/2012/05/depend6.png"><img src="http://thatjeffsmith.wpengine.com/wp-content/uploads/2012/05/depend6.png" alt="" title="depend6" width="450" height="43" class="alignleft size-full wp-image-3138" /></a>
<div style="clear:both;"></div>
Search Results:
<pre lang="plsql">
CREATE OR REPLACE PACKAGE BODY EMPLOYEES_tapi -- package definition
CREATE OR REPLACE PROCEDURE EMP_LIST AS -- procedure definition

So maybe some overlap with Declaration searches. What’s the difference between a ‘definition’ and a ‘declaration?’

You must declare and define a procedure before invoking it. You can either declare and define it at the same time, or you can declare it first and then define it later in the same block or subprogram.

So saying it exists is a declaration. Saying what it is is a definition. Savvy?

Reference

Search Results:

TABLE OF EMPLOYEES_tapi_rec; --record reference
PRAGMA INTERFACE(c,EMPTY_BLOB,&quot;peslbem&quot;); --function reference
emp_record emp_cursor%ROWTYPE; --cursor reference
SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id; --formal in reference
emp_tab(i) := emp_record; -- variable reference

And now for the grand finale, let’s end on a ‘Trick!’

Double-mouse-click on the search result

SQL Developer will auto-open the code object and place the cursor at the identified search string. Having trouble seeing your search term? Don’t forget about our ‘Incremental Search (Ctrl+E)‘ feature! It’s like turning the light on in an dark and scary room 😉

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

11 Comments

  1. I need to find the dependent objects on a view Ex. package, store proc etc.. I tried all_dependencies, dba_dependencies, user_source. These tables providing the information of table or synonym on top of which view is created, but not pulling the objects that are created using view.

    • Look in the opposite direction, you want to check the dependencies from the package’s point of view. The package dependencies will show what tables are required.

  2. T. MacCabe Reply

    I’d like to know, if I drop a specific column, what am I breaking? Can I get a listing of all views, triggers, and packages/procedures relying on a column only?

    • that would kind of defeat the point if you could, right?

      if it’s your code, you should have an unwrapped copy of it in your dev instance?

      package specs are usually unwrapped, did you check there?

  3. Prasanna Peshkar Reply

    Very Nice post Really it’s a simple way to find dependant objects in Pl/SQl program.
    Thnx a lot for all such informative posts.

    • How can I list database link dependencies? Like in Toad feature, Used By?

      Thanks,

      NoTOAD

    • Probably? Not sure what they do to determine that – our dependencies pages just show what’s in the ALL_DEPENDENCIES views for an object – this view doesn’t cover DB_LINKS as a dependent object type

Write A Comment