如何获得包含所有dba软件包描述的列表 [英] How to get a list with description of all dba packages

问题描述

我需要在应用程序中概述可以在数据库中的sql或pl/sql语句中使用的所有软件包. 我需要软件包名称以及其中的描述的过程/功能.

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.

例如:

dbms_output:提供一些输出的软件包

dbms_output: Package to give some output

dbms_output.put_line:将一些输出返回到serveroutput的过程

dbms_output.put_line: procedure to return some output to serveroutput

可以使用常规选择吗?

我已经尝试了以下方法，这些方法可以给我数据，但不能提供描述，不能提供程序包的过程和/或功能:

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

推荐答案

您可以从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               
...

如果要显示参数，如果只是为了区分重载过程，则可以加入all_arguments.但是不确定这将是多么易于管理:

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;

我仅以DBMS_OUTPUT为例:

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
...

对于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.

这篇关于如何获得包含所有dba软件包描述的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！