9.5 USER_DEPENDENCIES


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.

NAME

The name of the procedure function or package that has references to the REFERENCED_NAME. This view is not just for stored procedures. Views and other objects have dependencies as well.

TYPE

For PL/SQL program units, this includes PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY.

REFERENCED_OWNER

This schema owns the referenced object. A procedure can insert a row into a STUDENTS table in another schema. The procedure is dependent on that STUDENTS table. This column is the owner of the STUDENTS table.

REFERENCED_NAME

This is the object name being referenced. This can be a table, view, sequence, or database link. This column will include all other procedures, functions, and packages being references, including packages in the data dictionary such as DBMS_OUTPUT.

REFERENCED_LINK_NAME

This is the name of a database link. A procedure could select from a table at a remote site with the syntax table_name@link_name. This would include the link name.

SCHEMAID

This is the schema ID of the referenced owner.

DEPENDENCY_TYPE

This is HARD unless the dependency is through a REF type object, then this column value is REF.

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.

graphics/09fig03.jpg

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.



Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net