ThatJeffSmith

How to find dependent objects in your PL/SQL Programs using SQL Developer

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

Definition

Search Results:

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,"peslbem"); --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 ;)