Oracle 获取某个包依赖的所有对象包括其子对象

 Demo

 

  
 ---创建表 
  create table  cux_common_imports_temp(
    attribute1  varchar2(2000),
    attribute2  varchar2(2000)
  );

 ---DEMO


  declare
    -- 获取相应的 某个程序包 所需要应用的包
    cursor p_cur(p_name varchar2) is
      select dd.name, dd.type, dd.referenced_name, dd.referenced_type
        from dba_dependencies dd
       where 1 = 1
         and dd.referenced_type in
             ('PACKAGE', 'SYNONYM', 'TABLE', 'SEQUENCE')
         and dd.type in ('PACKAGE', 'PACKAGE BODY')
         AND dd.name = p_name;
  
    cursor p_temp(l_level number) is
      select attribute1, attribute2
        from cux_common_imports_temp
       where attribute2 = l_level;
  
    p_root_name varchar2(30);
    p_level     number := 0;
    p_count     number := 0;
  
  begin
    --将根节点 放入到表中
    p_root_name := 'CUX_COMMON_PUB';
    insert into cux_common_imports_temp
      (attribute1, attribute2)
    values
      (p_root_name, p_level);
    p_count := 1;
    --循环这一层的 节点 并获得其子节点
  
    while p_count != 0 loop
    
      for p_loop in p_temp(p_level) loop
        --下一层
        p_level     := p_level + 1;
        p_root_name := p_loop.attribute1;
        for p_rec in p_cur(p_root_name) loop
          if p_rec.referenced_type = 'PACKAGE' then
            --如果表内 没有这个程序就 加入到 临时表中
            select count(*)
              into p_count
              from cux_common_imports_temp
             where attribute1 = p_rec.referenced_name;
          
            if p_count = 0 then
              insert into cux_common_imports_temp
                (attribute1, attribute2)
              values
                (p_rec.referenced_name, p_level);
            end if;
            --输出相应的 子节点信息
            dbms_output.put_line(p_level || '-Name:' ||
                                 p_rec.referenced_name || '-Type:' ||
                                 p_rec.referenced_type);
          
          end if;
        end loop;
      end loop;
      --获取 该层是否为空
      select count(*)
        into p_count
        from cux_common_imports_temp
       where attribute2 = p_level;
    
    end loop;
  
  end;