Query below lists:

(A) all User Defined Functions accessible to the current user in Oracle database

(B) all User Defined Functions in Oracle database

Query was executed under the Oracle12c Database version.

Query

A. UDFs accessible to the current user

select obj.owner as schema_name,
       obj.object_name as function_name,
       ret.data_type as return_type,
       LISTAGG(args.in_out || ' ' || args.data_type, '; ')
              WITHIN GROUP (ORDER BY position) as arguments
from sys.all_objects obj
join sys.all_arguments args on args.object_id = obj.object_id
join (
      select object_id,
             object_name,
             data_type
      from sys.all_arguments
      where position = 0
) ret on ret.object_id = args.object_id
       and ret.object_name = args.object_name
where obj.object_type = 'FUNCTION'
      and obj.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')
      and args.position > 0
group by obj.owner,
         obj.object_name,
         ret.data_type
order by schema_name,
         function_name;

B. If you have privilege on dba_objects and dba_arguments

select obj.owner as schema_name,
       obj.object_name as function_name,
       ret.data_type as return_type,
       LISTAGG(args.in_out || ' ' || args.data_type, '; ')
              WITHIN GROUP (ORDER BY position) as arguments
from sys.dba_objects obj
join sys.dba_arguments args on args.object_id = obj.object_id
join (
      select object_id,
             object_name,
             data_type
      from sys.dba_arguments
      where position = 0
) ret on ret.object_id = args.object_id
       and ret.object_name = args.object_name
where obj.object_type = 'FUNCTION'
      and obj.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')
      and args.position > 0
group by obj.owner,
         obj.object_name,
         ret.data_type
order by schema_name,
         function_name;

Columns

• schema_name - name of the schema containing function
• function_name - name of the function
• return_type - function return type
• arguments - function arguments type with direction separated with ';'. Possible directions:
  • IN
  • OUT
  • IN/OUT

Rows

• One row - represents one function
• Scope of rows: - all functions in database
• Ordered by - schema name and function name

Sample results