GET_DEPENDENCY procedure - List objects dependent on the given object
The GET_DEPENDENCY procedure provides the capability to list all objects that are dependent upon the given object.
Syntax
Parameters
- type
- An input argument of type VARCHAR(128) that specifies the object type of name. Valid values are FUNCTION, INDEX, LOB, PACKAGE, PACKAGE BODY, PROCEDURE, SEQUENCE, TABLE, TRIGGER, and VIEW.
- schema
- An input argument of type VARCHAR(128) that specifies the name of the schema in which name exists.
- name
- An input argument of type VARCHAR(128) that specifies the name of the object for which dependencies are to be obtained.
Authorization
EXECUTE privilege on the DBMS_UTILITY module.
Examples
Example 1: The following
anonymous block finds dependencies on the table T1, and the function
FUNC1.
SET SERVEROUTPUT ON@
CREATE TABLE SCHEMA1.T1 (C1 INTEGER)@
CREATE OR REPLACE FUNCTION SCHEMA2.FUNC1( parm1 INTEGER )
SPECIFIC FUNC1
RETURNS INTEGER
BEGIN
RETURN parm1;
END@
CREATE OR REPLACE FUNCTION SCHEMA3.FUNC2()
SPECIFIC FUNC2
RETURNS INTEGER
BEGIN
DECLARE retVal INTEGER;
SELECT SCHEMA2.FUNC1(1) INTO retVal FROM SCHEMA1.T1;
END@
CALL DBMS_UTILITY.GET_DEPENDENCY('FUNCTION', 'SCHEMA2', 'FUNC1')@
CALL DBMS_UTILITY.GET_DEPENDENCY('TABLE', 'SCHEMA1', 'T1')@
This
example results in the following output:
SET SERVEROUTPUT ON
DB20000I The SET SERVEROUTPUT command completed successfully.
CREATE TABLE SCHEMA1.T1 (C1 INTEGER)
DB20000I The SQL command completed successfully.
CREATE OR REPLACE FUNCTION SCHEMA2.FUNC1( parm1 INTEGER )
SPECIFIC FUNC1
RETURNS INTEGER
BEGIN
RETURN parm1;
END
DB20000I The SQL command completed successfully.
CREATE OR REPLACE FUNCTION SCHEMA3.FUNC2()
SPECIFIC FUNC2
RETURNS INTEGER
BEGIN
DECLARE retVal INTEGER;
SELECT SCHEMA2.FUNC1(1) INTO retVal FROM SCHEMA1.T1;
END
DB20000I The SQL command completed successfully.
CALL DBMS_UTILITY.GET_DEPENDENCY('FUNCTION', 'SCHEMA2', 'FUNC1')
Return Status = 0
DEPENDENCIES ON SCHEMA2.FUNC1
------------------------------------------------------------------
*FUNCTION SCHEMA2.FUNC1()
* FUNCTION SCHEMA3 .FUNC2()
CALL DBMS_UTILITY.GET_DEPENDENCY('TABLE', 'SCHEMA1', 'T1')
Return Status = 0
DEPENDENCIES ON SCHEMA1.T1
------------------------------------------------------------------
*TABLE SCHEMA1.T1()
* FUNCTION SCHEMA3 .FUNC2()