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
You Might Also Enjoy
- SQL Developer Incremental Find via Ctrl-E
- PLSQL Warning Messages in Oracle SQL Developer
- SQLDev Trick: SHIFT+Hover to Peek Into a Stored Proc
- SQL Developer Video Tutorial: Navigating Your PL/SQL
- JAVADOC for the Oracle Database a la DBDOC
DIGITAL JUICE
No comments:
Post a Comment
Thank's!