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.registerOutParameter(2,OracleTypes.ARRAY, “SCHEMA.TABLE_TYPE_NAME”); // Register the output parameter type

//execute the Query

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


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



Hope this helps

R Vashi

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

  1. Manish

    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.



Leave a Reply

Please log in using one of these methods to post your comment: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.