Spring JDBC - Passing in ARRAY of BLOBs to SQL Function

The error message appears to indicate the Oracle JDBC driver doesn't know what to do with the javax.sql.rowset.serial.SerialBlob object you've passed to it.

Try creating the Blob objects using Connection.createBlob instead. In other words, try replacing the following loop

        for(int x = 0; x < byteFiles.size(); x++){
            fileBlobs.add(new javax.sql.rowset.serial.SerialBlob(byteFiles.get(x)));
        }

with

        Connection conn = dataSource.getConnection();
        for(int x = 0; x < byteFiles.size(); x++){
            Blob blob = conn.createBlob();
            blob.setBytes(1, byteFiles.get(x));
            fileBlobs.add(blob);
        }

Also, make sure that your parameter names are consistent between your SimpleJdbcCall and your stored function. Your SimpleJdbcCall declares the BLOB array parameter with name p_data but your stored function declaration uses p_files. If the parameter names are not consistent you are likely to get an Invalid column type error.

However, had I run the above test with a stored function of my own that actually did something with the BLOB values passed in, instead of just hard-coding a return value, I might have found that this approach didn't work. I'm not sure why, I'd probably have to spend some time digging around in the guts of Spring to find out.

I tried replacing the Blob values with Spring SqlLobValues, but that didn't work either. I guess Spring's SqlArrayValue<T> type doesn't handle Spring wrapper objects for various JDBC types.

So I gave up on a Spring approach and went back to plain JDBC:

import oracle.jdbc.OracleConnection;

// ...

        OracleConnection conn = dataSource.getConnection().unwrap(OracleConnection.class);

        List<Blob> fileBlobs = new ArrayList<>();
        for(int x = 0; x < byteFiles.size(); x++){
            Blob blob = conn.createBlob();
            blob.setBytes(1, byteFiles.get(x));
            fileBlobs.add(blob);
        }

        Array array = conn.createOracleArray("BLOB_ARRAY",
            fileBlobs.toArray(new Blob[fileBlobs.size()]));

        CallableStatement cstmt = conn.prepareCall("{? = call insertFiles(?, ?)}");
        cstmt.registerOutParameter(1, Types.NUMERIC);
        cstmt.setInt(2, userId);
        cstmt.setArray(3, array);

        cstmt.execute();

        int result = cstmt.getInt(1);

I've tested this with the stored function you've now included in your question, and it is able to call this function and insert the BLOBs into the database.

I'll leave it up to you to do what you see fit with the variable result and to add any necessary cleanup or transaction control.

However, while this approach worked, it didn't feel right. It didn't fit the Spring way of doing things. It did at least prove that what you were asking for was possible, in that there wasn't some limitation in the JDBC driver that meant you couldn't use BLOB arrays. I felt that there ought to be some way to call your function using Spring JDBC.

I spent some time looking into the ORA-22922 error and concluded that the underlying problem was that the Blob objects were being created using a different Connection to what was being used to execute the statement. The question then becomes how to get hold of the Connection Spring uses.

After some further digging around in the source code to various Spring classes, I realised that a more Spring-like way of doing this is to replace the SqlArrayValue<T> class with a different one specialised for BLOB arrays. This is what I ended up with:

import java.sql.Array;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

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

public class SqlBlobArrayValue extends AbstractSqlTypeValue {

    private List<byte[]> values;

    private String defaultTypeName;

    public SqlBlobArrayValue(List<byte[]> values) {
        this.values = values;
    }

    public SqlBlobArrayValue(List<byte[]> values, String defaultTypeName) {
        this.values = values;
        this.defaultTypeName = defaultTypeName;
    }

    protected Object createTypeValue(Connection conn, int sqlType, String typeName)
            throws SQLException {
        if (typeName == null && defaultTypeName == null) {
            throw new InvalidDataAccessApiUsageException(
                    "The typeName is null in this context. Consider setting the defaultTypeName.");
        }

        Blob[] blobs = new Blob[values.size()];
        for (int i = 0; i < blobs.length; ++i) {
            Blob blob = conn.createBlob();
            blob.setBytes(1, values.get(i));
            blobs[i] = blob;
        }

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

This class is heavily based on SqlArrayValue<T>, which is licensed under Version 2 of the Apache License. For brevity, I've omitted comments and a package directive.

With the help of this class, it becomes a lot easier to call your function using Spring JDBC. In fact, you can replace everything after the call to uploadFiles.compile() with the following:

        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("p_files", new SqlBlobArrayValue(byteFiles, "BLOB_ARRAY"))
                .addValue("p_userId", userId);

        Map<String, Object> results = uploadFiles.execute(in);