Query to get the list of procedures/functions and their parameter details of a PACKAGE (DBA_ARGUMENTS and DBA_PROCEDURES) with example ~ Oracle Apps Knowledge Sharing

Saturday, August 4, 2018

Query to get the list of procedures/functions and their parameter details of a PACKAGE (DBA_ARGUMENTS and DBA_PROCEDURES) with example

In every project we have a specific coding conventions and its responsibility of code reviewer to verify the custom packages are created with correct naming conventions.

For this, I have prepared a query to get the list of sub programs and their argument details. It’s very helpful for me and hence shared the same.

Query to get Sub program list:

SELECT owner,

       procedure_name subprogam_name,

       NVL2((SELECT 1

              FROM dba_arguments da

             WHERE subprogram_id = dp.subprogram_id

               AND argument_name IS NULL

               AND dp.OBJECT_ID = da.OBJECT_ID

            ), 'FUNCTION','PROCEDURE'

            ) subprogram_type

  FROM dba_procedures dp

 WHERE object_name = 'XXIF_TEST_PACKAGE'

   AND procedure_name IS NOT NULL;

Query to get Sub programs arguments list:

SELECT object_name subprogram_name,

       subprogram_id,

       position,

       sequence,

       NVL(argument_name,'RETURN_VALUE') argument_name,

       in_out,

       data_type

  FROM dba_arguments

 WHERE package_name = 'XXIF_TEST_PACKAGE'

ORDER BY subprogram_id,sequence;

Sample Package:

CREATE OR REPLACE PACKAGE XXIF_TEST_PACKAGE

IS

FUNCTION is_valid_date (pid_date IN VARCHAR2)

RETURN VARCHAR2;

FUNCTION is_valid_number (pin_number IN VARCHAR2)

RETURN VARCHAR2;

PROCEDURE do_interface(pin_batch_id         IN NUMBER,

                         piv_intf_source      IN VARCHAR2

                        );

END XXIF_TEST_PACKAGE;

/

CREATE OR REPLACE PACKAGE BODY XXIF_TEST_PACKAGE

IS

FUNCTION is_valid_date (pid_date IN VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

    NULL;

END;

  FUNCTION is_valid_number (pin_number IN VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

    NULL;

END;

PROCEDURE do_interface(pin_batch_id         IN NUMBER,

                         piv_intf_source      IN VARCHAR2

                        )

IS

BEGIN

    NULL;

END;

END XXIF_TEST_PACKAGE;

Query Executions:

DBA/USER/ALL_PROCEDURES

Column Name	Brief Explanation
OWNER
OBJECT_NAME	Name of the object: top level function/procedure/package/type/trigger name
PROCEDURE_NAME	Name of the package or type subprogram
OBJECT_ID	Object number of the object
SUBPROGRAM_ID	Unique sub-program identifier
OVERLOAD	Overload unique identifier
OBJECT_TYPE	The typename of the object
AGGREGATE	Is it an aggregate function ?
PIPELINED	Is it a pipelined table function ?
IMPLTYPEOWNER	Name of the owner of the implementation type (if any)
IMPLTYPENAME	Name of the implementation type (if any)
PARALLEL	Is the procedure parallel enabled ?

DBA/USER/ALL_ARGUMENTS

Column Name	Brief Explanation
OBJECT_NAME	Procedure or function name
PACKAGE_NAME	Package name
OBJECT_ID	Object number of the object
OVERLOAD	Overload unique identifier
SUBPROGRAM_ID	Unique sub-program Identifier
ARGUMENT_NAME	Argument name
POSITION	Position in argument list, or null for function return value
SEQUENCE	Argument sequence, including all nesting levels
DATA_LEVEL	Nesting depth of argument for composite types
DATA_TYPE	Datatype of the argument
DEFAULTED	Is the argument defaulted?
DEFAULT_VALUE	Default value for the argument
DEFAULT_LENGTH	Length of default value for the argument
IN_OUT	Argument direction (IN, OUT, or IN/OUT)
DATA_LENGTH	Length of the column in bytes
DATA_PRECISION	Length: decimal digits (NUMBER) or binary digits (FLOAT)
DATA_SCALE	Digits to right of decimal point in a number
RADIX	Argument radix for a number
CHARACTER_SET_NAME	Character set name for the argument
TYPE_OWNER	Owner name for the argument type in case of object types
TYPE_NAME	Object name for the argument type in case of object types
TYPE_SUBNAME	Subordinate object name for the argument type in case of object types
TYPE_LINK	Database link name for the argument type in case of object types
PLS_TYPE	PL/SQL type name for numeric arguments
CHAR_LENGTH	Character limit for string datatypes
CHAR_USED	Is the byte limit (B) or char limit (C) official for this string?

Note:

The USER_ARGUMENTS view contains only the argument name, type, passing mode, and default value. However, the view does not maintain any information about the NOCOPY hint, if used with the OUT or IN OUT arguments.

Hope this helps!!

Do you think this Article is useful?

0 Responses to “Query to get the list of procedures/functions and their parameter details of a PACKAGE (DBA_ARGUMENTS and DBA_PROCEDURES) with example”

Oracle Apps Knowledge Sharing

Saturday, August 4, 2018