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.
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.
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,"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 😉
11 Comments
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.
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?
If you never used * when declaring a cursor, you could search your PL/SQL source for references to that column.
I was able to do it by typing the column name and checking:
All Object Types,
All Identifier Types,
All Source Lines, and
All Dependencies
This was a very helpful post. Thank you for doing this.
Just make sure ALL your source has been compiled for PL/Scope!
In 12.2, this has been enhanced to start tracking individual SQL statements too.
PL/Scope reports on SQL Identifier and Statement metadata for SQL statements, tables, views and sequences appearing in PL/SQL source code.
Can we search within the wrapped code?
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?
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