0

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.

User_G
  • 13
  • 4

0 Answers0