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!!



Saturday, August 4, 2018 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.