Get multiple results from the same table in a query using recursive

Question

I want to do this in a recursive query. I tried to do everything so that the question was clear. If you have any question, answer quickly.

I have 4 testing procedures: PRC_TEST1, PRC_TEST2, PRC_TEST3, prc_test4

Inside PRC_TEST1, PRC_TEST2 ... is executed and PRC_TEST2 is executed by prc_test3. and prc_test3 does prc_test4

create or replace procedure prc_test1
as

begin
prc_test2();
end;
/

create or replace procedure prc_test2
as

begin
prc_test3();
end;
/

create or replace procedure prc_test3
as

begin
prc_test4();
end;
/

create or replace procedure prc_test4
as

begin
null;
end;
/

I want to find out from a recursive query what happens with prc_test2, and at the same time I want to know which procedure is referenced .. the following sample queries will explain my result.

in the following queries, I check where prc_test2 refers.

select referenced_name from user_dependencies where name = 'PRC_TEST2'

prc_test3, PRC_test2 prc_test3 , prc_test3

referenced_name user_dependencies, name = 'PRC_TEST3'

, , prc_test4

, , prc_test2.. , ,

user_dependencies, referenced_name = 'PRC_TEST2'

prc_test1, , prc_test1

user_dependencies, referenced_name = 'PRC_TEST1' .

( ),

Answer 1

(, ) .

4 :

create or replace procedure proc4
as
begin
  null;
end;
/
create or replace procedure proc3
as
begin
  proc4;
end;
/
create or replace procedure proc2
as
begin
  proc3;
end;
/
create or replace procedure proc1
as
begin
  proc2;
end;
/

:

with dependencies(d_name, d_referenced_name) as(
  select nvl(d1.name, d2.referenced_name)
       , d1.referenced_name
   from user_dependencies d1
        full join user_dependencies d2
          on (d1.name = d2.referenced_name)
  where d1.type = 'PROCEDURE'
     or d2.type = 'PROCEDURE'
)
select concat( lpad('-', 3*level, '-')
             , d.d_name) as res 
  from dependencies d 
start with d.d_name = 'PROC1'
connect by d.d_name = prior d.d_referenced_name

:

RES
------------------
---PROC1
------PROC2
---------PROC3
------------PROC4

SQLFiddle

Answer 2

-

SELECT    
             t2.name AS parent_name,
             t1.name, 
             t1.REFERENCED_NAME as calling_to
         --  t2.REFERENCED_NAME AS p_reference
      FROM user_dependencies t1
 LEFT JOIN user_dependencies t2 ON t1.name = T2.REFERENCED_NAME
START WITH t1.name = 'PRC_TEST2' 
CONNECT BY PRIOR t1.NAME = T1.REFERENCED_NAME