How to read SQL Collection type in java JDBC

Hi All,

In this article I will show how to use SQL Collection in java code to read values. As collections are very easy way to pass data to java layer. It helps in eliminating the use of cursors in the procedure.

SQL Type
declare SQL Collection type
Set length of 3

java code


cst = getConnection().prepareCall(“{MY_PROC(?,?,?,?)}”);
cst.setString(1,usrId);
cst.registerOutParameter(2,OracleTypes.ARRAY, “SCHEMA.TABLE_TYPE_NAME”); // Register the output parameter type
cst.registerOutParameter(3,Types.INTEGER);
cst.registerOutParameter(4,Types.VARCHAR);

//execute the Query
cst.executeQuery();

Array arryType = cst.getArray(4);  // use Get ARRAY To fetch the SQL Collection into array object
ResultSet rs = arryType.getResultSet(); // covert the arryType to ResultSet object

if(rs!=null){

while(rs.next()){
Struct strct = (Struct) rs.getObject(2);  // Cast the object in Struct Type
Object[] attributes = strct.getAttributes();
//now start reading one by one
// in my collection my array size is 3

System.out.println(String.valueOf(attributes[0]));
System.out.println(String.valueOf(attributes[1]));
System.out.println(String.valueOf(attributes[2]));
}

}

Hope this helps

Thanks
R Vashi

One thought on “How to read SQL Collection type in java JDBC

  1. Hi,
    Thanks for your valuable post here. But when I am trying to read a collection object using above steps, I am getting below error –
    Fail to construct descriptor: Unable to resolve type:

    I have done damn a lot of analysis on google, but couldnt get the answer. I would appreciate if you can help me out.

    Thanks,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s