Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Retrieving oracle collections with jdbc and jpub generated classes

 
Isabelle M Petoud
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Isabelle,
I'm only guessing -- since I don't use JPublisher -- but I believe you should use

instead of

Good Luck,
Avi.
 
Isabelle M Petoud
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Isabelle,
The following line is from your code:

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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic