RE: Check packages for functions and parameters
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <DavidM@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 5 Sep 2006 17:37:58 -0700
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 ?
------------------------------ ----------------------- ------ --------
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
FUNCTION CURRENT_INSTANCE RETURNS NUMBER
FUNCTION DATA_BLOCK_ADDRESS_BLOCK RETURNS NUMBER
Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
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
FUNCTION FORMAT_CALL_STACK RETURNS VARCHAR2
FUNCTION FORMAT_ERROR_STACK RETURNS VARCHAR2
PROCEDURE GET_DEPENDENCY
Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
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
FUNCTION GET_TIME RETURNS NUMBER
PROCEDURE GET_TZ_TRANSITIONS
Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
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
FUNCTION IS_CLUSTER_DATABASE RETURNS BOOLEAN
FUNCTION MAKE_DATA_BLOCK_ADDRESS RETURNS NUMBER
Nom d'argument Type E/S par défaut ?
------------------------------ ----------------------- ------ --------
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
FUNCTION OLD_CURRENT_SCHEMA RETURNS VARCHAR2
FUNCTION OLD_CURRENT_USER RETURNS VARCHAR2
FUNCTION PORT_STRING RETURNS VARCHAR2
PROCEDURE TABLE_TO_COMMA
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la
part de David Mitchell
Envoyé : mardi, 5. septembre 2006 17:12
À : oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
