How pass VARRAY(oracle PL/SQL) param to procedure with simplejdbccall?

Question

Here is VARRAY type :

CREATE TYPE NUM_ARR IS VARRAY(3) OF NUMBER(0)
;

Here is Procedure

PROCEDURE REGISTER_CONSENT(P_IPS_ACC_IBAN IN VARCHAR2,
                           P_IPS_BANK_BIC IN VARCHAR2,
                           P_STATUS_ID IN NUMBER,
                           P_PERMS IN NUM_ARR,
                           P_EXP_DATE IN DATE)
    IS
BEGIN

    FOR PERM_ID in P_PERMS.first ..P_PERMS.last
        LOOP
            INSERT INTO MLB_TEST.IPS_OB_CONSENT_PERMISSION(ID, CONSENT_ID, PERMISSION_ID, EXPIRATION_DATE)
            VALUES (IPS_OB_CONSENT_PERMISSION_SEQ.nextval,
                    IPS_OB_CONSENT_SEQ.currval,
                    PERM_ID,
                    P_EXP_DATE);
        END LOOP;

END;

Here is simplejdbccall (java)

        SimpleJdbcCall caller = new SimpleJdbcCall(dataSource);
    caller.withSchemaName("MLB_TEST")
            .withCatalogName("MLB_OB")
            .withProcedureName("REGISTER_CONSENT")
            .declareParameters(new SqlParameter("P_PERMS", Types.ARRAY, "NUM_ARR"));
    MapSqlParameterSource param = new MapSqlParameterSource()
            .addValue("P_IPS_ACC_IBAN", consent.getIban())
            .addValue("P_IPS_BANK_BIC", consent.getBankBic())
            .addValue("P_STATUS_ID", consent.getConsentStatusId())
            .addValue("P_PERMS", consent.getPermissionsIdList(), Types.ARRAY, "NUM_ARR")
            .addValue("P_EXP_DATE", consent.getExpirationDate());
    caller.execute(param);

This is the error i get : Caused by: java.sql.SQLException: Fail to convert to internal representation: [Ljava.lang.Integer;@40f5407d at oracle.sql.ARRAY.toARRAY(ARRAY.java:301)

I have tried with

• Integer array Ex : new Integer[3];
• Linken list Ex : new LinkedList<>();
• int array Ex : new int[]{1,2,3};

And nothing works , i am getting same error each time .

Answer 1

Unfortunately you need some vendor specific magic to get this to work with JDBC (not specific to Spring JDBC).

Here is a class we use called OracleSqlArrayValue. It was basically lifted from the defunct Spring JDBC extensions library although it handles unwrapping the connection properly. You will need the Oracle JDBC driver in your classpath during compile due to the import of OracleConnection.

I had additionally posted much of the below to SO here a while back.

import java.sql.Connection;
import java.sql.SQLException;

import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;

import oracle.jdbc.OracleConnection;

public class OracleSqlArrayValue<T> extends AbstractSqlTypeValue {
    private T[] values;
    private String defaultTypeName;

    public OracleSqlArrayValue(T[] values) {
        this.values = values;
    }

    public OracleSqlArrayValue(T[] values, String defaultTypeName) {
        this.values = values;
        this.defaultTypeName = defaultTypeName;
    }

    @Override
    protected Object createTypeValue(Connection conn, int sqlType,
            String typeName) throws SQLException {

        if (typeName == null && defaultTypeName == null) {
            throw new InvalidDataAccessApiUsageException(
                    "No type named defined. Instantiate class with default type name.");
        }

        if (!conn.isWrapperFor(OracleConnection.class)) {
            throw new InvalidDataAccessApiUsageException(
                    "Unable to unwrap OracleConnection. Ensure you are connecting to an Oracle DB.");
        }

        return conn.unwrap(OracleConnection.class).createOracleArray(
                typeName != null ? typeName : defaultTypeName, values);
    }
}

You would then use it like this.

SimpleJdbcCall caller = new SimpleJdbcCall(dataSource);
caller.withSchemaName("MLB_TEST")
        .withCatalogName("MLB_OB")
        .withProcedureName("REGISTER_CONSENT")
        .declareParameters(new SqlParameter("P_PERMS", Types.ARRAY, "NUM_ARR"));
MapSqlParameterSource param = new MapSqlParameterSource()
        .addValue("P_IPS_ACC_IBAN", consent.getIban())
        .addValue("P_IPS_BANK_BIC", consent.getBankBic())
        .addValue("P_STATUS_ID", consent.getConsentStatusId())
        .addValue("P_PERMS", new OracleSqlArrayValue<Integer>(consent.getPermissionsIdList(), "NUM_ARR"))
        .addValue("P_EXP_DATE", consent.getExpirationDate());
caller.execute(param);