5 Replies Latest reply: Oct 4, 2013 3:34 PM by rp0428 RSS

    Data Dictionary of Package , Procedure and Functions

    Skp_Oracle

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

       

       

       

      Hi All,

       

       

      I need to find out all the functions written  in a package  (private and non-Private)

      and corresponding RETURN type of the function .

       

       

      SELECT * FROM ALL_PROCEDURES WHERE object_type='PACKAGE' AND owner =<>

      AND object_name LIKE '<PKG_NAME>%'

      AND procedure_name LIKE '<Function_name>%'

       

       

      Gives me all the function names but not the private function of the pkg .(i.e. function not declared in pkg specification)

      also from where I determine the return type of the function.

       

       

      all of the function in the package start with F_   and all the private function start with  FP_ 

      in all of the packages.

       

       

      Thank you.

        • 1. Re: Data Dictionary of Package , Procedure and Functions
          rp0428

          See if  the ALL_ARGUMENTS view has what you are looking for.

          http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1014.htm

          ARGUMENT_NAMEVARCHAR2(30) If the argument is a scalar type, then the argument name is the name of the argument. A null argument name is used to denote a function return. If the function return or argument is a composite type, this view will have one row for each attribute of the composite type. Attributes are recursively expanded if they are composite. 

          The meanings of ARGUMENT_NAME, POSITION, SEQUENCE, and DATA_LEVEL are interdependent. Together, as a tuple, they represent a node of a flattened tree.

          ARGUMENT_NAME can refer to any of the following:

          •   Return type, if ARGUMENT_NAME is null and DATA_LEVEL = 0
          •   The argument that appears in the argument list if ARGUMENT_NAME is not null and DATA_LEVEL = 0
          •   Attribute name of the composite type if ARGUMENT_NAME is not null and DATA_LEVEL > 0;
          •   A collection element type if ARGUMENT_NAME is null and DATA_LEVEL > 0;
          • 2. Re: Data Dictionary of Package , Procedure and Functions
            SomeoneElse

            Just do a DESC on it.

             

            SQL> desc package_name


            • 3. Re: Data Dictionary of Package , Procedure and Functions
              Skp_Oracle

              Thanks , now I can figure out return type of all the non -private functions.

              though still  trying to find data dictionary details for private function.

              • 4. Re: Data Dictionary of Package , Procedure and Functions
                JustinCave

                Private functions are not visible in the data dictionary.

                 

                If you're really determined, you could potentially extract the source code for the package body from the data dictionary and write a parser for that.  That's probably a non-trivial amount of effort, though.  If the methods are private, what sort of reporting are you doing that requires you to mix the public and private methods?

                 

                Justin

                • 5. Re: Data Dictionary of Package , Procedure and Functions
                  rp0428
                  Thanks , now I can figure out return type of all the non -private functions.

                  though still  trying to find data dictionary details for private function.

                  Try using PL/SCOPE (Advanced App Dev Guide) and see if that gives you the info you are looking for:

                  http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope.htm

                   

                  Here is a simple sample that shows private procedures:

                  ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

                  CREATE OR REPLACE package pack1 as
                  PROCEDURE proc1;
                  PROCEDURE proc2;
                  END;
                  /

                  CREATE OR REPLACE package BODY pack1 as
                  PROCEDURE proc1 IS
                  BEGIN
                    NULL;
                  END;
                  PROCEDURE proc2 IS
                  BEGIN
                    proc1;
                  END;
                  PROCEDURE proc3 IS
                  BEGIN
                    proc1;
                    proc2;
                  END;
                  END;
                  /


                  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
                  /

                   

                  IDENTIFIER_USAGE_CONTEXTS,LINENO

                  Pack1............... package             definition          ,1

                    Proc1............. procedure           definition          ,2

                    Proc2............. procedure           definition          ,6

                      Proc1........... procedure           call                ,8

                    Proc3............. procedure           declaration         ,10

                      Proc3........... procedure           definition          ,10

                        Proc1......... procedure           call                ,12

                        Proc2......... procedure           call                ,13