不看会后悔的Windows XP之经验谈	简单快捷DIY实用家庭影院

调用Oracle存储过程的问题???

楼主zengtang（隋唐英雄）2006-03-21 14:07:51 在 Java / J2SE / 基础类提问

//代码如下
//存储过程参数依次为：date，varchar2，table
Connection con=getOracleConnection();
ResultSet rs=null;
CallableStatement cstm =con.prepareCall("{call proc_monitor(?,?,?)}");
cstm.setDate(1,new Date(2005,1,1));
cstm.setString(2,"12");
cstm.registerOutParameter(3,OracleTypes.PLSQL_INDEX_TABLE);
cstm.execute();//执行到此就报错

rs = (ResultSet)cstm.getObject(3);

错误提示为：
java.sql.SQLException: ORA-00932: inconsistent datatypes

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:1597)
at oracle.jdbc.oci8.OCIDBAccess.executeFetch(OCIDBAccess.java:1093)
at oracle.jdbc.oci8.OCIDBAccess.parseExecuteFetch(OCIDBAccess.java:1321)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1900)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:407)
at myStudy.db.OracleJdbc.main(OracleJdbc.java:27)
Exception in thread "main"
问题点数：50、回复次数：11Top

1 楼lcllcl987（毛爷爷）回复于 2006-03-21 14:28:41 得分 10

cstm.setDate(1,new Date(2005,1,1));
这个date有问题吧？
换成oracle的日期型试一下Top

2 楼zengtang（隋唐英雄）回复于 2006-03-21 14:36:35 得分 0

我定义的Date 是java.sql.dateTop

3 楼zengtang（隋唐英雄）回复于 2006-03-21 15:58:41 得分 0

应该是cstm.registerOutParameter(3,OracleTypes.PLSQL_INDEX_TABLE);
的问题，应该设置什么类型才行呢。Top

4 楼yuzl32（有酒醉）回复于 2006-03-22 10:19:58 得分 0

把proc_monitor 方法的各个参数声明发出来看看Top

5 楼zengtang（隋唐英雄）回复于 2006-03-22 11:45:19 得分 0

create or replace procedure proc_monitor(
d_statement in date,
s_citycode in varchar2,
table_ret out Prepayment_pkg.monitor_tab
)
create or replace package Prepayment_pkg as
type monitor_record is record (
s_citycode varchar2(40),
i_process_id number,
i_amount_undo number,
i_amount_done number,
i_max_times number,
i_min_times number);
type monitor_tab is table of monitor_record
INDEX BY BINARY_INTEGER;
end; Top

6 楼yuzl32（有酒醉）回复于 2006-03-22 12:01:07 得分 25

CallableStatement 替换为 OracleCallableStatement
Oracle定义的类型,CallableStatement 不一定试用Top

7 楼zengtang（隋唐英雄）回复于 2006-03-22 14:14:18 得分 0

换了OracleCallableStatement也不行啊Top

8 楼yuzl32（有酒醉）回复于 2006-03-22 19:57:42 得分 0

现在报什么错误?
(同样应该有对应的OracleConnection)Top

9 楼TONYBLARED（奔放的犀牛）回复于 2006-03-22 23:50:48 得分 15

楼主，给你个link，说的是sql server的，说的很清楚，你可以适当参考一下。
http://community.csdn.net/Expert/topic/4595/4595735.xml?temp=.0476343Top

10 楼zengtang（隋唐英雄）回复于 2006-03-31 11:11:06 得分 0

结贴
如下方法解决：
Connection con=getOracleConnection();
ResultSet rs=null;
OracleCallableStatement cstm =(OracleCallableStatement)con.prepareCall("{call Prepayment_process_monitor(?,?,?)}");
cstm.setDate(1,new Date(2005-1900,1-1,1));
cstm.setString(2,"12");
cstm.registerOutParameter(3,OracleTypes.ARRAY,"MONITOR_TAB");
cstm.execute();
ARRAY array = (ARRAY) cstm.getArray(3);
while(rs.next()){
System.out.println("success");
STRUCT obj= (STRUCT)rs.getObject(2);
Object[] attrs=obj.getAttributes();
String citycode=attrs[0].toString();
Integer process_id=new Integer(Integer.parseInt(attrs[1].toString()));
Integer amount_undo=new Integer(Integer.parseInt(attrs[2].toString()));
Integer amount_done=new Integer(Integer.parseInt(attrs[3].toString()));
Integer max_times=new Integer(Integer.parseInt(attrs[4].toString()));
Integer min_times=new Integer(Integer.parseInt(attrs[5].toString()));
System.out.println("citycode:"+citycode+" process_id:"+process_id+
" amount_undo:"+amount_undo+" amount_done:"+amount_done
+" max_times:"+max_times+" min_times:"+min_times);

}

rs =(ResultSet) array.getResultSet();
rs.close();
cstm.close();
con.close();

数据方面类型定义如下：
create or replace type monitor_record is object (
s_citycode varchar2(40),
i_process_id number,
i_amount_undo number,
i_amount_done number,
i_max_times number,
i_min_times number);
create or replace type monitor_tab is table of monitor_record;Top

11 楼zengtang（隋唐英雄）回复于 2006-03-31 11:13:58 得分 0

不好意思，
rs =(ResultSet) array.getResultSet();
位置贴错了。大家注意一下。Top