| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Check packages for functions and parameters
I'll check this out. I'd also missed all_arguments which probably gets us pretty much everything we need. Thanks...
David
-----Original Message-----
From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com]
Sent: Tuesday, September 05, 2006 5:43 PM
To: David Mitchell; oracle-l_at_freelists.org
Subject: RE: Check packages for functions and parameters
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de Jacques Kilchoer Envoyé : mardi, 5. septembre 2006 17:38 À : DavidM_at_HECB.WA.GOV; oracle-l_at_freelists.org 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 ?FUNCTION GET_TIME 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 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
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_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-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 05 2006 - 23:17:29 CDT
 |
 |