1

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###

Custom Oracle Object enter image description here enter image description here

5
  • As there are multiple custom objects, the parameter needs to be set as an array. See the UserListTypeHandler in this demo.
    – ave
    Commented Jan 17, 2022 at 17:51
  • When setting as an array i received this error Cause: java.lang.ClassCastException: oracle.sql.StructDescriptor cannot be cast to oracle.sql.ArrayDescriptor\n### updating the typehandler above so you can see how i am setting as an array
    – scocuzza
    Commented Jan 17, 2022 at 18:54
  • To receive multiple INSERT_MEMBER_DETAILS objects, the procedure must take a table type parameter, I think. See the CreateDB.sql in the demo. The original answer might help as well.
    – ave
    Commented Jan 17, 2022 at 19:09
  • I see, but INSERT_MEMBER_DETAILS is not a table type parameter, it is an OBJECT - and that is what the stored procedure expects as the input.. there is no way i can satisy that in the typehandler ? or does the change have to be done on the SP to take a table type instead
    – scocuzza
    Commented Jan 17, 2022 at 21:03
  • If the SP is defined to take single 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.
    – ave
    Commented Jan 17, 2022 at 21:26

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.