java - 获取 java.sql.SQLException:JAVA 中 CallableStatement 上的索引::4 处缺少 IN 或 OUT 参数

Question

我已经浏览了很多关于此异常的帖子，但没有得到任何明确的想法。

我开发了从 Excelsheet 中获取数据并将其插入数据库的应用程序。

为了实现这一点，我使用了 POI Apache 概念。我已经将数据从 excelsheet (HSSFCell) 对象提取到 Object[]。我开发了实现 SQLdata 和 Serialized 的类。

public class EmployeeObj implements SQLData,Serializable {
public long EMP_ID;               
public String FIRST_NM;    
public String MIDDLE_NM; 
public String DOJ;          
public String GENDER;   
public String STATUS;   
public long REPORTING_MGR;     
public String LAST_NM;
public String EMAIL_ID;
public String STREAM_ID;   
public String DESIGN_ID;   
public String EMP_LOCATION; 
public String GEO_CODE;   
public String EMP_NO; 
public long LEVEL_2_MGR;  
public String TENTATIVE_LAST_DAY;
private String sql_type="EMP_DATA_OBJ";
static int count=0;
public EmployeeObj() {
    super();
}
public EmployeeObj(String sql_type,Object[] empObj)
{
    this.sql_type = sql_type;
    count=count+1;
    this.EMP_ID=Long.parseLong(empObj[0].toString());               
    this.FIRST_NM=empObj[1].toString();    
    this.MIDDLE_NM=empObj[2].toString(); 
    this.DOJ=empObj[3].toString();          
    this.GENDER=empObj[4].toString();   
    this.STATUS=empObj[5].toString();   
    this.REPORTING_MGR=Long.parseLong(empObj[6].toString());     
    this.LAST_NM=empObj[7].toString();
    this.EMAIL_ID=empObj[8].toString();
    this.STREAM_ID=empObj[9].toString();   
    this.DESIGN_ID=empObj[10].toString();   
    this.EMP_LOCATION=empObj[11].toString(); 
    this.GEO_CODE=empObj[12].toString();   
    this.EMP_NO=empObj[13].toString(); 
    this.LEVEL_2_MGR=Long.parseLong(empObj[14].toString());  
    this.TENTATIVE_LAST_DAY=empObj[15].toString();
}

@Override
public String getSQLTypeName() throws SQLException {
    // TODO Implement this method
    return sql_type;
}

@Override
public void readSQL(SQLInput stream, String typeName) throws SQLException {
    // TODO Implement this method
    sql_type=typeName;
    this.EMP_ID=stream.readLong();               
    this.FIRST_NM=stream.readString();    
    this.MIDDLE_NM=stream.readString();
    this.DOJ=stream.readString();  
    this.GENDER=stream.readString();  
    this.STATUS=stream.readString();   
    this.REPORTING_MGR=stream.readLong();       
    this.LAST_NM=stream.readString();
    this.EMAIL_ID=stream.readString();
    this.STREAM_ID=stream.readString();
    this.DESIGN_ID=stream.readString();  
    this.EMP_LOCATION=stream.readString();
    this.GEO_CODE=stream.readString();
    this.EMP_NO=stream.readString();
    this.LEVEL_2_MGR=stream.readLong();    
    this.TENTATIVE_LAST_DAY=stream.readString();
    }


@Override
public void writeSQL(SQLOutput stream) throws SQLException {
    // TODO Implement this method
    stream.writeLong(EMP_ID);
    stream.writeString(FIRST_NM);
    stream.writeString(MIDDLE_NM);
    stream.writeString(DOJ);
    stream.writeString(GENDER);
    stream.writeString(STATUS);
    stream.writeString(LAST_NM);
    stream.writeString(EMAIL_ID);
    stream.writeString(STREAM_ID);
    stream.writeString(DESIGN_ID);
    stream.writeString(EMP_LOCATION);
    stream.writeString(GEO_CODE);
    stream.writeString(EMP_NO);
    stream.writeString(TENTATIVE_LAST_DAY);
    stream.writeLong(REPORTING_MGR);
    stream.writeLong(LEVEL_2_MGR);
}
}

我创建了一个可调用语句对象来从 java 调用 pl/sql 过程。

       CallableStatement pstmt =
           getDBTransaction().createCallableStatement("begin ?:= employee_dml.Main(?,EMP_OBJ_DT_ARR(?),?) end;",
                                                      0);
       EmployeeObj eob=null;
       Object[] obj = EmployeeD.toArray(new Object[EmployeeD.size()]);
       for(int i=0;i<EmployeeD.size();i++)
       {
           obj[i]=EmployeeD.get(i).toString();
       }
       eob=new EmployeeObj("EMP_DATA_OBJ",obj);
       pstmt.setInt(1,row_id);
       pstmt.setObject(2, (Object) eob);
       pstmt.setString(3,user);
       pstmt.registerOutParameter(1, Types.BIGINT);

       try
       {
       pstmt.executeUpdate();
       }
       catch(SQLException e1)
       {
               e1.printStackTrace();
           }
       dbTransaction.commit();

pl/sql 过程是:

   create or replace package body employee_dml is
     cursor C_EMP_EXISTS(L_EmpId LPM_EMPLOYEE_DATA.emp_id%type) is
      select count(1)
        from LPM_EMPLOYEE_DATA
       where emp_id=L_EmpId;

   cursor C_STREAM_EXISTS(L_StreamDesc LPM_STREAM.STREAM_DESC%type) is
      select STREAM_CODE
        from LPM_STREAM
       where UPPER(STREAM_DESC)=trim(UPPER(L_StreamDesc));

   cursor C_DESG_EXISTS(L_DesgDesc  LPM_DESIGNATION.DESIGNATION_DESC%type) is
    select DESIGNATION_CODE
      from LPM_DESIGNATION
     where UPPER(DESIGNATION_DESC)=trim(UPPER(L_DesgDesc));

   L_stream varchar2(255);
     L_desg   varchar2(255); 

   FUNCTION MAIN(row_id              IN       number,
                 p_obj_array         IN       EMP_OBJ_DT_ARR,
                 L_user              IN       varchar2)
   RETURN NUMBER IS 
   L_error_message varchar2(255);
   L_error boolean := false;
   L_count number := 0;
   BEGIN
    insert into ins_msg values('In Main new p_obj_array.count ');
    commit;
    for i in 1..p_obj_array.count loop
       open C_EMP_EXISTS(p_obj_array(i).emp_id);
      fetch C_EMP_EXISTS into L_count;
      close C_EMP_EXISTS;

       open C_STREAM_EXISTS(p_obj_array(i).STREAM_ID);
      fetch C_STREAM_EXISTS into L_stream;
      close C_STREAM_EXISTS;

       open  C_DESG_EXISTS(p_obj_array(i).DESIGN_ID);
      fetch C_DESG_EXISTS into L_desg;
      close C_DESG_EXISTS;

      if L_count = 0 then
        if INSERT_EMPDATA(p_obj_array,
                               L_error_message) = FALSE then
            return 1;
        end if;
      return 0;
      end if;

      END LOOP;
   EXCEPTION
    when OTHERS then
       L_error_message := SQL_LIB.CREATE_MSG('PACKAGE_ERROR',
                                         SQLERRM,
                                         null,
                                         TO_CHAR(SQLCODE));
  return 0;
  END MAIN; 
 FUNCTION INSERT_EMPDATA(p_obj_array in EMP_OBJ_DT_ARR,
                                O_error_message     OUT      varchar2)
  RETURN BOOLEAN IS
  BEGIN
    for i in 1..p_obj_array.count loop
      insert into lpm_employee_data
              (
              EMP_ID,     
              FIRST_NM, 
              MIDDLE_NM,
              DOJ,       
              GENDER,  
              STATUS,
              REPORTING_MGR,   
              LAST_NM, 
              EMAIL_ID, 
              STREAM_ID,   
              DESIGN_ID,  
              EMP_LOCATION,  
              GEO_CODE,  
              EMP_NO, 
              LEVEL_2_MGR,     
              TENTATIVE_LAST_DAY,      
              CREATE_ID,  
              CREATE_DATE,         
              UPDATE_ID,  
              UPDATE_DATE)
        values (p_obj_array(i).EMP_ID,     
              p_obj_array(i).FIRST_NM, 
              p_obj_array(i).MIDDLE_NM,
              TO_CHAR(TO_DATE(p_obj_array(i).doj,'DD-MM-YYYY')),       
              p_obj_array(i).GENDER,  
              p_obj_array(i).STATUS,
              p_obj_array(i).REPORTING_MGR,   
              p_obj_array(i).LAST_NM, 
              p_obj_array(i).EMAIL_ID, 
              L_stream,   
              L_desg,  
              p_obj_array(i).EMP_LOCATION,  
              p_obj_array(i).GEO_CODE,  
              p_obj_array(i).EMP_NO, 
              p_obj_array(i).LEVEL_2_MGR,     
              p_obj_array(i).TENTATIVE_LAST_DAY,
              USER,
              SYSDATE,
              USER,
              SYSDATE);
       end loop;
     return TRUE;
  EXCEPTION
  when OTHERS then
  O_error_message := SQL_LIB.CREATE_MSG('PACKAGE_ERROR',
                                         SQLERRM,
                                         null,
                                         TO_CHAR(SQLCODE));
  return FALSE;

  END INSERT_EMPDATA;
  END employee_dml;

运行代码后，我会得到

   java.sql.SQLException: Missing IN or OUT parameter at index:: 4

我只传递了 3 个参数和 1 个结果参数。所以我无法理解为什么它要求索引 4 处的参数。请就此提出建议。

Answer 1

函数的输出参数位于索引 1。输入参数从索引 2 开始到 4。

eob=new EmployeeObj("EMP_DATA_OBJ",obj);
pstmt.registerOutParameter(1, Types.BIGINT);
pstmt.setInt(2,row_id);
pstmt.setObject(3, (Object) eob)
pstmt.setString(4,user);