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:
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.
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.
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.
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?
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 😉