Query below lists:

(A) all arguments from procedures accessible to the current user in Oracle database

(B) all arguments from procedures procedures in Oracle database

Query

A. Arguments from procedures accessible to the current user

select proc.owner as schema_name,
       proc.object_name as procedure_name,
       args.argument_name,
       args.in_out,
       args.data_type,
       args.data_length,
       args.data_precision,
       args.data_scale,
       args.defaulted,
       args.default_value
from sys.all_procedures proc
left join sys.all_arguments args
    on proc.object_id = args.object_id
where proc.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
          'OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS',
          'WK_TEST', 'WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 
          'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
          'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'LBACSYS',
          'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'APEX_040200')
      and object_type = 'PROCEDURE'
order by schema_name,
         procedure_name,
         args.position;

B. If you have privilege on dba_procedures and dba_arguments

select proc.owner as schema_name,
       proc.object_name as procedure_name,
       args.argument_name,
       args.in_out,
       args.data_type,
       args.data_length,
       args.data_precision,
       args.data_scale,
       args.defaulted,
       args.default_value
from sys.dba_procedures proc
left join sys.dba_arguments args
    on proc.object_id = args.object_id
where proc.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
          'OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS',
          'WK_TEST', 'WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 
          'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
          'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'LBACSYS',
          'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'APEX_040200')
      and object_type = 'PROCEDURE'
order by schema_name,
         procedure_name,
         args.position;

Columns

• schema_name - name of the schema containing function
• procedure_name - name of the function
• argument_name - function argument name
• in_out - direction of argument
  • IN
  • OUT
  • IN/OUT
• data_type -data type of argument
• data_length - length of data type in bytes
• data_precision - length in decimal digits (NUMBER) or binary digits (FLOAT)
• data_scale - digits to the right of the decimal point in a number
• defaulted - indicate if argument has default value
• default_value - if argument is defaulted then value of default value, else null

Rows

• One row represents one procedure argument or procedure if it hasn't any argument
• Scope of rows: all arguments from all procedures
• Ordered by schema name, procedure name, argument position

Sample results