| Issue with the usage of Varray in Oracle 10g. [message #233351] |
Wed, 25 April 2007 15:43 |
iQMember
Messages: 1
Registered: April 2007
Location: US
|
Junior Member |
|
|
Hi All,
We are upgrading from Oracle9i to Oracle10g and we are facing problems in using the user defined Varray.
We are using user defined we arrays in our procedures. We are getting an extran parameter when we are querying the meta data for the proecuedure which has the user defined array.
We have a user defined varray called STRING_ARRAY.
Create type string_array is VARRAY(200000) OF VARCHAR2(10000);
This is used extensively in stored procedures as a parameter (IN, IN OUT) and as a return type (OUT).
In the middle tier java code, we first retrieve the argument list expected by a stored procedure. This is done using the getProcedureColumns method of the DatabaseMetaData interface (This method is implemented by the JDBC driver). For Oracle 10g, this method is returning an extra parameter for every user defined varray in the argument list.
procedure PRC_TEST_UPDATE
(
P_NPRICE_STR string_array,
P_MBR_ID number,
P_NPRICE_CHNG_TICS out varchar2,
P_ERR_CODE_NUM out number,
P_ERR_MESG_STR out varchar2
);
When the application encounters a call to this stored procedure, it first connects to the database to get the number and type of arguments expected by this procedure using the getProcedureColumns method in DatabaseMetaData.
This method should return 5 rows:
Parameter Type
P_NPRICE_STR VARRAY
P_MBR_ID NUMBER
P_NPRICE_CHNG_TICS VARCHAR2
P_ERR_CODE_NUM NUMBER
P_ERR_MESG_STR VARCHAR2
But it actually returns 6:
Parameter Type
P_NPRICE_STR VARRAY
null VARCHAR2
P_MBR_ID NUMBER
P_NPRICE_CHNG_TICS VARCHAR2
P_ERR_CODE_NUM NUMBER
P_ERR_MESG_STR VARCHAR2
The extra parameter returned is of the user defined array's type (see row 2). We are facing this problem only with Oracle 10g (IQD1). The same code works fine with Oracle 9i (RDRIQI1).We have tried using the oracle driver implementations for Oracle 9i and Oracle 10g (version 10.2.0.2). We have had no luck thus far.
Can anybody help please.
Sreenath
[Updated on: Wed, 25 April 2007 15:45] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
