The GET_DEPENDENCY procedure provides the capability to list all objects that are dependent upon the given object.
EXECUTE privilege on the DBMS_UTILITY module.
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')@
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()