如何获取软件包oracle中的所有过程的列表?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (26)

我可以获取包内所有功能的名称吗?假设我有一个包PKG_OWA,我想列出包内的所有过程。

提问于
用户回答回答于

数据字典视图ALL_PROCEDURES(或者USER_PROCEDURES,如果你只是想要你的包)。

 select procedure_name
 from all_procedures
 where owner = 'YOU'
 and object_name = 'YOUR_PACKAGE'

这列出了包规范中公开的公共程序。除了处理源文本之外,没有简单的方法来检索私有过程(即仅在包体中指定的过程)。Oracle提供了一个PL / SCOPE实用程序,我们可以使用它来收集这些信息,但它要求我们更改会话设置并重新编译我们的代码,因此它可能不适合所有情况。

用户回答回答于

SQL> select * from all_procedures where owner='TEST';

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
PROCEDURE_NAME                 AGG PIP IMPLTYPEOWNER
------------------------------ --- --- ------------------------------
IMPLTYPENAME                   PAR INT DET AUTHID
------------------------------ --- --- --- ------------
TEST                           TEST
                               NO  NO
                               NO  NO  NO  DEFINER

SQL> select * from user_procedures;

OBJECT_NAME                    PROCEDURE_NAME                 AGG PIP
------------------------------ ------------------------------ --- ---
IMPLTYPEOWNER                  IMPLTYPENAME                   PAR INT DET
 ------------------------------ ------------------------------ --- --- ---
AUTHID
------------
TEST                                                          NO  NO
                                                          NO  NO  NO

还要注意,当你授予和撤销对程序包,过程或函数的执行时,它们将显示在DBA_TAB_PRIVS表中(与表上的插入/更新/删除权限相同的表)

一旦你确定了一个软件包的名称,比如Oracle提供的DBMS软件包,你也可以使用DESC来提供预期的参数和类型列表,例如:

SQL> desc dbms_lob
PROCEDURE APPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BLOB                    IN
PROCEDURE APPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        CLOB                    IN
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_1                          BLOB                    IN
 LOB_2                          BLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_1                          CLOB                    IN
 LOB_2                          CLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_1                         BINARY FILE LOB         IN
 FILE_2                         BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
PROCEDURE CONVERTTOBLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_CLOB                       CLOB                    IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BLOB_CSID                      NUMBER                  IN
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT
PROCEDURE CONVERTTOCLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_BLOB                       BLOB                    IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BLOB_CSID                      NUMBER                  IN
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT
PROCEDURE COPY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BLOB                    IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE COPY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        CLOB                    IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE CREATETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 CACHE                          BOOLEAN                 IN
 DUR                            BINARY_INTEGER          IN     DEFAULT
PROCEDURE CREATETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 CACHE                          BOOLEAN                 IN
 DUR                            BINARY_INTEGER          IN     DEFAULT
PROCEDURE ERASE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE ERASE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE FILECLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
PROCEDURE FILECLOSEALL
FUNCTION FILEEXISTS RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
PROCEDURE FILEGETNAME
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 DIR_ALIAS                      VARCHAR2                OUT
 FILENAME                       VARCHAR2                OUT
FUNCTION FILEISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
PROCEDURE FILEOPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN     DEFAULT
PROCEDURE FREETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
PROCEDURE FREETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
FUNCTION GETCHUNKSIZE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION GETCHUNKSIZE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
FUNCTION GET_STORAGE_LIMIT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
FUNCTION GET_STORAGE_LIMIT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
 PATTERN                        RAW                     IN
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
 PATTERN                        VARCHAR2                IN
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 PATTERN                        RAW                     IN
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
FUNCTION ISTEMPORARY RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION ISTEMPORARY RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
PROCEDURE LOADBLOBFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_BFILE                      BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
PROCEDURE LOADCLOBFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_BFILE                      BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BFILE_CSID                     NUMBER                  IN
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT
PROCEDURE LOADFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE LOADFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN     DEFAULT
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN
 BUFFER                         RAW                     OUT
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN
 BUFFER                         VARCHAR2                OUT
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN
 BUFFER                         RAW                     OUT
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE TRIM
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 NEWLEN                         NUMBER(38)              IN
PROCEDURE TRIM
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 NEWLEN                         NUMBER(38)              IN
PROCEDURE WRITE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN
 OFFSET                         NUMBER(38)              IN
 BUFFER                         RAW                     IN
PROCEDURE WRITE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN
 OFFSET                         NUMBER(38)              IN
 BUFFER                         VARCHAR2                IN
PROCEDURE WRITEAPPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN
 BUFFER                         RAW                     IN
PROCEDURE WRITEAPPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN
 BUFFER                         VARCHAR2                IN

扫码关注云+社区