My goal here is to successfully call the stored procedure while passing in the memberDetails which is a custom oracle object as seen in the screenshot, however it is complaining that the the column type is invalid even with the custom type handler i created to send it as an object
error i am receiving the issue is only with the memberDetails input so i am removing the other inputs and outputs (for simplicity sake)
Cause: java.sql.SQLException: Invalid column type\n### The error may exist in mybatis/commission-insertmember-mapper.xml\n### The error may involve insertmember.insertMemberSP-Inline\n### The error occurred while setting parameters\n### SQL: { CALL PKG_OLB.PRC_PREINSTALL_INSMBR_DETAILS( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }\n
mapper xml file
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="gi.emp.billing.commission.insertmember">
<select id="insertMemberSP" parameterType="InsertMemberEntity" statementType="CALLABLE">
{ CALL PKG_OLB.PRC_PREINSTALL_INSMBR_DETAILS(
//input
(other inputs...)
**#{memberDetail, javaType=Object, jdbcType=JAVA_OBJECT, jdbcTypeName=INSERT_MEMBER_DETAILS, mode=IN, typeHandler=MemberDetailArrayTypeHandler}
//outputs
(other outputs...)
) }
</select>
</mapper>
custom type handler for memberDetails
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;
import java.sql.Types;
import java.util.List;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import oracle.jdbc.OracleConnection;
public class MemberDetailArrayTypeHandler implements TypeHandler {
@Override
public void setParameter(PreparedStatement preparedStatement, int i, Object o, JdbcType jdbcType)
throws SQLException {
List<MemberDetailEntity> objects = (List<MemberDetailEntity>) o;
Connection hikariCon = preparedStatement.getConnection();
if (hikariCon.isWrapperFor(OracleConnection.class)) {
OracleConnection connection = hikariCon.unwrap(OracleConnection.class);
Struct[] structs = new Struct[objects.size()];
for (int index = 0; index < objects.size(); index++) {
MemberDetailEntity memberDetailEntity = objects.get(index);
Object[] params = new Object[35];
params[0] = memberDetailEntity.getAdrdAddrType();
params[1] = memberDetailEntity.getBenCatNo();
params[2] = memberDetailEntity.getBgrpKey();
params[3] = memberDetailEntity.getBirthDt();
params[4] = memberDetailEntity.getCatalogNumber();
params[5] = memberDetailEntity.getCity();
params[6] = memberDetailEntity.getControlNumber();
params[7] = memberDetailEntity.getCountry();
params[8] = memberDetailEntity.getCtrId();
params[9] = memberDetailEntity.getEffectDt();
params[10] = memberDetailEntity.getEmail();
params[11] = memberDetailEntity.getFirstName();
params[12] = memberDetailEntity.getHours();
params[13] = memberDetailEntity.getJoinCoDt();
params[14] = memberDetailEntity.getJoinSchDt();
params[15] = memberDetailEntity.getLastName();
params[16] = memberDetailEntity.getLgDescript();
params[17] = memberDetailEntity.getLine1();
params[18] = memberDetailEntity.getLine2();
params[19] = memberDetailEntity.getLine3();
params[20] = memberDetailEntity.getMarStatCd();
params[21] = memberDetailEntity.getMbgpKey();
params[22] = memberDetailEntity.getMbstMbrStatCd();
params[23] = memberDetailEntity.getMiddleInitial();
params[24] = memberDetailEntity.getNamePrefix();
params[25] = memberDetailEntity.getNatlIdNo();
params[26] = memberDetailEntity.getPenEarnings();
params[27] = memberDetailEntity.getPhone();
params[28] = memberDetailEntity.getPhoneExt();
params[29] = memberDetailEntity.getPostCd();
params[30] = memberDetailEntity.getSalDescript();
params[31] = memberDetailEntity.getSexCd();
params[32] = memberDetailEntity.getSmokerCd();
params[33] = memberDetailEntity.getState();
params[34] = memberDetailEntity.getXpirDt();
structs[index] = connection.createStruct("INSERT_MEMBER_DETAILS", params);
}
preparedStatement.setObject(i, structs, Types.JAVA_OBJECT);
}
}
@Override
public Object getResult(ResultSet resultSet, String s) throws SQLException {
return null;
}
@Override
public Object getResult(ResultSet resultSet, int i) throws SQLException {
return null;
}
@Override
public Object getResult(CallableStatement callableStatement, int i) throws SQLException {
return null;
}
}
when setting as an array
structs[index] = connection.createStruct("INSERT_MEMBER_DETAILS", params);
}
Array oracleArray = ((OracleConnection) connection).createOracleArray("INSERT_MEMBER_DETAILS", structs);
preparedStatement.setArray(i, oracleArray);
receive this error
Cause: java.lang.ClassCastException: oracle.sql.StructDescriptor cannot be cast to oracle.sql.ArrayDescriptor\n###
UserListTypeHandler
in this demo.INSERT_MEMBER_DETAILS
objects, the procedure must take a table type parameter, I think. See theCreateDB.sql
in the demo. The original answer might help as well.INSERT_MEMBER_DETAILS
and you cannot change the SP definition, you can only pass one object at a time from the Java side. In the demo,UserTypeHandler
is for single object. See how it's used in the mapper.