java oracle-database mybatis ibatis typehandler

SQL state [99999]; error code [17068] nested exception is java.sql.SQLException:

I'm trying to call a stored procedure from my java application using myBatis.

My db type:

create or replace TYPE
  ARR_TEXT_OUT AS VARRAY(20000) OF VARCHAR(200);

My stored procedure (I think you only need the spec):

create or replace PACKAGE
  PKG_AMM_LOOKOUT AS 

  PROCEDURE pr_resolveMDL101(IN_id_processo      IN  NUMBER
                                ,OUT_text OUT ARR_TEXT_OUT
                                ); 

END PKG_AMM_LOOKOUT;

My class which wraps IN and OUT parameters:

package com.ubiss.domain.lookoutMdl;

import java.io.Serializable;

public class Mdl101Parameter implements Serializable {
    private static final long serialVersionUID = 1L;
    private Long idProcesso;
    private OutText outText;
    
    public Mdl101Parameter(Long idProcesso, OutText outText) {
        this.idProcesso = idProcesso;
        this.outText = outText;
    }
    
    public Mdl101Parameter() {}

    public Long getIdProcesso() {
        return idProcesso;
    }

    public void setIdProcesso(Long idProcesso) {
        this.idProcesso = idProcesso;
    }

    public OutText getOutText() {
        return outText;
    }

    public void setOutText(OutText outText) {
        this.outText = outText;
    }

    @Override
    public String toString() {
        return "Mdl101Parameter [idProcesso=" + idProcesso + ", outText=" + outText + "]";
    }
}

My class which wraps the varray type:

package com.ubiss.domain.lookoutMdl;

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.Arrays;
import java.util.Map;

public class OutText implements Array, SQLData{
    private String[] outText;

    public OutText(String[] outText) {
        this.outText = outText;
    }
    
    public OutText() {}

    public String[] getOutText() {
        return outText;
    }

    public void setOutText(String[] outText) {
        this.outText = outText;
    }

    @Override
    public String toString() {
        return "OutText [outText=" + Arrays.toString(outText) + "]";
    }

    @Override
    public String getBaseTypeName() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public int getBaseType() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Object getArray() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Object getArray(Map<String, Class<?>> map) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Object getArray(long index, int count) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException                                                                                           {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public ResultSet getResultSet() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public ResultSet getResultSet(long index, int count) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void free() throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public String getSQLTypeName() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        // TODO Auto-generated method stub
        
    }
}

My TypeHandler implementation for the varray parameter:

package com.ubiss.domain.lookoutMdl;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import oracle.jdbc.driver.OracleConnection;

public class OutTextTypeHandler implements TypeHandler<OutText> {

    @Override
    public void setParameter(PreparedStatement ps, int i, OutText parameter, JdbcType jdbcType) throws SQLException {
        OracleConnection conn = ps.getConnection().unwrap(OracleConnection.class);
        List<Struct> structs = new ArrayList<Struct>();
        if (parameter != null) {
            Object[] obj = parameter.getOutText();
            structs.add(conn.createStruct("SUPPORTO.ARR_TEXT_OUT", obj));
        }
        Array array = conn.createOracleArray("SUPPORTO.ARR_TEXT_OUT", structs.toArray());
        ps.setArray(i, array);
        array.free();
    }

    @Override
    public OutText getResult(ResultSet rs, String columnName) throws SQLException {
        // TODO Auto-generated method stub
        return (OutText) rs.getArray(columnName);
    }

    @Override
    public OutText getResult(ResultSet rs, int columnIndex) throws SQLException {
        // TODO Auto-generated method stub
        return (OutText) rs.getArray(columnIndex);
    }

    @Override
    public OutText getResult(CallableStatement cs, int columnIndex) throws SQLException {
        // TODO Auto-generated method stub
        return (OutText) cs.getArray(columnIndex);
    }

}

My mapper Errore101Mapper.xml:

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ubiss.dao.lookoutMdl.errori.Errore101Dao">
    <select id="resolve" statementType="CALLABLE"
        parameterType="com.ubiss.domain.lookoutMdl.Mdl101Parameter"
        resultMap="resolveResultMap">
        {call SUPPORTO.PKG_AMM_LOOKOUT.pr_resolveMDL101(
        #{idProcesso, mode=IN, jdbcType=NUMERIC},
        #{outText,jdbcType=ARRAY, mode=OUT}
        )}
    </select>
    
    <resultMap id="resolveResultMap"
        type="com.ubiss.domain.lookoutMdl.Mdl101Parameter">
        <id column="IN_id_processo" property="idProcesso" />
        <id column="OUT_text" property="outText" typeHandler="com.ubiss.domain.lookoutMdl.OutTextTypeHandler"/>
    </resultMap>
</mapper>

My myBatisV3-config.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <setting name="jdbcTypeForNull" value="NULL" />
    </settings>
    
    <typeAliases>
    </typeAliases>
    
    <typeHandlers>
        <typeHandler handler="com.ubiss.domain.logger.ElencoCampiTypeHandler" javaType="com.ubiss.domain.logger.ElencoCampiModificati"/>
        <typeHandler handler="com.ubiss.domain.lookoutMdl.OutTextTypeHandler" javaType="com.ubiss.domain.lookoutMdl.OutText"/>      
    </typeHandlers>
    
    <mappers>
        <mapper resource="/configImiweb/maps/GestioneAnagraficaMapper.xml" />
        <mapper resource="/configImiweb/maps/CensimentoTitoliMapper.xml" />
        <mapper resource="/configImiweb/maps/RisottomissioneMovimentiEseguiti.xml" />
        <mapper resource="/configImiweb/maps/GestionePortafoglioClienteMapper.xml" />
        <mapper resource="/configImiweb/maps/AggiornamentoAnagraficaTitoliMapper.xml" />
        <mapper resource="/configImiweb/maps/LoggerMapper.xml" />
        <mapper resource="/configImiweb/maps/PiattaformeInformativaMapper.xml" />
        <mapper resource="/configImiweb/maps/GestioneDipendenzeMapper.xml" />
        <mapper resource="/configImiweb/maps/GestioneAgreementMapper.xml" />
        <mapper resource="/configImiweb/maps/GestioneTabellaServiziClienteMapper.xml" />
        <mapper resource="/configImiweb/maps/GestionePiattaformaClienteMapper.xml" />
        <mapper resource="/configImiweb/maps/GestioneAgreementClienteMapper.xml" />
        <mapper resource="/configImiweb/maps/AggiornamentoPrezziOTCMapper.xml" />
        <mapper resource="/configImiweb/maps/GestioneMercatoMapper.xml" />
        <mapper resource="/configImiweb/maps/GestioneStrumentoMapper.xml" />
        <mapper resource="/configImiweb/maps/TbAccessoSottorubricaMapper.xml" />
        <mapper resource="/configImiweb/maps/TbPersonaMapper.xml" />
        <mapper resource="/configImiweb/maps/TbLoginMapper.xml" />
        <mapper resource="/configImiweb/maps/TbDossierMapper.xml" />
        <mapper resource="/configImiweb/maps/TbAccessoInternetMapper.xml" />
        <mapper resource="/configImiweb/maps/TbCashAmountsMapper.xml" />
        <mapper resource="/configImiweb/maps/TbGruppoMapper.xml" />
        <mapper resource="/configImiweb/maps/TbUtenzaRtkMapper.xml" />
        <mapper resource="/configImiweb/maps/TbCcMapper.xml" />
        <mapper resource="/configImiweb/maps/TbHbAccessoCcMapper.xml" />
        <mapper resource="/configImiweb/maps/TbRelClienteGruppoMapper.xml" />
        <mapper resource="/configImiweb/maps/ScadenzaDerivatiMapper.xml" />
        <mapper resource="/configImiweb/maps/EsitoProcessiMapper.xml" />     
        <mapper resource="/configImiweb/maps/StoricoOrdiniMapper.xml" />
        <mapper resource="/configImiweb/maps/Errore101Mapper.xml" />                    
    </mappers>
</configuration>

During the build and the start of the jboss application I don't get any errors. But then when the dao method resolve() is called I get the following error:

SQL state [99999]; error code [17068]; ; nested exception is java.sql.SQLException: error while setting parameters

I tried to but a breakpoint in the methods of the TypeHandler but it seems it does not come into those methods and the error occurs before. And it also seems like I can't use the ArrayTypeHandler built in iBatis because I have a 3.4 version of batis.

From the stack trace it seems like the error is setting the OUT parameter of the stored procedure (OUT_text OUT ARR_TEXT_OUT) Please let me know if you have any clue of what the cause could be.

Solution

About the `OutText` class

I might be missing something, but it is unnecessary to implement java.sql.Array or java.sql.SQLData.

About the mapper statement

Oracle procedure does not return result set (cursor), so <select resultMap="..."> is not used. You should use <update> or <insert>, probably.
As it's a custom type, jdbcTypeName must be specified.
It seems that a globally registered type handler is not applied to OUT parameters. You may have to specify typeHandler explicitly in the parameter reference.

<update id="resolve" statementType="CALLABLE">
  {call SUPPORTO.PKG_AMM_LOOKOUT.pr_resolveMDL101(
    #{idProcesso, mode=IN, jdbcType=NUMERIC},
    #{outText,jdbcType=ARRAY,jdbcTypeName=ARR_TEXT_OUT,mode=OUT,typeHandler=test.OutTextTypeHandler}
  )}
</update>

About the type handler

For OUT params, TypeHandler#getResult() is used. And you cannot just cast the result of getArray() like that. I also noticed that you imported a wrong OracleConnection.
OutTextTypeHandler should look something like this.

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import oracle.jdbc.OracleArray;
import oracle.jdbc.OracleConnection;

public class OutTextTypeHandler extends BaseTypeHandler<OutText> {

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, OutText parameter, JdbcType jdbcType)
      throws SQLException {
    OracleConnection con = ps.getConnection().unwrap(OracleConnection.class);
    Array array = con.createOracleArray("ARR_TEXT_OUT", parameter.getOutText());
    try {
      ps.setArray(i, array);
    } finally {
      array.free();
    }
  }

  @Override
  public OutText getNullableResult(ResultSet rs, String columnName) throws SQLException {
    return oracleArrayToOutText((OracleArray) rs.getArray(columnName));
  }

  @Override
  public OutText getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
    return oracleArrayToOutText((OracleArray) rs.getArray(columnIndex));
  }

  @Override
  public OutText getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    return oracleArrayToOutText((OracleArray) cs.getArray(columnIndex));
  }

  private OutText oracleArrayToOutText(OracleArray array) throws SQLException {
    if (array == null) {
      return null;
    }
    OutText result = new OutText((String[]) array.getArray());
    array.free();
    return result;
  }
}

Here is the demo project that I used to verify.
https://github.com/harawata/mybatis-issues/tree/master/so-76768574

SQL state [99999]; error code [17068] nested exception is java.sql.SQLException:

About the OutText class

About the mapper statement

About the type handler

About the `OutText` class