Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Check packages for functions and parameters
You probably already know this, but the DESCRIBE command on a package returns the list of external procedures and parameters. E.g. from a 9.2 database
SQL> describe dbms_utility
PROCEDURE ACTIVE_INSTANCES
Nom d'argument Type E/S par défaut ?FUNCTION CURRENT_INSTANCE RETURNS NUMBER FUNCTION DATA_BLOCK_ADDRESS_BLOCK RETURNS NUMBER
------------------------------ ----------------------- ------ --------
INSTANCE_TABLE TABLE OF RECORD OUT INSTANCE_COUNT NUMBER OUT PROCEDURE ANALYZE_DATABASE Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
METHOD VARCHAR2 IN ESTIMATE_ROWS NUMBER IN DEFAULT ESTIMATE_PERCENT NUMBER IN DEFAULT METHOD_OPT VARCHAR2 IN DEFAULT PROCEDURE ANALYZE_PART_OBJECT Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN DEFAULT OBJECT_NAME VARCHAR2 IN DEFAULT OBJECT_TYPE CHAR IN DEFAULT COMMAND_TYPE CHAR IN DEFAULT COMMAND_OPT VARCHAR2 IN DEFAULT SAMPLE_CLAUSE VARCHAR2 IN DEFAULT PROCEDURE ANALYZE_SCHEMA Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN METHOD VARCHAR2 IN ESTIMATE_ROWS NUMBER IN DEFAULT ESTIMATE_PERCENT NUMBER IN DEFAULT METHOD_OPT VARCHAR2 IN DEFAULT PROCEDURE CANONICALIZE Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN CANON_NAME VARCHAR2 OUT CANON_LEN BINARY_INTEGER IN PROCEDURE COMMA_TO_TABLE Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
LIST VARCHAR2 IN TABLEN BINARY_INTEGER OUT TAB TABLE OF VARCHAR2(227) OUT PROCEDURE COMMA_TO_TABLE Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
LIST VARCHAR2 IN TABLEN BINARY_INTEGER OUT TAB TABLE OF VARCHAR2(4000) OUT PROCEDURE COMPILE_SCHEMA Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN COMPILE_ALL BOOLEAN IN DEFAULT PROCEDURE CREATE_ALTER_TYPE_ERROR_TABLE Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME VARCHAR2 IN TABLE_NAME VARCHAR2 IN
Nom d'argument Type E/S par défaut ?FUNCTION FORMAT_CALL_STACK RETURNS VARCHAR2 FUNCTION FORMAT_ERROR_STACK RETURNS VARCHAR2 PROCEDURE GET_DEPENDENCY
------------------------------ ----------------------- ------ --------
DBA NUMBER IN FUNCTION DATA_BLOCK_ADDRESS_FILE RETURNS NUMBER Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
DBA NUMBER IN PROCEDURE DB_VERSION Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
VERSION VARCHAR2 OUT COMPATIBILITY VARCHAR2 OUT PROCEDURE EXEC_DDL_STATEMENT Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
PARSE_STRING VARCHAR2 IN
Nom d'argument Type E/S par défaut ?FUNCTION GET_TIME RETURNS NUMBER
------------------------------ ----------------------- ------ --------
TYPE VARCHAR2 IN SCHEMA VARCHAR2 IN NAME VARCHAR2 IN FUNCTION GET_HASH_VALUE RETURNS NUMBER Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN BASE NUMBER IN HASH_SIZE NUMBER IN FUNCTION GET_PARAMETER_VALUE RETURNS BINARY_INTEGER Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
PARNAM VARCHAR2 IN INTVAL BINARY_INTEGER IN/OUT STRVAL VARCHAR2 IN/OUT LISTNO BINARY_INTEGER IN DEFAULT
Nom d'argument Type E/S par défaut ?FUNCTION IS_CLUSTER_DATABASE RETURNS BOOLEAN FUNCTION MAKE_DATA_BLOCK_ADDRESS RETURNS NUMBER
------------------------------ ----------------------- ------ --------
REGIONID NUMBER IN TRANSITIONS RAW OUT FUNCTION IS_BIT_SET RETURNS NUMBER Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
R RAW IN N NUMBER IN
Nom d'argument Type E/S par défaut ?FUNCTION OLD_CURRENT_SCHEMA RETURNS VARCHAR2 FUNCTION OLD_CURRENT_USER RETURNS VARCHAR2 FUNCTION PORT_STRING RETURNS VARCHAR2
------------------------------ ----------------------- ------ --------
FILE NUMBER IN BLOCK NUMBER IN PROCEDURE NAME_RESOLVE Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN CONTEXT NUMBER IN SCHEMA VARCHAR2 OUT PART1 VARCHAR2 OUT PART2 VARCHAR2 OUT DBLINK VARCHAR2 OUT PART1_TYPE NUMBER OUT OBJECT_NUMBER NUMBER OUT PROCEDURE NAME_TOKENIZE Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN A VARCHAR2 OUT B VARCHAR2 OUT C VARCHAR2 OUT DBLINK VARCHAR2 OUT NEXTPOS BINARY_INTEGER OUT
Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
TAB TABLE OF VARCHAR2(227) IN TABLEN BINARY_INTEGER OUT LIST VARCHAR2 OUT PROCEDURE TABLE_TO_COMMA Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
TAB TABLE OF VARCHAR2(4000) IN TABLEN BINARY_INTEGER OUT LIST VARCHAR2 OUT
SQL>
-----Message d'origine-----
De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de David Mitchell
Envoyé : mardi, 5. septembre 2006 17:12
À : oracle-l_at_freelists.org
Objet : Check packages for functions and parameters
We have a third party application that we've layered our own asp.net app on top of and the vendor periodically changes some functions around when new releases come out. In particular a function was recently moved from one package to another. They also sometimes change the parameters for a given function that we rely on. Our developer is looking for a way to do some rudimentary unit testing to ensure we know when something has changed. The unit tests are being written in C#. So far we've been thinking of looking at all_source to check to see if a function is still in a particular package but we've yet to come up with a good way to check to see if the parameters have changed. Anyone got any suggestions while I continue my search?
David
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 05 2006 - 19:37:58 CDT
![]() |
![]() |