The Parents And The Order Of Operations

When you build objects in a new environment, you need to build them in the right order of operations.

Until you have all the objects in place, you can’t create a procedure that references them all. And if those objects require more parent objects, they must be created first too. For example:

procedure reads
    view which reads a
       table which is composed of a
          type

—-

Recently I wrote in my other post about the parent and child dependencies. They give a lot of great information. But they only go one level in either direction. As you know, there can be many levels of objects.

What is the order of operations to build them? I’ve written some complex scripts here to find all the successive parents of an object.

The first script I wrote produced some odd results. Interestingly enough, objects can be repeated, a number of times. ie.

PARENT_LVL_OBJID_ROWNUM
------------------------------------------------------------
  PACKAGE BODY  APPS.HR_DELETE   1   278801   1
  PACKAGE BODY  APPS.HR_DELETE   1   278801   2
  PACKAGE BODY  APPS.HR_DELETE   1   278801   3
    SYNONYM  PUBLIC.USER_CATALOG   2   1167   4
      VIEW  SYS.USER_CATALOG   3   1166   5
      VIEW  SYS.USER_CATALOG   3   1166   6
        VIEW  SYS._CURRENT_EDITION_OBJ   4   3270113   7
        VIEW  SYS._CURRENT_EDITION_OBJ   4   3270113   8
  PACKAGE BODY  APPS.HR_DELETE   1   278801   9
    SYNONYM  PUBLIC.DBMS_SQL   2   2328   10
      PACKAGE  SYS.DBMS_SQL   3   2327   11
      PACKAGE  SYS.DBMS_SQL   3   2327   12
      PACKAGE  SYS.DBMS_SQL   3   2327   13
        PACKAGE  SYS.UTL_IDENT   4   3291213   14

...

Of course, this makes sense in some environments. One standard error trapping routine can be called by dozens, and even hundreds of packages and procedures.

What does seem odd, is that, in PUBLIC_DEPENDENCIES, the very same two objects can have multiple dependencies, as you can see here with SYS.USER_CATALOG and SYS._CURRENT_EDITION_OBJ.

————————————————————

This second script gives a distinct set of rows.

————————————————————

fparent_tree.sql

/*

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

Will display for example:

procedure
     view
       table
         type
     table
       view

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

If you prefer, change to:

select object_name
, the_level
, object_id
, the_rownum 

If creating objects in a new environment, start with the highest level first.
Change the order by to:
order by 1 - the_level, the_rownum

The script includes objects owned by:  SYS, SYSTEM, PUBLIC
Comment that line if you want to include them. 

*/

accept ls_obj_name prompt "Enter the exact object name:  " ;

select  ao.object_type || '  '  || ao.OWNER || '.' ||
        ao.OBJECT_NAME  as object
, max (pd.OBJECT_ID) as object_id
from  all_objects  ao
join PUBLIC_DEPENDENCY pd
   on pd.object_id  = ao.object_id
join  all_objects  ao2
   on  ao2.object_id  = pd.REFERENCED_OBJECT_ID
where  ao.OBJECT_NAME = upper(  '&ls_obj_name' )     /* parent */
and  ao2.OWNER not IN ('SYS', 'SYSTEM')
group by ao.object_type || '  '  || ao.OWNER || '.' ||
        ao.OBJECT_NAME
/

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

select object_name
   || '   '    ||  the_level
   || '   '    ||  object_id
   || '   '    ||  the_rownum
    as  parent_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.OBJECT_ID =  &ls_obj_id
       CONNECT BY PRIOR   pd.REFERENCED_OBJECT_ID = pd.OBJECT_ID
      )
   group by object_name, object_id, the_level
   )
order by the_rownum
/

Here is some output:

PARENT_LVL_OBJID_ROWNUM
-------------------------------------------------------------------------
  PACKAGE BODY  APPS.HR_DELETE   1   278801   1
    SYNONYM  PUBLIC.USER_CATALOG   2   1167   4
      VIEW  SYS.USER_CATALOG   3   1166   5
        VIEW  SYS._CURRENT_EDITION_OBJ   4   3270113   7
    SYNONYM  PUBLIC.DBMS_SQL   2   2328   10
      PACKAGE  SYS.DBMS_SQL   3   2327   11
        PACKAGE  SYS.UTL_IDENT   4   3291213   14
    SYNONYM  APPS.FND_LOOKUP_VALUES   2   90783   16
    SYNONYM  APPS.FND_LOOKUP_TYPES   2   90784   18
    SYNONYM  APPS.FND_PROFILE_OPTION_VALUES   2   91033   20
    SYNONYM  APPS.FND_PROFILE_OPTIONS   2   91036   22
...

    SYNONYM  APPS.PER_APPLICATIONS   2   92201   138
    SYNONYM  APPS.PER_ADDRESSES   2   92203   140
      TABLE  HR.PER_ADDRESSES   3   38985   141
        TYPE  MDSYS.SDO_ORDINATE_ARRAY   4   24452   143
        TYPE  MDSYS.SDO_ELEM_INFO_ARRAY   4   24453   145
        TYPE  MDSYS.SDO_GEOMETRY   4   24454   147
          TYPE  MDSYS.SDO_POINT_TYPE   5   24451   149
          TYPE  MDSYS.SDO_ORDINATE_ARRAY   5   24452   151
          TYPE  MDSYS.SDO_ELEM_INFO_ARRAY   5   24453   153
    SYNONYM  APPS.PER_ABS_ATTENDANCE_REASONS   2   92204   155
    SYNONYM  APPS.PER_ABSENCE_ATTENDANCE_TYPES   2   92205   157-------------------

----

——————-

At one place I worked at, there was a number of coworker who, who had 10 or 15 years of “experience” with Oracle. One week, there was a big migration of objects to the production environment, with many objects to be created. An “experienced” coworker, struggled with the creation script over 2 or 3 days! Later I realized that the reason she struggled so much, was that she actually wrote the script using guessing, and trial and error. She still did not know how to query the data dictionary!

—-

So, this script should be very useful in some environments!