Finding All Generations Of An Object’s Children

Say you have an object, such as a table. What are all objects are dependent on it? That is, all generations. Children, Grandchildren. Great-Grandchildren. Great-Great-Grandchildren. Etc. ??

If you drop that object, all generations of its children will become invalid.

The classic case is a table. There are generations of objects that are built referencing them.

Table
  View
    Package
      Function

Table
  Trigger

etc.

This script will be useful for:
– Determining the Order Of Operations when building objects.
– Determining Multiple dependencies
– Assessing the impact of changing a table structure, or dropping a table.

Output will look something like:

@fchild_tree
Enter the exact object name to find info about:  per_addresses

THE_OBJECT
----------------------------------------------------------------------------------------------------
SYNONYM   APPS.PER_ADDRESSES    92203
SYNONYM   PUBLIC.PER_ADDRESSES    85116
TABLE   HR.PER_ADDRESSES    38985

3 rows selected.

From above, enter the exact object_id:  38985

FINDING_ALL_CHILDREN_FOR
----------------------------------------------------------------------------------------------------
TABLE   HR.PER_ADDRESSES   38985

1 row selected.

CHILD_LVL_OBJID_ROWNUM
----------------------------------------------------------------------------------------------------
  SYNONYM  APPS.PER_ADDRESSES   1   92203   1
...

    PACKAGE  APPS.PER_ADD_SHD   2   245641   636
      SYNONYM  PUBLIC.PER_ADD_SHD   3   85764   637
      PACKAGE  APPS.PER_ADD_FLEX   3   245640   638
        SYNONYM  PUBLIC.PER_ADD_FLEX   4   85669   639
        PACKAGE BODY  APPS.PER_ADD_BUS   4   264010   640
        PACKAGE BODY  APPS.PER_ADD_FLEX   4   273034   641
      PACKAGE  APPS.PER_ADD_DEL   3   245643   642
        SYNONYM  PUBLIC.PER_ADD_DEL   4   85766   643
        PACKAGE BODY  APPS.HR_H2PI_PERSON_UPLOAD   4   255974   644
        PACKAGE BODY  APPS.GHR_CORR_CANC_SF52   4   267035   645
        PACKAGE BODY  APPS.PER_ADD_DEL   4   273032   646
        PACKAGE BODY  APPS.IRC_PURGE_OLD_DATA_PKG   4   719001   647
...

  TRIGGER  APPS.PERADDRESSES_488I_DYT   1   3259153   1323
  TRIGGER  APPS.PERADDRESSES_131U_DYT   1   3259155   1324

1176 rows selected.

———————–

Delving a little deeper, the output says that PACKAGE APPS.PER_ADD_SHD refers to PER_ADDRESSES. Looking at the source code for the package, we get:

Select text
from all_source
where name = rtrim(ltrim(upper( 'PER_ADD_SHD' )))
and type = 'PACKAGE BODY'
and UPPER(TEXT) LIKE '%PER_ADDRESSES%'
order by owner, name, line

TEXT
---------------------------------------------------------------------
If (p_constraint_name = 'PER_ADDRESSES_PK') Then
from per_addresses
from per_addresses
hr_utility.set_message_token('TABLE_NAME', 'per_addresses');

So, if you were creating objects, PER_ADDRESSES must be created first, then create, PACKAGE APPS.PER_ADD_SHD.

———————–

Script: fchild_tree.sql

/*

fchild_tree.sql

Script to find all the successive generations of children of an object
By Rodger Lepinsky

Will display for example:

Table
  View
    Package

Will show the owner.object_name, level from the child, object_id, and the rownum
on one line for sqlplus

If you prefer, change the output to:

select object_name
, the_level
, object_id
, the_rownum 

*/

accept ls_object_name prompt "Enter the exact object name to find info about:  " ;

select     object_type  ||  '   '  ||  owner || '.' || object_name   ||
               '    ' || object_id
          as the_object
from all_objects
where upper(object_name) = trim(upper('&ls_object_name'))
order by object_type, owner, object_name
/

accept ls_obj_id prompt "From above, enter the exact object_id:  " ;

select  ao.object_type || '   ' ||   ao.owner || '.' || ao.object_name ||
           '   '  || ao.object_id
      as Finding_all_children_for
FROM  all_objects  ao
where    ao.object_id   =  &ls_obj_id
/

select object_name
   || '   '    ||  the_level
   || '   '    ||  object_id
   || '   '    ||  the_rownum
    as  child_lvl_objid_rownum
from
   (
   select  object_name     as object_name
   , OBJECT_ID
   , the_level
   , min (the_rownum)  as the_rownum
   from
       (  /* By Rodger Lepinsky */
       SELECT     LPAD(' ', 2*(LEVEL-1)) || '  '  ||
       ao.OBJECT_type || '  '  ||  ao.owner || '.' ||
       ao.object_name  || '   ' || ao.OBJECT_ID || '  ' || rownum
                AS THE_TREE
       , LPAD ( ' ', 2*(LEVEL-1) ) || '  '  ||
         ao.OBJECT_type || '  ' ||
         ao.owner ||  '.' ||
         ao.object_name
              as object_name
       , ao.owner ||  '.' || ao.object_name
            as object_name2
       , ao.OBJECT_type
       , ao.OBJECT_ID
       , ao.owner
       , rownum as the_rownum
       , level as the_level
       FROM               PUBLIC_DEPENDENCY   pd
       join         all_objects  ao
            on    ao.object_id   = pd.object_id
/*       where ao.owner NOT in  ( 'SYS', 'SYSTEM', 'PUBLIC' )    */
       START WITH         pd.REFERENCED_OBJECT_ID =  &ls_obj_id
       CONNECT BY PRIOR   pd.OBJECT_ID = pd.REFERENCED_OBJECT_ID
      )
   group by object_name, object_id, the_level
   )
order by the_rownum
/

-----------------------
 The final output excludes objects owned by: SYS, SYSTEM, PUBLIC. UnComment that line if you want to include them. Although, it would be highly unusual to create a SYSTEM object on top of a regular schema object.

Note: it’s best to run this script as a user with the privilege: Select Any Table. Otherwise, you may not see all the objects.

To find the tree of all the -parents- of an object, see my previous post.