You can get the procedures and functions for the packages, and object types (member functions), from all_procedures:
select ao.owner, ao.object_type, ao.object_name, ap.procedure_name
from all_objects ao
left join all_procedures ap
on ap.owner = ao.owner
and ap.object_name = ao.object_name
where ao.owner in (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
and ao.object_type in ('PACKAGE', 'TYPE')
order by ao.owner, ao.object_type, ao.object_name, ap.procedure_name;
OWNER OBJECT_TYPE OBJECT_NAME PROCEDURE_NAME
------------------------------ ------------------- ------------------------------ ------------------------------
DBSNMP PACKAGE DM_FMTLIB MGMT_DM_GEN_AC
DBSNMP PACKAGE DM_FMTLIB MGMT_DM_GEN_AC_FH
DBSNMP PACKAGE DM_FMTLIB MGMT_DM_GEN_AC_FS
DBSNMP PACKAGE DM_FMTLIB MGMT_DM_GEN_ANYC
...
If you want to show the arguments, if only to distinguish between overloaded procedures, you can join to all_arguments. But not sure how manageable that will be:
select ao.object_type
||' '|| ao.owner ||'.'|| ao.object_name
|| case when ap.procedure_name is not null then
'.'|| ap.procedure_name
|| case when count(aa.argument_name) > 0 then
'('|| listagg(aa.argument_name, ',')
within group (order by position) ||')'
end
end as object_info
from all_objects ao
left join all_procedures ap
on ap.owner = ao.owner
and ap.object_name = ao.object_name
and ap.object_id = ao.object_id
left join all_arguments aa
on aa.owner = ap.owner
and aa.object_name = ap.procedure_name
and aa.object_id = aa.object_id
and aa.subprogram_id = ap.subprogram_id
where ao.owner in (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
and ao.object_type in ('PACKAGE', 'TYPE')
and ao.object_name = 'DBMS_OUTPUT'
group by ao.object_type, ao.owner, ao.object_name, ap.procedure_name,
ao.object_id, ap.subprogram_id
order by ao.owner, ao.object_type, ao.object_name, ap.procedure_name;
I've restricted to just show DBMS_OUTPUT as an example:
OBJECT_INFO
-----------------------------------------------------------------------------------
PACKAGE SYS.DBMS_OUTPUT.DISABLE
PACKAGE SYS.DBMS_OUTPUT.ENABLE(BUFFER_SIZE,LOG_TYPE,LOG_TARGET,LOG_LEVEL)
PACKAGE SYS.DBMS_OUTPUT.GET_LINE(FILE,ID,LINE,BUFFER,DATATYPE,STATUS,BYTE_MODE,LEN)
PACKAGE SYS.DBMS_OUTPUT.GET_LINES(LINES,NUMLINES)
PACKAGE SYS.DBMS_OUTPUT.GET_LINES(LINES,NUMLINES)
PACKAGE SYS.DBMS_OUTPUT.NEW_LINE
PACKAGE SYS.DBMS_OUTPUT.PUT(A)
PACKAGE SYS.DBMS_OUTPUT.PUT_LINE(A)
PACKAGE SYS.DBMS_OUTPUT
Or to show return types for functions:
select ao.object_type
||' '|| ao.owner ||'.'|| ao.object_name
|| case when ap.procedure_name is not null then
'.'|| ap.procedure_name
|| case when count(aa.argument_name) > 0 then
'('|| listagg(aa.argument_name, ',')
within group (order by position) ||')'
end
|| case when min(aa.position) = 0 then ' RETURN '
|| max(case when position = 0 then aa.pls_type end) end
end as object_info
...
For DBMS_RANDOM:
PACKAGE SYS.DBMS_RANDOM.INITIALIZE(VAL)
PACKAGE SYS.DBMS_RANDOM.NORMAL RETURN NUMBER
PACKAGE SYS.DBMS_RANDOM.RANDOM RETURN BINARY_INTEGER
PACKAGE SYS.DBMS_RANDOM.SEED(VAL)
PACKAGE SYS.DBMS_RANDOM.SEED(VAL)
PACKAGE SYS.DBMS_RANDOM.STRING(OPT,LEN) RETURN VARCHAR2
PACKAGE SYS.DBMS_RANDOM.TERMINATE
PACKAGE SYS.DBMS_RANDOM.VALUE RETURN NUMBER
PACKAGE SYS.DBMS_RANDOM.VALUE(LOW,HIGH) RETURN NUMBER
PACKAGE SYS.DBMS_RANDOM
You can obviously format the information however you want, use data types instead of argument names, etc. so this is just a starting point.
I doubt there are descriptions or even comments anywhere though. This gives you a lot of things to have checkboxes against, and to write descriptions for. Referring out to the documentation seems more sensible, but depends what you want to do with the information.
