3 Replies Latest reply: Mar 31, 2013 3:02 PM by rp0428 RSS

    Unable to set array value to procedure as input

    944729

      Hi ,

      I have a procedure call defined as below :
      PROCEDURE PR_COPY_RMG( PI_CregionCode IN VARCHAR2,
      PI_CrespCode IN VARCHAR2,
      PI_CFlt_no IN VARCHAR2,
      PI_Croute IN VARCHAR2,
      PI_CcOMP IN VARCHAR,
      PI_CpeakType IN VARCHAR2,
      PI_CpoolName IN VARCHAR2,
      PI_CFREQUENCY IN VARCHAR2,
      PI_REF_CPY_FLT_TO IN P_REF_CPY_FLT,
      PI_RMG_UPD_FLG IN VARCHAR2,
      PI_Cuser IN VARCHAR2);

      Here P_REF_CPY_FLT is defined as : CREATE OR REPLACE TYPE P_REF_CPY_FLT IS TABLE OF T_REF_CPY_FLT

      And T_REF_CPY_FLT is defined as :
      CREATE OR REPLACE TYPE T_REF_CPY_FLT AS OBJECT
      (
      REGION_CODE VARCHAR2(3),
      RESP_CODE VARCHAR2(3),
      FLT_NO VARCHAR2(4),
      ROUTE VARCHAR2(6),
      COMP VARCHAR2(1),
      PEAK_TYPE VARCHAR2(10),
      POOL_NAME VARCHAR2(100),
      FREQUENCY VARCHAR2(7)
      )


      The Java call is done as below :

      CallableStatement cstm = null;
      Connection con = null;
      try
      {
      con = (((WrappedConnection) (((HibernatePM) getPM()).getConnection()))).getUnderlyingConnection();
      StructDescriptor Stdesc = StructDescriptor.createDescriptor("T_REF_CPY_FLT", con);
      ArrayDescriptor Ardesc = ArrayDescriptor.createDescriptor("P_REF_CPY_FLT", con);

      Object[]arrayMain = new Object[data.size()];

      STRUCT eachElement = null;
      Iterator iter=data.iterator();
      int j=0;
      while (iter.hasNext()) {
      CopyRMGDTO dto = (CopyRMGDTO) iter.next();
      String args[] = new String[8];
      args[0] = dto.getRegionCode();
      args[1] = dto.getRespCode();
      args[2] = dto.getFltNo();
      args[3] = searchDTO.getRoute();
      args[4] = searchDTO.getComp();
      args[5] = dto.getPeakType();
      args[6] = dto.getPoolName();
      args[7] = searchDTO.getFrequency();
      eachElement = new STRUCT(Stdesc, con, args);
      arrayMain [j] = eachElement;
      j++;
      }

      ARRAY newArray = new ARRAY(Ardesc, con, arrayMain);

      cstm = con.prepareCall("call PKG_RMG_SR_PRCS.PR_Copy_RMG(?,?,?,?,?,?,?,?,?,?,?)");
      cstm.setString(1, searchDTO.getRegionCode());
      cstm.setString(2, searchDTO.getRespCode());
      cstm.setString(3, searchDTO.getFltNo());
      cstm.setString(4, searchDTO.getRoute());
      cstm.setString(5, searchDTO.getComp());
      cstm.setString(6, searchDTO.getPeakType());
      cstm.setString(7, searchDTO.getPoolName());
      cstm.setString(8, searchDTO.getFrequency());
      *((OracleCallableStatement) cstm).setObject(9, newArray);*
      cstm.setString(10, (update==true)?"Y":"N");
      cstm.setString(11, searchDTO.getUser());

      cstm.execute();

      }catch (SQLException ex) {
      ServiceException sx = new ServiceException(ex.getMessage());
      sx.setErrorCode("RMG.022");
      throw sx;
      }


      At runtime, i am getting the below error at the line marked in BOLD above in the code. Any help is greatly appreciated. Need to resolve this ASAP.
      java.lang.ArrayIndexOutOfBoundsException: 0
      at oracle.jdbc.oracore.OracleTypeADT.getAttrTypeAt(OracleTypeADT.java:3407)
      at oracle.jdbc.oracore.OracleTypeADT.pickle81rec(OracleTypeADT.java:2635)
      at oracle.jdbc.oracore.OracleTypeADT.pickle81(OracleTypeADT.java:2534)
      at oracle.jdbc.oracore.OracleTypeUPT.pickle81(OracleTypeUPT.java:481)
      at oracle.jdbc.oracore.OracleTypeCOLLECTION.pickle81(OracleTypeCOLLECTION.java:807)
      at oracle.jdbc.oracore.OracleTypeADT.pickle81(OracleTypeADT.java:2500)
      at oracle.jdbc.oracore.OracleTypeADT.linearize(OracleTypeADT.java:1750)
      at oracle.sql.ArrayDescriptor.toBytes(ArrayDescriptor.java:1034)
      at oracle.sql.ARRAY.toBytes(ARRAY.java:985)
      at oracle.jdbc.driver.OraclePreparedStatement.setArrayCritical(OraclePreparedStatement.java:6699)
      at oracle.jdbc.driver.OraclePreparedStatement.setARRAYInternal(OraclePreparedStatement.java:6659)
      at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:10662)
      at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:10249)
      at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:11029)
      at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:5590)

      Thanks in advance

      Kiran

      Edited by: KiranRaja2012 on 19-Jun-2012 23:31

      Edited by: KiranRaja2012 on 19-Jun-2012 23:32

      Edited by: KiranRaja2012 on 19-Jun-2012 23:32