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:43:21 -0700
Sorry, I hit send too soon. I will also add this excerpt from the documentation
Oracle9i Supplied PL/SQL Packages and Types Reference. Release 2 (9.2), Part
Number A96612-01
Chapter 14
DBMS_DESCRIBE
You can use the DBMS_DESCRIBE package to get information about a PL/SQL object.
When you specify an object name, DBMS_DESCRIBE returns a set of indexed tables
with the results. Full name translation is performed and security checking is
also checked on the final object.
This package provides the same functionality as the Oracle Call Interface
OCIDescribeAny call.
-----Message d'origine-----
De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la
part de Jacques Kilchoer
Envoyé : mardi, 5. septembre 2006 17:38
À : DavidM@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Objet : 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 ?
------------------------------ ----------------------- ------ --------
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
