Dowemo


Question:

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';



Best Answer:


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.




Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs