Showing all procedure names called within the procedure in order of execution

  Kiến thức lập trình

I have 4 procedures, pr_x is my main procedure
and in

pr_y(v_first_name);
pr_z(v_number);

are called in order

CREATE OR REPLACE NONEDITIONABLE PROCEDURE pr_y AS
    v_first_name VARCHAR2(100);
    pr_t(v_first_name);


pr_t is called inside pr_y.

I want these procedures to be shown in the order they run, but I get the following result with this code.

WITH procedure_calls (called_procedure, depth) AS (
    SELECT 
        referenced_name AS called_procedure,
        1 AS depth
    FROM 
        all_dependencies
    WHERE 
        name = 'PR_X'
        AND type = 'PROCEDURE'
        AND referenced_type = 'PROCEDURE'
    UNION ALL
    SELECT 
        ad.referenced_name AS called_procedure,
        pc.depth + 1 AS depth
    FROM 
        all_dependencies ad
    INNER JOIN 
        procedure_calls pc ON ad.name = pc.called_procedure
    WHERE 
        ad.type = 'PROCEDURE'
        AND ad.referenced_type = 'PROCEDURE'
)
SELECT 
    called_procedure,
    depth,
    ROW_NUMBER() OVER (ORDER BY depth) AS execution_order
FROM 
    procedure_calls
WHERE 
    called_procedure LIKE 'PR%';

I tried this solution but not what i want it

CALLED_PROCEDURE DEPTH EXECUTION_ORDER
PR_Y 1 1
PR_Z 1 2
PR_T 2 3

I want this result

CALLED_PROCEDURE DEPTH EXECUTION_ORDER
PR_Y 1 1
PR_T 2 2
PR_Z 1 3

LEAVE A COMMENT