CODEWITHSUNDEEP
sqloracle-databaseplsql
nightfox79
nightfox79

Reputation: 2119

How to get a list with description of all dba packages

I need an overview in my application of all packages you can use in an sql or pl/sql statement from the database. I need the package name and procedures/functions with there description.

So for example:

dbms_output: Package to give some output

dbms_output.put_line: procedure to return some output to serveroutput

Can this be done using a normal select ?

I tried already the following which gives me data but not a description and not the procedures and or functions of the packages:

SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE
     WHERE 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 Object_Type IN ('PACKAGE', 'TYPE')
     ORDER BY Owner, Object_Type, Object_Name;


select *
from all_objects
where object_type = 'PACKAGE';

Upvotes: 3

Views: 18098

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

Maybe you take the HTML documentation from Oracle and read it with a DOM Parser. This prevents you from making "copy-paste" hundreds of times.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191305

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.

Upvotes: 5

Related Questions