stored - Cómo obtener una lista de todos los procedimientos dentro de un paquete oracle

procedure oracle ejemplos

Question

¿Puedo obtener el nombre de todas las funciones dentro de un paquete? Supongamos que tengo un paquete PKG_OWA y quiero enumerar todos los procedimientos dentro del paquete.

Answer 1

La respuesta de APC está en las líneas correctas, pero el SQL dado solo enumerará los procedimientos que posee "USTED" y es lo mismo que seleccionar de USER_PROCEDURES pero podría haber paquetes en otro esquema

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

También tenga en cuenta que cuando otorga y revoca la ejecución en un paquete, procedimiento o función, estos se muestran en la tabla DBA_TAB_PRIVS (la misma tabla que los privilegios de inserción / actualización / eliminación en las tablas)

Una vez que haya mencionado el nombre de un paquete como los DBMS proporcionados por Oracle, también puede usar DESC, que proporcionará una lista de los parámetros y tipos esperados, por ejemplo:

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

Answer 2

La vista del diccionario de datos ALL_PROCEDURES (o USER_PROCEDURES si solo quieres tus paquetes). Saber más.

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

Esto enumera los procedimientos públicos expuestos en la especificación del paquete. No hay una manera fácil de recuperar los procedimientos privados (es decir, aquellos especificados solo en el cuerpo del paquete) excepto procesando el texto de origen. Oracle proporciona una utilidad PL / SCOPE que podemos usar para recopilar esta información, pero nos obliga a cambiar la configuración de la sesión y volver a compilar nuestro código, por lo que puede no ser adecuado en todas las situaciones. Entérate más .

Answer 3

Tal vez sea útil para alguien, esta es una forma de averiguar el procedimiento y la función especificados en el cuerpo del paquete también.

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

Answer 4

Yo uso este:

tu equipaje:

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

sus procedimientos (solo a partir de especificaciones, es decir, globales)

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

y los argumentos de entrada / salida del procedimiento:

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

¡disfrutar!