I'm passing a list object [1, 2, 3, 4, 5] to Oracle stored procedure by creating a custom type in oracle package as below -
create type index_num_rec as object (index_number varchar2(1000));
create type index_num_arr as table of index_num_rec ;
Procedure code -
procedure get_info (i_index_list in index_num_arr)
is
......
......
begin
.....
select * from index_data where index_number in (select index_number from table (i_index_list));
.....
end;
Java Code -
public List getIndexDetails(){
List objList = null;
try {
Session session = HibernateUtil.currentSession();
StoredProcUtil util = new StoredProcUtil();
List inoutList = new ArrayList();
int num = 1;
SQLArray sqlArr = new SQLArray("INDEX_NUM_ARR", "INDEX_NUM_REC");
sqlArr.setListElementJavaClassName("com.pearson.ebook.persistence.storedproc.IndexNumRec");
List indexNumRecList = [1, 2, 3, 4, 5];
sqlArr.setObjectList(indexNumRecList );
sqlArr.setPropertyArray("INDEX_NUMBER");
StoredProcParam param1 = new StoredProcParam(StoredProcParam.SQLARRAY_TYPE, sqlArr, num,
StoredProcParam.PARAMETER_TYPE_IN);
inoutList.add(param1);
num++;
util.executeStoredProc(session.connection(), "DEMO_PKG.GET_INFO", inoutList);
} catch (SQLException sql) {
logger.error("SQLException occurred in getRegions: ", sql);
throw new DataAccessException("SQLException occurred in getRegionsV2: ", sql);
}
return objList;
}
Now, instead of index_number in (1,2,3,4,5)
I'm getting last element as - index_number in (5)
Please guide, if I missed something.