http://auv2009.blog.163.com/blog/static/68858712201081711141383/
http://viralpatel.net/blogs/java-passing-array-to-oracle-stored-procedure/
为了解决这个迷茫了很久,主要是总是报无效的数据类型这个错误,后来才知道
自定义数组类型的名称必须要大写(具体为啥要大写建议参考oracle文档,我也没搞明白)
sql代码:
创建 存放字符串的类型
create or replace type vast.p_table as table of varchar2(20)
创建procedure
create or replace procedure vast.proc_comm_getListByGroup(
p_cursor out pkg_return_list.list_cursor,
p_array in p_table
)
as
begin
opechuan=n p_cursor for select to_char(timeslot,'yyyy-mm-dd hh24:mi:ss'),fromuser,touser,locat
from vast.comm_data_fri
where fromuser=p_array(0);
end;
java代码:
<span style="white-space:pre"> </span>DBConnect dbc = new DBConnect();
List<CommData> comm_data_list = null;
Connection connection = dbc.getConnection();
try {
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("VAST.P_TABLE",connection);
ARRAY array = new ARRAY(arraydesc, connection, personnames);
CallableStatement cs = connection.prepareCall("{call vast.proc_comm_getListByGroup(?,?)}");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.setArray(2, array);
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(1);
if(rs == null){
System.out.println("null");
}
comm_data_list = new ArrayList<CommData>();
while(rs.next())
{
CommData commdata = new CommData();
commdata.setTimeslot(rs.getString("timeslot"));
commdata.setFromuser(rs.getString("fromuser"));
commdata.setTouser(rs.getString("touser"));
commdata.setLocation(rs.getString("locat"));
comm_data_list.add(commdata);
}
cs.close();
rs.close();
但是解决完上面问题之后,发现数组是穿进去了,但是只有长度信息,值是空的,找了好多网页之后,才发现只需要添加orai18n.jar文件就可以了。。。