The view USER_DEPENDENCIES shows all dependencies for objects including procedures, functions, package specifications, and package bodies. Query this view to see all PL/SQL procedures that are dependent on a table you are about to drop. The description is: SQL> desc user_dependencies Name Null? Type ------------------------------ -------- --------------- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) REFERENCED_OWNER VARCHAR2(30) REFERENCED_NAME VARCHAR2(64) REFERENCED_TYPE VARCHAR2(12) REFERENCED_LINK_NAME VARCHAR2(128) SCHEMAID NUMBER DEPENDENCY_TYPE VARCHAR2(4) If you write a procedure, HELLO, that inserts a row in the STUDENTS table, there will be a row in this view with NAME=HELLO and REFERENCED_NAME=STUDENTS.
When a procedure is invalid, the task is to identify all objects that REFERENCE that procedure. To do this, SELECT NAME, TYPE WHERE REFERENCED_NAME = the invalid object. Continue this search until there are no more dependencies. There are a variety of ways to automate this search. Writing a recursive PL/SQL function is one option. The following is one solution that populates a TEMP table with a SELECT from USER_DEPENDENCIES. To demonstrate a scenario, consider the impact to procedures P1 through P4, or any other procedures, when P5 becomes invalid. This dependency is illustrated in Figure 9-3. The procedure code for P1 through P5 is: CREATE OR REPLACE procedure P5 IS BEGIN dbms_output.put_line('executing p5'); END; / CREATE OR REPLACE procedure P4 IS BEGIN P5; END; / CREATE OR REPLACE procedure P3 IS BEGIN P4; END; / CREATE OR REPLACE procedure P2 IS BEGIN P5; END; / CREATE OR REPLACE procedure P1 IS BEGIN P2; P3; END; / Figure 9-3. Invalid Procedures.
Assume for the example that all procedures are invalid and we need to analyze what calls P5. What calls the code that calls P5? To trace a dependency, create a TEMP table with PARENT, CHILD columns . CREATE TABLE temp (parent VARCHAR2(30), child VARCHAR2(30)); Load the starting point, which can be any point in the tree. We start with P5. INSERT INTO temp VALUES ('P5', null); Repeat the following INSERT statement until the message 0 rows created is received. INSERT INTO temp SELECT u.name a, t.parent b FROM user_dependencies u, temp t WHERE u.referenced_name=t.parent AND NOT EXISTS (SELECT * FROM temp WHERE parent=u.name AND child=t.parent); Select from TEMP, shown next , to show that P1 calls P2 and P3, P2 calls P5, and so forth. PARENT CHILD -------------------- ------- P1 P2 P1 P3 P3 P4 P2 P5 P4 P5 P5 Tracing invalid code should start with an attempt to compile the schema using DBMS_UTILITY, described in Section 9.4, "Dependencies among Procedures." After compiling the schema, trace invalid objects by identifying those objects that have an immediate dependency. For this example, identify procedures P4 and P2, because they directly call procedure P5. Troubleshooting a dependency issue is only required when code is changed and recompiled without any preliminary analysis. There are numerous methods for tracing code dependency. The USER_DEPENDENCIES view provides all necessary information. You can automate a process with a PL/SQL solution or sequentially query the USER_DEPENDENCIES until you identify where the code is broken. How can you extract the source code for P4 and P2? This may be necessary to investigate why the code is broken. Use the views USER and ALL_SOURCE covered in the next section. |