Get Installation Sequence of Oracle Objects

Ok, I have a complex recursion problem. I want to get a dependecy installation sequence of all of my objcts (all_objects table) in my Oracle 11g database.

First I have created a view holding all dependencies

create or replace 
view REALLY_ALL_DEPENDENCIES as
select * 
  from ALL_DEPENDENCIES
union
select owner, index_name, 'INDEX', table_owner, table_name, table_type, null, null
  from all_indexes
union
select p.owner, p.table_name, 'TABLE', f.owner, f.table_name, 'TABLE', null, null
  from all_constraints p
  join all_constraints f 
    on  F.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME 
    and F.CONSTRA开发者_运维问答INT_TYPE = 'R'
    and p.constraint_type='P'
;
/

EDIT

I have tried do concate all dependencies by using this function:

create 
 or replace
function dependency(
   i_name varchar2
  ,i_type varchar2
  ,i_owner varchar2
  ,i_level number := 0
  ,i_token clob := ' ') return clob
is
  l_token clob := i_token;
  l_exist number := 0;
begin
  select count(*) into l_exist 
    from all_objects
    where   object_name  = i_name
      and   object_type  = i_type
      and   owner = i_owner;  

  if l_exist > 0 then
    l_token := l_token || ';' || i_level || ';' || 
      i_name  || ':' || i_type || ':' || i_owner;
  else
    -- if not exist function recursion is finished
    return l_token;
  end if;

  for tupl in (
    select distinct
       referenced_name
      ,referenced_type
      ,referenced_owner
      from REALLY_ALL_DEPENDENCIES 
      where name  = i_name
      and   type  = i_type
      and   owner = i_owner
    )
  loop
   -- if cyclic dependency stop and shout!
    if i_token like '%' || tupl.referenced_name || ':' || tupl.referenced_type || ':' || tupl.referenced_owner || '%' then
      select count(*) into l_exist 
        from REALLY_ALL_DEPENDENCIES
        where   name  = tupl.referenced_name 
          and   type  = tupl.referenced_type 
          and   owner =  tupl.referenced_owner;  
      if  l_exist > 0 then
        return '!!!CYCLIC!!! (' || i_level || ';' || tupl.referenced_name || ':' || tupl.referenced_type || ':' || tupl.referenced_owner || '):' || l_token;
      end if;
    end if;

    -- go into recursion
    l_token := dependency(
       tupl.referenced_name
      ,tupl.referenced_type
      ,i_owner /* I just want my own sources */
      ,i_level +1
      ,l_token);
  end loop;

  -- no cyclic condition and loop is finished
  return l_token;
end;
/

And I can query through

select
   object_name
  ,object_type
  ,owner
  ,to_char(dependency(object_name, object_type, owner)) as dependecy
  from all_objects 
  where owner = 'SYSTEM'
;

Ok, maybe it is something like "cheating" but you can not do cyclic dependencies at creation time. So at least as a human beeing I am only able to create one object after another :-) And this sequence should be "reverse engineer able".

Now I am more interested in a solution than before ;-) And it is still about the tricky part ... "How can I select all soures from a schema orderd by its installation sequence (dependent objects list prior the using object)"? It is just some kind of sorting problem, insn't it?

Usually you "cheat" by creating the objects in a particular order. For example, you might make sequences first (they have zero dependencies). Then you might do tables. After that, package specs, then package bodies, and so on.

Keep in mind that it is possible to have cyclic dependencies between packages, so there are cases where it will be impossible to satisfy all dependencies at creation anyway.

What's the business case here? Is there a real "problem" or just an exercise?

EDIT

The export tool we use exports objects in the following order:

• Database Links
• Sequences
• Types
• Tables
• Views
• Primary Keys
• Indexes
• Foreign Keys
• Constraints
• Triggers
• Materialized Views
• Materialized View Logs
• Package Specs
• Package Bodies
• Procedures
• Functions

At the end, we run the dbms_utility.compile_schema procedure to make sure everything is valid and no dependencies are missed. If you use other object types than these, I'm not sure where they'd go in this sequence.

Ok, I had some time to look at the job again and I want to share the results. Maybe anotherone comes across this thread searching for a solution. First of all I did the SQLs as SYS but I think you can do it in every schema using public synonyms.

The Procedure "exec obj_install_seq.make_install('SCOTT');" makes a clob containing a sql+ compatible sql file, assuming your sources are called "object_name.object_type.sql". Just spool it out.

Cheers Chris

create global temporary table DEPENDENCIES on commit delete rows as 
select * from ALL_DEPENDENCIES where 1=2 ;
/

create global temporary table install_seq(
     idx   number
    ,seq   number
    ,iter  number
    ,owner varchar2(30)
    ,name  varchar2(30)
    ,type  varchar2(30)
) on commit delete rows;
/

create global temporary table loop_chk(
     iter  number
    ,lvl   number
    ,owner varchar2(30)
    ,name  varchar2(30)
    ,type  varchar2(30)
) on commit delete rows;
/

create or replace package obj_install_seq is
  procedure make_install(i_schema varchar2 := 'SYSTEM');
end;
/

create or replace package body obj_install_seq is
  subtype install_seq_t is install_seq%rowtype;
  type dependency_list_t is table of DEPENDENCIES%rowtype;

  procedure set_list_data(i_schema varchar2 := user)
  is
    l_owner varchar2(30) := i_schema;
  begin
    -- collect all dependencies
    insert into DEPENDENCIES 
      select *  
        from (select * 
              from ALL_DEPENDENCIES
             where owner = l_owner
               and referenced_owner = l_owner
             union
            select owner, index_name, 'INDEX', table_owner, table_name, table_type, null, null
              from all_indexes
             where owner = l_owner
               and table_owner = l_owner
             union
             select p.owner, p.table_name, 'TABLE', f.owner, f.table_name, 'TABLE', null, null
              from all_constraints p
              join all_constraints f 
                on  F.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME 
               and F.CONSTRAINT_TYPE = 'R'
               and p.constraint_type='P'
               and p.owner = f.owner
             where p.owner = l_owner
           ) all_dep_tab;

     -- collect all objects
     insert into install_seq   
     select rownum, null,null, owner, object_name, object_type
       from (select distinct owner, object_name, object_type, created
               from all_objects
              where owner = l_owner
              order by created) objs;
  end;

  function is_referencing(
      i_owner varchar2
     ,i_name varchar2
     ,i_type varchar2
     ,i_iter number
     ,i_level number := 0
  ) return boolean
  is
    l_cnt number;
  begin
    select count(*) into l_cnt 
      from loop_chk 
     where name  = i_name
       and owner = i_owner
       and type  = i_type
       and iter  = i_iter
       and lvl   < i_level;

    insert into loop_chk values(i_iter,i_level,i_owner,i_name,i_type);

    if l_cnt > 0 then
      return true;
    else 
      return false;
    end if;
  end;

  procedure set_seq(
    i_owner varchar2
   ,i_name varchar2
   ,i_type varchar2
   ,i_iter number
   ,i_level number := 0)
  is
    -- l_dep all_dependencies%rowtype;
    l_idx number;
    l_level number := i_level +1;
    l_dep_list dependency_list_t;
    l_cnt number;
  begin
    -- check for dependend source
    begin
      select * bulk collect into l_dep_list
        from dependencies 
       where name  = i_name
         and owner = i_owner
         and type  = i_type;

      if l_dep_list.count <= 0 then
        -- recursion finished
        return;
      end if;
    end;

    for i in 1..l_dep_list.count loop
      if is_referencing(   
         l_dep_list(i).referenced_owner
        ,l_dep_list(i).referenced_name
        ,l_dep_list(i).referenced_type
        ,i_iter
        ,i_level
      ) then
        -- cyclic dependecy
        update install_seq
           set seq = 999
              ,iter = i_iter
         where name  = l_dep_list(i).referenced_name
           and owner = l_dep_list(i).referenced_owner
           and type  = l_dep_list(i).referenced_type; 
      else
        --chek if sequence is earlier
        select count(*) into l_cnt 
          from install_seq 
         where name  = l_dep_list(i).referenced_name
           and owner = l_dep_list(i).referenced_owner
           and type  = l_dep_list(i).referenced_type
           and seq   > l_level *-1;

        -- set sequence      
        if l_cnt > 0 then
          update install_seq
             set seq = l_level *-1
                ,iter = i_iter
           where name  = l_dep_list(i).referenced_name
             and owner = l_dep_list(i).referenced_owner
             and type  = l_dep_list(i).referenced_type; 
        end if;

        -- go recusrion  
        set_seq(
           l_dep_list(i).referenced_owner
          ,l_dep_list(i).referenced_name
          ,l_dep_list(i).referenced_type
          ,i_iter + (i-1)
          ,l_level
      );

      end if;          
    end loop;
  end;


  function get_next_idx return number
  is
    l_idx number;
  begin
    select min(idx) into l_idx
      from install_seq
     where seq is null;

   return l_idx;
  end;

  procedure make_install(i_schema varchar2 := 'SYSTEM') 
  is
    l_obj install_seq_t;
    l_idx number;
    l_iter number := 0;
    l_install_clob clob := chr(10);
  begin
    set_list_data(i_schema);
    l_idx := get_next_idx;

    while l_idx is not null loop
      l_iter := l_iter +1;

      select * into l_obj from install_seq where idx = l_idx;
      update install_seq set iter = l_iter where idx = l_idx;
      update install_seq set seq = 0 where idx = l_idx;
      set_seq(l_obj.owner,l_obj.name,l_obj.type,l_iter);

      l_idx := get_next_idx;
    end loop;

    for tupl in ( select * from install_seq order by seq, iter, idx ) loop
      l_install_clob := l_install_clob || '@' || 
        replace(tupl.name,' ' ,'') || '.' || 
        replace(tupl.type,' ' ,'') || '.sql' || 
        chr(10);
    end loop;

    l_install_clob := l_install_clob || 
      'exec dbms_utility.compile_schema(''' || upper(i_schema) || ''');';

    -- do with the install file what you want
    DBMS_OUTPUT.PUT_LINE(dbms_lob.substr(l_install_clob,4000));
  end;
end;
/