5 Replies Latest reply on Jun 17, 2019 1:53 PM by user10316500

    Find All procedures name inside a package

    user10316500

      Hi

       

      Is there a query to find all procedures name inside a package?

       

      I tried with the following statement but it finds only procedures declared inside both Header/Body.

       

       

      select *
        from dba_procedures
       where object_type = 'PACKAGE'
         and object_name = 'xx'
      

       

      Procedure declared only in the package body are not founded.

       

      Thanks in advance.

       

      Stefano

        • 1. Re: Find All procedures name inside a package
          Solomon Yakobson

          I am not aware of any data dictionary view that would list package private procedures/functions. But why do you need their names if they can't be called from outside the package?

           

          SY.

          • 2. Re: Find All procedures name inside a package
            Solomon Yakobson

            The only way I am aware of is using PLSCOPE. Issue:

             

            alter session set plscope_settings='IDENTIFIERS:ALL';

             

            recompile package body and query all/user_identifiers.

             

            SY.

            • 3. Re: Find All procedures name inside a package
              MansiRaval

              The procedure created only in package cant found in dba_procedures .

               

              Came through below scenario while creating test package for this question, if anyone can comment on this.

              Below is script used:

              CREATE OR REPLACE PACKAGE test_pkg AS

                  PROCEDURE test_sp ( in_branch   IN VARCHAR2 );

              END test_pkg;

              /

              CREATE OR REPLACE PACKAGE BODY test_pkg AS

                  PROCEDURE test_sp ( in_branch   IN VARCHAR2 ) AS

                      v_branch   VARCHAR2(3);

                  BEGIN

                      v_branch := in_branch;

                      dbms_output.put_line('Brnach name:' || v_branch);

                  END test_sp;

                PROCEDURE sp_branch_check (

                      in_branch     IN VARCHAR2,

                      return_code   OUT NUMBER

                  ) AS

                      v_branch   VARCHAR2(3);

                  BEGIN

                      return_code := 0;

                      SELECT branch INTO

                          v_branch

                      FROM tab_branch WHERE branch = in_branch;

                  EXCEPTION

                      WHEN no_data_found THEN

                          BEGIN

                    --error code

                              return_code := 68603;

                              return;

                          END;

                  END sp_branch_check;

              END test_pkg;

              /

               

              When I run below query:

              select * from all_Procedures where Owner <>'SYS' and Object_Type='PACKAGE' and Object_Name='TEST_PKG';

              It return two row as below:

               

                

              OWNEROBJECT_NAMEPROCEDURE_NAMEOBJECT_IDSUBPROGRAM_IDOVERLOADOBJECT_TYPEAGGREGATEPIPELINEDIMPLTYPEOWNERIMPLTYPENAMEPARALLELINTERFACEDETERMINISTICAUTHID
              USER1TEST_PKGTEST_SP1616841PACKAGENONONONONODEFINER
              USER1TEST_PKG1616840PACKAGENONONONONODEFINER

               

              I compile package again by commenting private store proc sp_branch_check as well, still the result for above query is same.

               

              If anyone can suggest why this second row created in spite of having only one store proc in package.

              • 4. Re: Find All procedures name inside a package
                Solomon Yakobson

                For example:

                 

                CREATE OR REPLACE

                  PACKAGE PKG1

                    IS

                      PROCEDURE P_PUBLIC;

                      FUNCTION F_PUBLIC

                        RETURN NUMBER;

                END;

                /

                ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL'

                /

                CREATE OR REPLACE

                  PACKAGE BODY PKG1

                    IS

                      PROCEDURE P_PRIVATE

                        IS

                        BEGIN

                            NULL;

                      END;

                      FUNCTION F_PRIVATE

                        RETURN NUMBER

                        IS

                        BEGIN

                            RETURN 1;

                      END;

                      PROCEDURE P_PUBLIC

                        IS

                        BEGIN

                            NULL;

                      END;

                      FUNCTION F_PUBLIC

                        RETURN NUMBER

                        IS

                        BEGIN

                            RETURN 1;

                      END;

                END;

                /

                SELECT  UI.NAME

                   FROM  USER_IDENTIFIERS UI

                   WHERE UI.OBJECT_NAME = 'PKG1'

                     AND UI.USAGE = 'DEFINITION'

                     AND UI.TYPE IN ('PROCEDURE','FUNCTION')

                MINUS

                SELECT  PROCEDURE_NAME

                   FROM  USER_PROCEDURES

                   WHERE OBJECT_TYPE = 'PACKAGE'

                     AND OBJECT_NAME = 'PKG1'

                /

                 

                NAME

                --------------------

                F_PRIVATE

                P_PRIVATE

                 

                SQL>

                 

                SY.

                • 5. Re: Find All procedures name inside a package
                  user10316500

                  I want to enable logging level for all procedures inside package.

                   

                  To do this in an automatic mode  I need the query I asked,