January 28, 2020

Get recursive dependencies between objects

We often get invalidated objects and would like to know the recursive dependency of these objects to other objects. This script provides a listing of how objects depend on other given objects such as packages, package bodies or procedures recursively.

Rem    NAME
Rem      rdeptree.sql - Show objects which a given object is recursively 
Rem      dependent on 
Rem      This procedure, view and temp table will allow you to see all
Rem      objects that a given object recursively depends on.
Rem      Note: you will only see objects for which you have permission.
Rem      Examples:
Rem        execute rdeptree_fill('procedure', 'scott', 'billing');
Rem        select * from rdeptree order by seq#;
Rem        execute rdeptree_fill('table', 'scott', 'emp');
Rem        select * from rdeptree order by seq#;
Rem        execute rdeptree_fill('package body', 'scott', 'accts_payable');
Rem        select * from rdeptree order by seq#;
Rem        A better way to display this information than:
Rem		select * from rdeptree order by seq#;
Rem	   is
Rem             select * from irdeptree;
Rem        This shows the dependency relationship via indenting.  Notice
Rem        that no order by clause is needed with ideptree.
Rem    NOTES
Rem      Run this script once for each schema that needs this utility.

drop sequence rdeptree_seq
create sequence rdeptree_seq cache 200 /* cache 200 to make sequence faster */
drop table rdeptree_temptab
create table rdeptree_temptab
  object_id            number,
  referenced_object_id number,
  nest_level           number,
  seq#                 number      
create or replace procedure rdeptree_fill (type char, schema char, name char) is
  obj_id number;
  delete from rdeptree_temptab;
  select object_id into obj_id from all_objects
    where owner        = upper(rdeptree_fill.schema)
    and   object_name  = upper(rdeptree_fill.name)
    and   object_type  = upper(rdeptree_fill.type);
  insert into rdeptree_temptab
    values(0, obj_id, 0, 0);
  insert into rdeptree_temptab
    select object_id, referenced_object_id,
        level, rdeptree_seq.nextval
      from public_dependency
      connect by object_id = prior referenced_object_id
      start with object_id = rdeptree_fill.obj_id;
  when no_data_found then
    raise_application_error(-20000, 'ORU-10013: ' ||
      type || ' ' || schema || '.' || name || ' was not found.');

drop view rdeptree

set echo on

set echo off
create view rdeptree
  (nested_level, type, schema, name, seq#)
  select d.nest_level, o.object_type, o.owner, o.object_name, d.seq#
  from rdeptree_temptab d, all_objects o
  where d.referenced_object_id = o.object_id (+)

drop view irdeptree
create view irdeptree (dependencies)
  select lpad(' ',3*(max(nested_level))) || max(nvl(type, '<no permission>')
    || ' ' || schema || decode(type, NULL, '', '.') || name)
  from rdeptree
  group by seq# /* So user can omit sort-by when selecting from ideptree */