This discussion is archived
6 Replies Latest reply: Sep 8, 2011 4:58 AM by 32685 RSS

how to find dependency procedure names within a  package

prakash Pro
Currently Being Moderated
Hi all,

I have 156 procedures inside a package , i want to find out dependency procedure names .

For example :
Lets say
Package p1 contains sp1,sp2,sp3 procedures and sp1 is calling sp2,sp3 procedures .
In this case for sp2,sp3 dependency procedure is going to be sp1 .

Thanks in advance ...






Thanks,
P Prakash
  • 1. Re: how to find dependency procedure names within a  package
    32685 Expert
    Currently Being Moderated
    Which version of Oracle are you on?

    If it's 11g you might find PL/SCOPE helps

    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#g1010526
    create or replace package pkg_1
    as
    
        procedure p1;
    END;
    /
    create or replace package BODY pkg_1
    as
    
        procedure p5
        IS
        
        BEGIN
        
            NULL;
        
        END;
    
        procedure p4
        IS
        
        BEGIN
        
            p5;
        
        END;
    
        procedure p3
        IS
        
        BEGIN
        
            p4;
        
        END;
        
        procedure p2
        IS
        
        BEGIN
        
            p3;
        
        END;    
    
        procedure p1
        IS
        
        BEGIN
        
            p2;
            
            p3;
        
        END;
    END;
    /
    WITH v AS (
      SELECT    Line,
                Col,
                INITCAP(NAME) Name,
                LOWER(TYPE)   Type,
                LOWER(USAGE)  Usage,
                USAGE_ID,
                USAGE_CONTEXT_ID
        FROM USER_IDENTIFIERS
          WHERE Object_Name = 'PKG_1'
            AND Object_Type = 'PACKAGE BODY'
    )
    SELECT RPAD(LPAD(' ', 2*(Level-1)) ||
                     Name, 20, '.')||' '||
                     RPAD(Type, 20)||
                     RPAD(Usage, 20)
                     IDENTIFIER_USAGE_CONTEXTS
      FROM v
      START WITH USAGE_CONTEXT_ID = 0
      CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
      ORDER SIBLINGS BY Line, Col
    /
    
    IDENTIFIER_USAGE_CONTEXTS
    --------------------------------------------------------------------------------
    Pkg_1............... package             definition
      P5................ procedure           declaration
        P5.............. procedure           definition
      P4................ procedure           declaration
        P4.............. procedure           definition
          P5............ procedure           call
      P3................ procedure           declaration
        P3.............. procedure           definition
          P4............ procedure           call
      P2................ procedure           declaration
        P2.............. procedure           definition
          P3............ procedure           call
      P1................ procedure           definition
        P2.............. procedure           call
        P3.............. procedure           call
    
    15 rows selected.
    HTH

    David

    Edited by: Bravid on Sep 8, 2011 11:27 AM
  • 2. Re: how to find dependency procedure names within a  package
    Solomon Yakobson Guru
    Currently Being Moderated
    Oracle tracks dependency on object level only. It does not track dependencies on object elements/items. Since package is a single object, Oracle does not track dependencies to package procedure/function and dependencies between package procedures/functions. It is same as tracking dependencies on table column or between table columns - Oracle simple does not provide it. What you could do is create package procedures/functions as standalone (probably in a separate schema so you can easily get rid of them), check dependencies and remove created standalone procedures/functions.

    SY.
  • 3. Re: how to find dependency procedure names within a  package
    prakash Pro
    Currently Being Moderated
     
    
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE     11.1.0.7.0     Production
    TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production
    Thanks,
    P Prakash
  • 4. Re: how to find dependency procedure names within a  package
    32685 Expert
    Currently Being Moderated
    Hello

    I'm not sure if PL/SCOPE is available on 11g r1 - maybe you could try it out? I've extended the example to make it so you can establish whether a procedure is within the package or not.
    CREATE OR REPLACE procedure p6
    AS
    BEGIN
        NULL;
    END;
    /
    create or replace package pkg_1
    as
    
        procedure p1;
    END;
    /
    create or replace package BODY pkg_1
    as
    
        procedure p5
        IS
        
        BEGIN
        
            p6;
        
        END;
    
        procedure p4
        IS
        
        BEGIN
        
            p5;
        
        END;
    
        procedure p3
        IS
        
        BEGIN
        
            p4;
        
        END;
        
        procedure p2
        IS
        
        BEGIN
        
            p3;
        
        END;    
    
        procedure p1
        IS
        
        BEGIN
        
            p2;
            
            p3;
        
        END;
    END;
    /
    WITH v AS (
      SELECT    Line,
                Col,
                INITCAP(NAME) Name,
                LOWER(TYPE)   Type,
                LOWER(USAGE)  Usage,
                USAGE_ID,
                USAGE_CONTEXT_ID
        FROM USER_IDENTIFIERS
          WHERE Object_Name = 'PKG_1'
            AND Object_Type = 'PACKAGE BODY'
    )
    SELECT
        identifier_usage_contexts,
        CASE
            WHEN COUNT(CASE WHEN lower(usage) = 'definition' THEN 1 END) OVER(PARTITION BY name) = 0 THEN
                'External to package'
            ELSE
                'Within package'
        END location
    FROM
        (   SELECT
                IDENTIFIER_USAGE_CONTEXTS,
                Type,
                Usage,
                Name,
                ROWNUM rn
            FROM
                (   SELECT RPAD(LPAD(' ', 2*(Level-1)) ||
                             Name, 20, '.')||' '||
                             RPAD(Type, 20)||
                             RPAD(Usage, 20)
                             IDENTIFIER_USAGE_CONTEXTS,
                             Type,
                             Usage,
                             Name
                      FROM v
                      START WITH USAGE_CONTEXT_ID = 0
                      CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
                      ORDER SIBLINGS BY Line, Col
                )
        )
    ORDER BY rn
    /
    
    IDENTIFIER_USAGE_CONTEXTS                                              LOCATION
    ---------------------------------------------------------------------- -------------------
    Pkg_1............... package             definition                    Within package
      P5................ procedure           declaration                   Within package
        P5.............. procedure           definition                    Within package
          P6............ procedure           call                          External to package
      P4................ procedure           declaration                   Within package
        P4.............. procedure           definition                    Within package
          P5............ procedure           call                          Within package
      P3................ procedure           declaration                   Within package
        P3.............. procedure           definition                    Within package
          P4............ procedure           call                          Within package
      P2................ procedure           declaration                   Within package
        P2.............. procedure           definition                    Within package
          P3............ procedure           call                          Within package
      P1................ procedure           definition                    Within package
        P2.............. procedure           call                          Within package
        P3.............. procedure           call                          Within package
    
    16 rows selected.
    HTH

    David

    Edited by: Bravid on Sep 8, 2011 11:57 AM
    Corrected the ordering of the result set
  • 5. Re: how to find dependency procedure names within a  package
    prakash Pro
    Currently Being Moderated
    Is this USER_IDENTIFIERS view is accessible by all users of database .
  • 6. Re: how to find dependency procedure names within a  package
    32685 Expert
    Currently Being Moderated
    It should be but it will only show the identifiers relative to the connected session.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points