Oracle hierarchical recursive query stop at cycle and continue with other package name

Question

I am a little struggling at the moment with the following problem.

I want to know which package calls which packages. The table is not hierarchical. Its the table user_dependencies.

The code so far:

CREATE OR REPLACE PACKAGE object_x is
 
  type ObjectRec is record(
    dName      varchar2(250),
    level       number
  );
 
  type ObjectTemp is table of ObjectRec;
 
  function Referenced(dname VARCHAR2, level NUMBER, maxl NUMBER) return ObjectTemp pipelined;
 
end;
/
 
CREATE OR REPLACE PACKAGE BODY object_x is
 
  function Referenced(dname VARCHAR2, level NUMBER, maxl NUMBER) return ObjectTemp pipelined is
    rData  ObjectRec;

  begin
 
    if level >= maxl then
      return;
    end if;
  
    if level = 1 then
      rData.dName := name;
      rData.Level := maxl;
      pipe row(rData);
    end if;
 
    for r in ( 
      select referenced_name
      from user_dependencies
      where name = upper(dname)
      and type = 'PACKAGE BODY'
      and referenced_type = 'PACKAGE'
      and referenced_name != UPPER(dname)
      and referenced_name != name 
    ) 

      loop
      rData.dName := LPAD(' ', 3, ' ') || r.Referenced_name;
      rData.level := level+1;
      pipe row(rData);
      rData.Name := r.Referenced_name;
 
 
     for r2 in (select * from table(Referenced(rData.Name, level + 1, maxl))) loop
        rData.Name := LPAD(' ', 3, ' ') || r2.dName;
        rData.Level := r2.Level;
        pipe row(rData);
        null;
      end loop;
 
    end loop;

RESULT:

     Level Dname
---------- --------------------------------------------------------------------------------
         1 PAC1
         2    PAC2
         2    PAC3
         2    PAC4
         2    PAC5
         3       PAC6
         3       PAC2
         3       PAC7
         3       PAC8
         4          PAC9
         4          PAC10
         5             PAC6
         5             PAC11
         5             PAC3
         5             PAC9
         5             PAC12
         5             PAC6
         5             PAC3
         5             PAC9
         5             PAC4
         5             PAC8
         3       PAC10
         4          PAC6
         4          PAC11
         4          PAC3
         4          PAC9
         4          PAC12
         4          PAC4

EXPECTED RESULT:

     Level Dname
---------- --------------------------------------------------------------------------------
         1 PAC1
         2    PAC2
         2    PAC3
         2    PAC4
         2    PAC5
         3       PAC6
         3       PAC2
         3       PAC7
         3       PAC8
         4          PAC9
         4          PAC10
         5             PAC6
         5             PAC11
         5             PAC3
         5             PAC9
         5             PAC12
         5             PAC6
         5             PAC3
         5             PAC9
         5             PAC4
         5             PAC8
         3       PAC10 LOOP!!!!
          -----BREAK------
         CONTINUE WITH OTHER PACKAGES……….. 

Thanks for any advice.

Answer 1

Ultimately, yes, it is possible to do what you want.

One way to do this is to use a nested table to store the chain of dependent packages that you've built up through the current chain of recursive function calls. You can use the MEMBER OF operator with this nested table to check whether a package is already in this chain (and so you've found a loop). If not, you can call your Referenced function recursively, but adding an extra item to this table for the current package.

I made these changes to your code and ended up with the following:

CREATE OR REPLACE TYPE varchar2_table AS TABLE OF VARCHAR2(250);
/

CREATE OR REPLACE PACKAGE object_x is
 
  type ObjectRec is record(
    dName      varchar2(250),
    dLevel     number
  );
 
  type ObjectTemp is table of ObjectRec;
 
  function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER) return ObjectTemp pipelined;
  function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER, pkgs_so_far IN varchar2_table) return ObjectTemp pipelined;

end;
/
 
CREATE OR REPLACE PACKAGE BODY object_x is

  function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER) return ObjectTemp pipelined is
    rData  ObjectRec;
  begin
    for r2 in (select * from table(Referenced(dname, dlevel, maxl, varchar2_table(dname))))
    loop
      rData.dName := r2.dName;
      rData.dLevel := r2.dLevel;
      pipe row(rData);
    end loop;
  end;
 
  function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER, pkgs_so_far IN varchar2_table) return ObjectTemp pipelined is
    rData  ObjectRec;
    new_pkgs_so_far varchar2_table;
    loop_detected boolean;
  begin
 
    if dlevel >= maxl then
      return;
    end if;
  
    if dlevel = 1 then
      rData.dName := dname;
      rData.dLevel := 1;
      pipe row(rData);
    end if;
 
    for r in ( 
      select referenced_name
      from user_dependencies
      where name = upper(dname)
      and type = 'PACKAGE BODY'
      and referenced_type = 'PACKAGE'
      and referenced_name != UPPER(dname)
      and referenced_name != name 
    ) 

    loop
      loop_detected := r.referenced_name MEMBER OF pkgs_so_far;
    
      rData.dName := LPAD(' ', 3, ' ') || r.Referenced_name;
      if loop_detected then
        rData.dName := rData.dName || ' --- LOOP';
      end if;
      rData.dLevel := dLevel + 1;
      pipe row(rData);
 
      if not loop_detected THEN
        new_pkgs_so_far := pkgs_so_far;
        new_pkgs_so_far.EXTEND(1);
        new_pkgs_so_far(new_pkgs_so_far.COUNT) := r.referenced_name;
        for r2 in (select * from table(Referenced(r.referenced_name, dLevel + 1, maxl, new_pkgs_so_far))) loop
          rData.dName := LPAD(' ', 3, ' ') || r2.dName;
          rData.dLevel := r2.dLevel;
          pipe row(rData);
        end loop;
      end if;
 
    end loop;
  end;
end;
/

I've modified your pipelined function to add the pkgs_so_far parameter, which contains the table of packages currently encountered. I've added another pipelined function with the same signature as your original function, and this just calls the other one with the table of packages containing only the given package.

In the modified function, we now use the MEMBER OF operator to check whether we've already come across the current package. If we have, we can add an extra --- LOOP marker to indicate this. If we haven't come across this package before, we add the package name to the table and continue to recursively examine its references. Note that assigning the table in the line new_pkgs_so_far := pkgs_so_far; appears to set new_pkgs_so_far to a copy of the table in pkgs_so_far, so we can mutate new_pkgs_so_far without this affecting pkgs_so_far.

However, there were also a number of other issues I cleared up with your code:

• The package body appears to be missing the final two end; lines, one to end the procedure and one to end the package body itself.
• Your code contains some references to the name field of your record, but it is named dName. I've tidied these all up to use dName.
• Oracle didn't seem to like the name level being used, so I changed it to dLevel. (LEVEL is an Oracle keyword, it's used in hierarchical SQL.)
• I'm not sure it's correct to pipe out a row with dLevel set to maxl in the case where the variable dlevel is 1, so I've changed this to 1 instead.
• When calling your References function recursively, I've changed the first parameter to be r.referenced_name, as then we don't need to assign to rData.dName.
• I've removed an unnecessary null; statement within the loop that iterates through a recursive call to your procedure.

I did also note that you've written LPAD(' ', 3, ' ') twice, but I'm not sure why: this is a long-winded way of writing ' ', where there are three spaces in the string.

Some of these errors may have arisen because you anonymised your code before posting it here but you didn't check whether the code you posted compiles before posting. In future, please ensure your code compiles, as otherwise anyone who wishes to help you will have to first fix your compilation errors.

I also don't have all of your packages (and I suspect in reality they aren't named PAC1, PAC2 and so on), so I created three packages PAC1 to PAC3. PAC2 and PAC3 both reference each other and PAC1, and the output I got was as follows:

SQL> COLUMN dname FORMAT a30
SQL> select * from table(object_x.referenced('PAC2', 1, 10));

DNAME                              DLEVEL
------------------------------ ----------
PAC2                                    1
   PAC1                                 2
   PAC3                                 2
      PAC1                              3
      PAC2 --- LOOP                     3