ver stored salida procedimientos procedimiento parametros ejercicios ejemplos ejecutar developer con comandos codigo bloque anonimo almacenados almacenado oracle plsql data-dictionary

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



procedure oracle ejemplos (4)

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


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


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 .


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


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!