Handling Oracle SQL Collection type/Table Types in JDBC Java

Hi All,

Below example will show you how to pass data in Oracle SQL Collection type from JDBC.

Step 1: Get the connection
Connection con=getConnection();//FROM DB factory

if (con != null) {
//Prepare the callable statement
cst = this.getConnection().prepareCall(“PROCEEDURE_NAME”);

//Create a struct descrptior for your collection type
StructDescriptor itemDescriptor = StructDescriptor.createDescriptor(“SCHEMA.<<COLLECTION_NAME>>”, con);

//Pass your data list
if (myDataList != null) {

//Create a Objecr Array, so that diffrent types could be passed
Object[] structArray = new Object[myDataList.size()];
int index = 0;

//Iterate your Collection object to inject data in the Object array
for (MyBean iElement : myDataList) {
Object[] itemAttributes = new Object[itemDescriptor.getLength()];
itemAttributes[0] = iElement.getProperty1();
itemAttributes[1] = iElement.getProperty1();
itemAttributes[2] = Integer.parseInt(iElement.getProperty1());

//Create a struct type object
Struct itemObject = new STRUCT(itemDescriptor, con,    itemAttributes);
structArray[index] = itemObject;
index++;
}

//Now create ArrayDescriptor
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(“SCHEMA.<<COLLECTION_NAME>>”,con);

//Now final step to create a ARRAY object on the basis of Struct and Array descriptor
ARRAY array = new ARRAY(descriptor, con, structArray);
cst.setArray(1, array); //Set the array as input paramtere in statement
cst.execute();

….

}

Hope this helps people facing problems handling SQL Collection using JDBC.


Thanks
R Vashi

One thought on “Handling Oracle SQL Collection type/Table Types in JDBC Java

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