Oracle Side:
step1: create type IDLIST
CREATE OR REPLACE TYPE COMMON."IDLIST" is table of number ;
Step2: Create proc for inserting a array of categories for a company id
procedure insertValpakCatsMapping(op_company_id IN DISCOUNT_OWNER.COMP_VALPAK_CAT.COMPANYID%type,
op_category_ids IN common.idlist,
op_user_id IN DISCOUNT_OWNER.COMP_VALPAK_CAT.ADD_BY_USERID%type)IS
begin
insert into discount_owner.COMP_VALPAK_CAT
(companyid,
VALPAK_CATEGORY_ID,
add_by_userid,
change_by_userid)
select op_company_id,
catids.column_value,
op_user_id,
op_user_id
from table(cast(op_category_ids as common.idlist)) catids
minus
select op_company_id,
catids.column_value,
op_user_id,
op_user_id
from discount_owner.COMP_VALPAK_CAT optx, table(cast(op_category_ids as common.idlist)) catids
where optx.companyid = op_company_id and
optx.VALPAK_CATEGORY_ID = catids.column_value;
end;
Java Calling Code:
Class Test {
private static ArrayDescriptor _numericDescriptor = null;
public void insertValpakSubcategories(String companyid,
ArrayList<Integer> cats,long userId)throws SQLException {
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try {
conn = PoolManager.getInstance().getConnection(_pool);
stmt = conn.prepareCall("{call CMT_COMPANY_PACKAGE.insertValpakCats(?,?,?)}");
Array categoryIds = createSqlIntegerArray(cats, conn);
stmt.setString(1,companyid);
stmt.setArray(2, categoryIds);
stmt.setLong(3,userId);
stmt.execute();
stmt.close();
}catch(SQLException e){
LOGGER.error("error in saving subcategories for valpak",e);
throw e ;
}
}
public static Array createSqlIntegerArray(ArrayList integerList, Connection conn) {
if (_numericDescriptor == null) {
createArrayDescriptors();
}
try {
return new oracle.sql.ARRAY(_numericDescriptor, conn, integerList.toArray());
}
catch(SQLException e) {
LOGGER.error("Error creating sql integer array.", e);
return null;
}
}
private static void createArrayDescriptors() {
if(_numericDescriptor==null) {
Connection conn = null;
try {
conn = PoolManager.getInstance().getConnection(null);
_numericDescriptor = ArrayDescriptor.createDescriptor("COMMON.IDLIST", conn);
_stringDescriptor = ArrayDescriptor.createDescriptor("COMMON.VARLIST", conn);
} catch(SQLException e) {
LOGGER.error("Exception creating array descriptors...", e);
}
finally {
cleanUp(conn, null, null);
}
}
}
}
for more info please read
http://betteratoracle.com/posts/26-passing-arrays-between-java-and-oracle-procedures 