Retrieving oracle collections with jdbc and jpub generated classes

[Isabelle M Petoud]

Hello

I've used JPub to create custom classes for my base type and for a varray of that type. See the signatures of these at the bottom of this message.
I've a pl/sql procedure that has an out parameter that is for the array.
I get java.lang.ClassCastException when I tried to get the result as a CRDCountryArray.

This is my code:
final String COUNTRY_TYPE = "CRD_COUNTRY_TYPE";
final String COUNTRY_ARRAY = "CRD_COUNTRY_ARRAY";
....

// get an Oracle connection
dbConnection = (OracleConnection) dataSource.getConnection();
// Register the Oracle Type
Map typeMap = dbConnection.getTypeMap();
typeMap.put(COUNTRY_TYPE,CRDCountryParamVO.class);
typeMap.put(COUNTRY_ARRAY,CRDCountryArray.class);
dbConnection.setTypeMap(typeMap);
// Define the call to the procedure/function
stmt = (OracleCallableStatement)dbConnection.prepareCall
("{call " + procedureName + "(?) }");
stmt.registerOutParameter( 1 ,oracle.jdbc.OracleTypes.STRUCT,COUNTRY_ARRAY );
stmt.executeUpdate();
//aCountryArray = (CRDCountryArray)stmt.getArray(1);
oracle.sql.ARRAY array = stmt.getARRAY(1);
aCountryArray = (CRDCountryArray) array.getArray();

Could you please tell me what I am doing wrong?
Thanks a lot for your help
Regards
isabelle from Switzerland.


PROCEDURE getCountries(poo_Countries OUT CRD_COUNTRY_ARRAY) ;

TYPE CRD_COUNTRY_TYPE AS OBJECT
(
COUNTRYID NUMBER ,
COUNTRYCODE VARCHAR2(30 CHAR),
COUNTRYISOCODE VARCHAR2(3 CHAR),
COUNTRYISOCODE2 VARCHAR2(2 CHAR),
COUNTRYUNID NUMBER,
COUNTRYDNBID NUMBER,
COUNTRYBVDID NUMBER,
COUNTRYSHORTNAME VARCHAR2(20 CHAR),
COUNTRYNAME VARCHAR2(50 CHAR),
COUNTRYCOMMENT VARCHAR2(4000 CHAR)
)

TYPE CRD_COUNTRY_ARRAY AS VARRAY (1000) OF CRD_COUNTRY_TYPE

Classes generated by Jpub
public class JPubCRDCountryType implements ORAData, ORADataFactory, java.io.Serializable
public class CRDCountryParamVORef implements ORAData, ORADataFactory, java.io.Serializable
public class CRDCountryArray implements ORAData, ORADataFactory, java.io.Serializable

[Jeanne Boyarsky]

Isabelle,
Welcome to JavaRanch!

If you add the following code before the line that throws the exception, you can see what is really being returned. That will help in determining the mismatch.

Object temp = array.getArray();
System.out.println(temp);  

[Avi Abrami]

Isabelle,
I'm only guessing -- since I don't use JPublisher -- but I believe you should use

oracle.jdbc.OracleTypes.ARRAY

instead of

oracle.jdbc.OracleTypes.STRUCT

Good Luck,
Avi.

[Isabelle M Petoud]

Hello again

Thanks all for your help and suggestion. The code is pretty simple, once that one knows how to do it...
Here it is :
public CRDCountryArray getCountries(CRDCountryParamVO countryVO)
throws CRDServiceException {
final String COUNTRY_TYPE = CRDCountryParamVO._SQL_NAME;
final String COUNTRY_ARRAY = CRDCountryArray._SQL_NAME;
final String PROC = "CRD_GEOGRAPHY_PKG.getCountries";
// input not used for the moment...
CRDCountryArray countryArray = null;

ArrayDescriptor countryArrayDescriptor = null;

DataSource dataSource = getDataSource();

OracleCallableStatement stmt = null;
OracleConnection dbConnection = null;
try {
// get an Oracle connection
dbConnection = (OracleConnection) dataSource.getConnection();

// Register the Oracle Type : NOT NEEDED!!!
/*
Map typeMap = dbConnection.getTypeMap();
typeMap.put(COUNTRY_TYPE,CRDCountryParamVO.class);
typeMap.put(COUNTRY_ARRAY,CRDCountryArray.class);
dbConnection.setTypeMap(typeMap);
*/

// Define the call to the procedure/function
stmt = (OracleCallableStatement)dbConnection.prepareCall
("{call " + PROC + "(?) }");

// Define the descriptor
countryArrayDescriptor = new ArrayDescriptor(COUNTRY_ARRAY,dbConnection);

// This is probably where the problem starts
// Either ARRAY or STRUT
stmt.registerOutParameter( 1 ,oracle.jdbc.OracleTypes.ARRAY,COUNTRY_ARRAY );

// Call the PL/SQL procedure through the execute
stmt.execute();

// Let's try to get out the array...
countryArray = (CRDCountryArray)stmt.getORAData(1,CRDCountryArray.getORADataFactory());


} catch (SQLException sqle) {
SQLCallerUtil.transformSQLException(sqle);
} finally {
SQLCallerUtil.closeStmtAndCon(stmt,dbConnection);
}

return countryArray;

}
Regards
Isabelle

[Avi Abrami]

Isabelle,
The following line is from your code:

stmt.registerOutParameter(1,oracle.jdbc.OracleTypes.ARRAY,COUNTRY_ARRAY);

In other words, you are verifying that I gave you the correct suggestion when I said to use "OracleTypes.ARRAY" -- and not "OracleTypes.STRUCT" (as you originally had).

You're welcome,
Avi.