Passing Arrays to SP

Calling a SP (Stored procedure) by passing primitive types such as int, strings is not a big deal..
but for some scenarios we might need to pass a collection to a SP to get our work done,,,

for example say we need to persist a collection of users list in our system to the DB table, assume the list size is 100+ , simple program can be used to persist a single user data record in to the DB and we might loop this function to persist that kind of a user lot...

how about passing a user list directly to the SP and using a single DB connection(JDBC connection) to get this work done,,so that a single DB connection is used to persist 100+ data records..or even 1000+ ..

for this we can use Oracle StructDescriptor and ArrayDescriptor ..

1. Create struct descriptor and Array descriptor for the passing object type and Array.

Note: we have to get OracleConnection for this, below code sample is for a Jboss deployed Data access class,
T_USER_LOGIN and T_USER_LOGIN_TABLE are required at oracle DB level as Oracle Object type & oracle collection type respectively.


StructDescriptor userLoginsDesc = StructDescriptor.createDescriptor("T_USER_LOGIN",
(OracleConnection) ((WrappedConnection) conn).getUnderlyingConnection());

ArrayDescriptor userLoginsTableDesc = ArrayDescriptor.createDescriptor(
"T_USER_LOGIN_TABLE", (OracleConnection) ((WrappedConnection) conn)
.getUnderlyingConnection());



2. Create STRUCT array to pass in,

if (userLogins != null && userLogins.size() > 0) {
for (Login bean : userLogins) {

Object[] recUsrLogin = new Object[3];

recUsrLogin[0] = 1;
recUsrLogin[1] = bean.getLoginName();
recUsrLogin[2] = bean.getPassword();

STRUCT loginsStruct = new STRUCT(
userLoginsDesc,
(OracleConnection) ((WrappedConnection) conn).getUnderlyingConnection(),
recUsrLogin);

userLoginsArr[index] = loginsStruct;

++index;

}

}


ARRAY pUsrLoginsarr = new ARRAY(userLoginsTableDesc,
(OracleConnection) ((WrappedConnection) conn).getUnderlyingConnection(),
userLoginsArr);


3.Set the Array to the collable statement.

ocs = (CallableStatement) conn
.prepareCall("YOUR_SP_NAME");

ocs.setArray(3, pUsrLoginsarr);

ocs.registerOutParameter(3, OracleTypes.ARRAY, "T_USER_LOGIN_TABLE");


Note : you have to register this array type as a out parameter to the Collable statement..


ocs.execute(); // execute it,, that's All..