Forum Stats

  • 3,852,382 Users
  • 2,264,100 Discussions
  • 7,905,056 Comments

Discussions

Finding out all procedures , functions in a tree like structure in sequence

2»

Answers

  • Unknown
    edited Sep 6, 2012 10:29PM
    See Example 8-3 USAGE_CONTEXT_ID and USAGE_ID. That example doesn't show line numbers (it could) but it shows the hierarchical query to show the indented nested call and reference chain.

    Bravid's reply in this thread from last year should give you a better example using packaged procedures.
    9856377

    ------ ADDED modified version of Bravid's query to show line numbers but couldn't get the result to format properly in the forum. Use this query after creating the packages in my reply above
    ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
    
    alter package pack4 compile
    
    alter package pack3 compile
    
    alter package pack2 compile
    
    alter package pack1 compile
    
    select * from user_identifiers
    
    WITH v AS (
      SELECT    Line,
                Col,
                INITCAP(NAME) Name,
                LOWER(TYPE)   Type,
                LOWER(USAGE)  Usage,
                USAGE_ID,
                USAGE_CONTEXT_ID, line lineno
        FROM USER_IDENTIFIERS
          WHERE Object_Name = 'PACK1'
            AND Object_Type = 'PACKAGE BODY'
    )
    SELECT RPAD(LPAD(' ', 2*(Level-1)) ||
                     Name, 20, '.')||' '||
                     RPAD(Type, 20)||
                     RPAD(Usage, 20)
                     IDENTIFIER_USAGE_CONTEXTS, lineno
      FROM v
      START WITH USAGE_CONTEXT_ID = 0
      CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
      ORDER SIBLINGS BY Line, Col
    / 
This discussion has been closed.