1

I am trying to pass Java array object to PLSQL stored procedure, however when I am trying to execute, I am getting the following exception

java.sql.SQLException: Inconsistent java and sql object types

My Dao Class:

public class UploadTradeDaoImpl implements UploadTradeDao{

private static Logger log = LoggerFactory
        .getLogger(UploadTradeDaoImpl.class);
private SqlSessionFactory sqlSessionFactory;

public SqlSessionFactory getSqlSessionFactory() {
    return sqlSessionFactory;
}

public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
    this.sqlSessionFactory = sqlSessionFactory;
}

public int uploadTrade(List<UploadTrade> uploadTradeList) {

    SqlSession session = sqlSessionFactory.openSession();
    try {
        Connection conn = session.getConnection().getMetaData()
                .getConnection();
        StructDescriptor structDescriptor = StructDescriptor
                .createDescriptor("UPLOADTRADE_OBJ", conn);
        STRUCT[] testStruct= new STRUCT[uploadTradeList.size()];
        ArrayDescriptor arrayDescriptor= ArrayDescriptor.createDescriptor(
                "UPLOADTRADE_REC", conn);
        Object[] upload_obj_array = new Object[uploadTradeList.size()];
        for (int index = 0; index < uploadTradeList.size(); index++) {
            UploadTrade uploadTradeObj = uploadTradeList.get(index);
            Object[] uploadObjects = new Object[] {
                    uploadTradeObj.getBusTrdId(),
                    uploadTradeObj.getIntrnlExtl(),
                    uploadTradeObj.getMarsLe(),
                    uploadTradeObj.getLeg1CflwType(),
                     uploadTradeObj.getLeg2CflwType(),
                    uploadTradeObj.getRestmntCode(),
                    uploadTradeObj.getRestmntQtr(),
                    uploadTradeObj.getTrdId()};
            upload_obj_array[index] = new STRUCT(structDescriptor, conn, uploadObjects);

        }
        ARRAY obj_array = new ARRAY(arrayDescriptor, conn, upload_obj_array);

        CallableStatement callableStatement= conn.prepareCall("call INSERTUPLOADTRADEOBJ(?,?)");
        callableStatement.setArray(1, obj_array);
        callableStatement.registerOutParameter(2, OracleTypes.ARRAY,"UPLOADTRADE_REC");
        callableStatement.execute();


    } catch (Exception e) {
        e.printStackTrace();
        session.rollback();
        log.error("Error! in UploadTrade()" + e.getMessage());
        return 0;
    } finally {

        session.close();

    }
    return 1;

}

I am doing this using these 2 links: https://community.oracle.com/message/4329339#4329339

Pass array from Java to Oracle: java.sql.SQLException: Fail to convert to internal representation:error

please let me know what I am doing wrong.

Thanks in Advance

6
  • Try out OracleCallableStatement and setObject() instead of setArray() Commented Jun 18, 2014 at 12:32
  • Hi OracleUser Thanks for suggestion but I tried it with setObject() also but now working is there any possibilities that the procedure may have error. Commented Jun 18, 2014 at 13:09
  • in which line you get the error exactly? in execute? Commented Jun 18, 2014 at 13:12
  • upload_obj_array[index] = new STRUCT(structDescriptor, conn, uploadObjects); in this line Commented Jun 18, 2014 at 13:30
  • What is the definition of the type UPLOADTRADE_OBJ? It appears that oracle is complaining that your arguments in your Object[] do not match the type Oracle is expecting. Commented Jun 19, 2014 at 3:06

1 Answer 1

2

Example

userEntitlementDescriptor =  ArrayDescriptor.createDescriptor("TWO_D_TYPE", conn.unwrap(oracle.jdbc.OracleConnection.class));
    userDescriptor = ArrayDescriptor.createDescriptor("T_ARRAY", conn.unwrap(oracle.jdbc.OracleConnection.class));  
    userListArray = new ARRAY(userDescriptor, conn.unwrap(oracle.jdbc.OracleConnection.class), userArray);  

    call = conn.prepareCall("{ ? = call USER_ENTITLEMENT_CHECK(?,?,?) }");
    call.registerOutParameter(1, Types.ARRAY, "TWO_D_TYPE"); // or whatever it is
    call.setString(2, entitlementTags);
    call.setString(3, contentId);
    call.setArray(4, userListArray);
    call.execute();
    userEntitlementArray = (ARRAY) call.getObject(1);
    ResultSet rsl = userEntitlementArray.getResultSet();
    while(rsl.next()){
      ARRAY varray3 = (ARRAY) rsl.getObject (2);
      String[] entitlementResultArray = (String[])varray3.getArray();
      userEntitlementMap.put(entitlementResultArray[0], entitlementResultArray[1]);
    }

}
catch(SQLException sqlException){
    SERVICE_LOGGER.error("Error while making silverpop call for content id--"+contentId);
}

Glossary

  • TWO_D_TYPE -- array type in Oracle,
  • T_ARRAY -- array type in Oracle,
  • userArray -- array in java,
  • entitlementTags,contentId -- string object in java,
  • conn -- connection object in java,
  • call -- callable statement
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.