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()