-- deps.sql
-- Find dependencies for specified object
-- William Robertson 2004, www.williamrobertson.net
--:vim:set ts=8

set term off
store set sqlplus_settings.sql replace
set autoprint off term off

-- Use DBA_ views if possible:
col dict_level new_value dict_level
SELECT 'ALL' AS dict_level FROM dual;
SELECT 'DBA' AS dict_level FROM dba_users WHERE rownum = 1;
-- Now &DICT_LEVEL contains either 'ALL' or 'DBA'
col dict_level clear

col name format a30

var arg    VARCHAR2(100)
exec :arg := UPPER('&1')
var owner  VARCHAR2(30)
var object VARCHAR2(30)

-- Poor man's name tokenizer:
BEGIN
	IF :arg LIKE '%.%'
	THEN
		:owner := SUBSTR(:arg,1,INSTR(:arg,'.') -1);
		:object := SUBSTR(:arg,INSTR(:arg,'.') +1);
	ELSE
		:owner := SYS_CONTEXT('userenv','current_schema');
		:object := :arg;
	END IF;
END;
/

@sqlplus_settings.sql
set term on

break on dependency_type skip1 nodup
col dependency_type format a14 hea "Dependency|type"

SELECT 'Referenced by' AS dependency_type
     , owner
     , name
     , type
FROM   &DICT_LEVEL._dependencies
WHERE  referenced_owner = :owner
AND    referenced_name LIKE :object ESCAPE '\'
AND    NOT (name = :object AND owner = :owner)
UNION
SELECT 'References'
     , referenced_owner
     , referenced_name
     , referenced_type
FROM   &DICT_LEVEL._dependencies
WHERE  owner = :owner
AND    name LIKE :object ESCAPE '\'
AND    NOT (referenced_name = :object AND referenced_owner = :owner)
ORDER BY 1,2,3,4
/