Иерархический рекурсивный запрос Oracle останавливается на цикле и продолжается с другим именем пакета

В конце концов, да, можно делать то, что вы хотите.

Один из способов сделать это — использовать вложенную таблицу для хранения цепочки зависимых пакетов, которую вы создали в текущей цепочке рекурсивных вызовов функций. Вы можете использовать оператор MEMBER OF с этой вложенной таблицей, чтобы проверить, находится ли пакет уже в этой цепочке (и, следовательно, вы нашли петлю). Если нет, вы можете вызвать свою функцию Referenced рекурсивно, но добавив дополнительный элемент в эту таблицу для текущего пакета.

Я внес эти изменения в ваш код и получил следующее:

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;
/

Я изменил вашу конвейерную функцию, добавив параметр pkgs_so_far, который содержит таблицу пакетов, которые в настоящее время встречаются. Я добавил еще одну конвейерную функцию с той же сигнатурой, что и ваша исходная функция, и она просто вызывает другую с таблицей пакетов, содержащей только данный пакет.

В измененной функции мы теперь используем оператор MEMBER OF, чтобы проверить, встречались ли мы уже с текущим пакетом. Если у нас есть, мы можем добавить дополнительный маркер --- LOOP, чтобы обозначить это. Если мы не встречали этот пакет раньше, мы добавляем имя пакета в таблицу и продолжаем рекурсивно проверять его ссылки. Обратите внимание, что назначение таблицы в строке new_pkgs_so_far := pkgs_so_far;, по-видимому, устанавливает new_pkgs_so_far в копию таблицы в pkgs_so_far, поэтому мы можем изменить new_pkgs_so_far, не затрагивая при этом pkgs_so_far.

Однако был также ряд других проблем, которые я прояснил с помощью вашего кода:

• В теле пакета отсутствуют последние две строки end;: одна для завершения процедуры, а другая для завершения самого тела пакета.
• Ваш код содержит некоторые ссылки на поле name вашей записи, но оно называется dName. Я привел их в порядок, чтобы использовать dName.
• Похоже, Oracle не понравилось используемое имя level, поэтому я изменил его на dLevel. (LEVEL — это ключевое слово Oracle, оно используется в иерархическом SQL.)
• Я не уверен, что правильно передавать строку с dLevel, установленным на maxl, в случае, когда переменная dlevel равна 1, поэтому вместо этого я изменил это на 1.
• При рекурсивном вызове вашей функции References я изменил первый параметр на r.referenced_name, так как тогда нам не нужно назначать rData.dName.
• Я удалил ненужный оператор null; в цикле, который повторяет рекурсивный вызов вашей процедуры.

Я также заметил, что вы написали LPAD(' ', 3, ' ') дважды, но я не знаю, почему: это многословный способ написания ' ', где в строке три пробела.

Некоторые из этих ошибок могли возникнуть из-за того, что вы анонимизировали свой код перед его публикацией здесь, но не проверили, компилируется ли опубликованный вами код перед публикацией. В будущем убедитесь, что ваш код компилируется, иначе любой, кто захочет вам помочь, должен будет сначала исправить ваши ошибки компиляции.

У меня тоже нет всех ваших пакетов (и я подозреваю, что на самом деле они не названы PAC1, PAC2 и так далее), поэтому я создал три пакета от PAC1 до PAC3. PAC2 и PAC3 ссылаются друг на друга и PAC1, и результат, который я получил, был следующим:

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