Query below lists:

(A) all Procedures accessible to the current user in Oracle database

(B) all Procedures in Oracle database

Query

A. Procedures accessible to the current user

select proc.owner as schema_name,
       proc.object_name as procedure_name,
       LISTAGG(args.argument_name || ' ' || args.in_out  || 
                ' ' || args.data_type, '; ')
              WITHIN GROUP (ORDER BY position) as arguments
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'
group by proc.owner,  proc.object_name

B. If you have privilege on dba_procedures and dba_arguments

select proc.owner as schema_name,
       proc.object_name as procedure_name,
       LISTAGG(args.argument_name || ' ' || args.in_out  ||
                ' ' || args.data_type, '; ')
              WITHIN GROUP (ORDER BY position) as arguments
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'
group by proc.owner,  proc.object_name

Columns

• schema_name - name of the schema containing procedure
• procedure_name - name of the procedure
• arguments - procedure arguments with type and direction separated with ';'. Possible directions:
  • IN
  • OUT
  • IN/OUT

Rows

• One row represents one procedure
• Scope of rows: all procedures in database
• Ordered by schema name and procedure name

Sample results