source

패키지 오라클 내부의 모든 프로시저 목록을 가져오는 방법

ittop 2023. 7. 1. 09:59
반응형

패키지 오라클 내부의 모든 프로시저 목록을 가져오는 방법

패키지 안에 있는 모든 기능의 이름을 알 수 있습니까?PKG_OWA 패키지가 있는데 패키지 내부의 모든 절차를 나열하려고 합니다.

데이터 사전 보기 ALL_PROCEDURES(또는 패키지만 원하는 경우 USER_PROCEDURES).자세히 알아보세요.

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

여기에는 패키지 사양에 노출된 공개 절차가 나열됩니다.원본 텍스트를 처리하지 않는 한 개인 프로시저(즉, 패키지 본문에만 지정된 프로시저)를 검색하는 쉬운 방법은 없습니다.Oracle은 이 정보를 수집하는 데 사용할 수 있는 유틸리티 PL/SCOPE를 제공하지만 세션 설정을 변경하고 코드를 다시 컴파일해야 하므로 모든 상황에서 적합하지 않을 수 있습니다.자세히 알아보세요.

나는 이것을 사용합니다.

패키지:

SELECT *
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PACKAGE' 
    and owner = 'owner_name'
    and object_name = 'package_name'

절차(전 세계 사양만 해당):

select *
 from all_procedures
 where owner = 'owner_name'
 and object_name = 'package_name'

프로시저의 내부/외부 인수:

select * 
from ALL_ARGUMENTS 
where owner = 'owner_name'
    and package_name = 'package_name'
    and object_name = 'procedure_name'

즐기세요!

누군가에게 유용할 수도 있지만, 이것은 패키지 본문에만 명시된 절차와 기능을 찾는 방법입니다.

select name, 
       type,
       decode(usage,'DECLARATION', 'body only', 'DEFINITION', 'spec and body', usage) defined_on,
       line body_line
  from user_identifiers ui
  where type in ('PROCEDURE', 'FUNCTION')
  and usage_context_id = (select usage_id
    from user_identifiers
      where object_name = ui.object_name
        and object_type = ui.object_type
        and usage_context_id = 0)
  and object_name = 'your package name'
  and object_type = 'PACKAGE BODY'
  order by name

APC의 답변이 올바른 라인에 있지만 주어진 SQL에는 'YOU'가 소유한 프로시저만 나열되며 USER_PROCEDURS에서 선택하는 것과 동일하지만 다른 스키마에 패키지가 있을 수 있습니다.

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_PRIVLS 테이블에 이러한 테이블이 표시됩니다(테이블의 삽입/업데이트/삭제 권한과 동일한 테이블).

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

언급URL : https://stackoverflow.com/questions/8654155/how-to-get-list-of-all-the-procedure-inside-a-package-oracle

반응형