首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >正在获取java.sql.SQLException:在JAVA中CallableStatement的索引::4处缺少IN或OUT参数

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

Stack Overflow用户
提问于 2017-01-09 21:10:22
回答 1查看 1.7K关注 0票数 0

我已经看了很多关于这个例外的帖子,但没有得到任何明确的想法。

我已经开发了应用程序,从fetches表中提取数据,并将其插入到数据库中。

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

代码语言:javascript
复制
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);
}
}

我已经创建了一个callable statement对象来从java调用pl/sql过程。

代码语言:javascript
复制
       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过程是:

代码语言:javascript
复制
   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;

运行代码后,我将获得

代码语言:javascript
复制
   java.sql.SQLException: Missing IN or OUT parameter at index:: 4

我只有3个传递参数和1个结果参数。所以我不能理解为什么它要求索引为4的参数。请在这方面给我建议。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-01-09 21:36:58

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

代码语言:javascript
复制
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);
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/41548838

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档