Passing an array to Oracle PL/SQL from Spring and MyBatis

1. Create an oracle type




CREATE OR REPLACE TYPE "VARCHAR2_TABLE" as table of varchar2(4000);

2. Access it

type c_cursor is ref cursor;

Procedure sp_test(p_codes IN varchar2_table, p_cursor out c_cursor) as

..Do processing.

Return ref cursor back

end sp_test

3. In Spring create a class called StringArrayTypeHandlerCallBack

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;




import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;




import org.apache.ibatis.type.JdbcType;

import org.apache.ibatis.type.TypeHandler;




public class StringArrayTypeHandlerCallBack implements TypeHandler {

/* @SuppressWarnings("unchecked")

public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {

if(parameter == null) {

setter.setNull(Types.ARRAY);

}

else if(parameter instanceof ArrayList) {

Statement stmt = setter.getPreparedStatement();

Connection nativeConnection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) stmt.getConnection());

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", nativeConnection);

parameter = new ARRAY(desc, nativeConnection, ((ArrayList) parameter).toArray());

}

setter.setObject(parameter);

}*/




public Object valueOf(String arg0) {

return null;

}




@Override

public Object getResult(ResultSet arg0, String arg1) throws SQLException {

// TODO Auto-generated method stub

return null;

}




@Override

public Object getResult(CallableStatement arg0, int arg1) throws SQLException {

// TODO Auto-generated method stub

return null;

}




@Override

public void setParameter(PreparedStatement ps, int i, Object parameter,

JdbcType arg3) throws SQLException {




Connection conn = ps.getConnection();

// TODO Auto-generated method stub

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARCHAR2_TABLE", conn);

parameter = new ARRAY(desc, conn, ((ArrayList) parameter).toArray());

ps.setArray(i, (oracle.sql.ARRAY)parameter);

}




}

Mybatis Configuration.(Mapper File)- Lets call it DataMapper.xml

>

call sp_test(

#{codes,mode=IN,typeHandler=com.usps.eems.common.StringArrayTypeHandlerCallBack}

,#{data,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=resultOutMap}

)

Service Class

public void test(List codes){

Map map = new HashMap();

if(null!=codes)

map.put("codes", codes);

dataMapper.

test

(map);

}

Thats it.