aspose file tools*
The moose likes JDBC and the fly likes Retrieving oracle collections with jdbc and jpub generated classes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retrieving oracle collections with jdbc and jpub generated classes" Watch "Retrieving oracle collections with jdbc and jpub generated classes" New topic
Author

Retrieving oracle collections with jdbc and jpub generated classes

Isabelle M Petoud
Greenhorn

Joined: May 18, 2005
Posts: 2
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29249
    
139

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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

Joined: May 18, 2005
Posts: 2
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

Joined: Oct 11, 2000
Posts: 1121

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retrieving oracle collections with jdbc and jpub generated classes
 
Similar Threads
Mapping Varray of Object Type in oracle through jdbc
trouble passing array to stored procedure in spring HELP
Stored Procedure and Java
Complex data object returned from oracle
Passing String array as out parameter to stored procedure