Ibatis TypeHandler and empty Varchar parameters in types in stored procedure

Question

In a mybatis, spring application I have a TypeHandler which fills data for an ARRAY of STRUCTS required to call Oracle's stored procedure. A blob entry is properly filled and visible in the stored procedure; String entries are not, no string data is sent. No error or warning printed in logs. Data is not null and valid in application. The data simply disappears between application and oracle.

My handler's setParameter implementation looks like this:

public void setParameter(PreparedStatement ps, int i, List<MailAttachment> parameter,
    JdbcType jdbcType) throws SQLException 
{
    List<MailAttachment> attachmentList =  parameter;

    OracleConnection oracleConnection = ps.getConnection().unwrap(OracleConnection.class);

    StructDescriptor structDescriptor = StructDescriptor.createDescriptor(ATTACHMENT, oracleConnection);
    Object[] structs = null;
    structs = new Object[attachmentList == null ? 0 :attachmentList.size()];
    if (attachmentList != null) {  
        //CharacterSet chs = CharacterSet.make(CharacterSet.UTF8_CHARSET);
        for (int index = 0; index < attachmentList.size(); index++) {

            MailAttachment mailAttachment = attachmentList.get(index);
            BLOB blob = null;
            if (mailAttachment.getData() != null){
                blob = BLOB.createTemporary(oracleConnection,false,BLOB.DURATION_SESSION);
               // filling blob works
            }
            CHAR attachName = new CHAR(mailAttachment.getFilename(), CharacterSet.make(CharacterSet.UTF8_CHARSET) );
            CHAR contentType = new CHAR(mailAttachment.getContentType(), CharacterSet.make(CharacterSet.UTF8_CHARSET) );

            STRUCT struct = new STRUCT(structDescriptor, oracleConnection,
                                new Object[] {blob, attachName, contentType, null}
                                );
            structs[index] = struct;
        }
    }

    ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(ATTACHMENT_LIST, oracleConnection);
    ARRAY oracleArray = new ARRAY(arrayDesc, oracleConnection, structs);
    ps.setObject(i, oracleArray);
}

Answer 1

This issue is connected with Oracle JDBC driver and it's support for internationalization.

Remember to include orai18n.jar in classpath/pom file in correct version for your ojdbc jar file.

If orai18n.jar is missing:

• setParameters: varchar2 parameters in Oracle type will be set to null
• getResult/getNonNullParameter: varchar2 parameters will be loaded to java class as "???" string.