This discussion is archived
3 Replies Latest reply: Mar 31, 2013 1:02 PM by rp0428 RSS

Unable to set array value to procedure as input

944729 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points