java调用oracle存储过程CallableStatementCallback; uncategorized SQLException

[Spark921]

错误信息如下:

CallableStatementCallback; uncategorized SQLException for SQL [{call move_ccodepool_table_poppy(?,?)}]; SQL state [null]; error code [17041]; 索引中丢失 IN 或 OUT 参数:: 2; nested exception is java.sql.SQLException: 索引中丢失 IN 或 OUT 参数:: 2

java代码如下:

public int moveData(final String tableName) throws Exception {
		// TODO Auto-generated method stub

		String sql = "{call move_ccodepool_table_poppy(?,?)}";
		Object o = super.execute(sql, new CallableStatementCallback() {

			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				cs.setString(1, tableName);
				cs.executeUpdate();
				int a = cs.getInt(2);
				return a;

			}
		});
		if (o != null) {
			return (Integer) o;
		}
		return 0;
	}

存储过程代码如下:

create or replace procedure move_ccodepool_table_poppy(tableName IN VARCHAR,
                                                       rowCnt    out int) as
  v_sql  varchar2(500); --sql语句
  v_sql1 varchar2(500);
 
begin
  v_sql := 'delete from ' || tableName ||
           ' where cpp_status != 0  or  ent_time < sysdate';
  dbms_output.put_line('rows deleted' || sql%rowcount);
  v_sql1 := 'insert into t_c_code_pool_Bak (ccp_id, cg_id, cpp_usecount, cpp_img, cpp_number, cpp_status, used_count, start_time, ent_time, ccp_stringkey, BAK_TIME
            ) select t.ccp_id, t.cg_id, t.cpp_usecount, t.cpp_img, t.cpp_number, t.cpp_status, t.used_count, t.start_time, t.ent_time, t.ccp_stringkey, sysdate 
             from ' || tableName ||
            ' t where t.cpp_status != 0 or t.ent_time < sysdate';
  execute immediate v_sql1;
  execute immediate v_sql;
   rowCnt :=sql%rowcount;
  commit;
end;

[luyun2011]

你的存储过程中有两个参数，一个IN，一个OUT，在java代码中必须有不同的处理，对于IN参数直接setString赋值即可，对OUT参数要注册：
cs.registerOutParameter(2, Types.VARCHAR);
在你的代码cs.setString(1, tableName);后面加上cs.registerOutParameter(2, Types.VARCHAR);即可