6 Replies Latest reply: Sep 8, 2011 6:58 AM by 32685 RSS

    how to find dependency procedure names within a  package

    prakash
      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
          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
            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
               
              
              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
                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
                  Is this USER_IDENTIFIERS view is accessible by all users of database .
                  • 6. Re: how to find dependency procedure names within a  package
                    32685
                    It should be but it will only show the identifiers relative to the connected session.