CODEWITHSUNDEEP
javaspringoracle-databasejdbc
Shawn Hill
Shawn Hill

Reputation: 1

Spring SimpleJdbcCall on Oracle Stored Procedure: Closed Connection accessing BLOB output parameter

I'm attempting to read a BLOB out of a stored procedure's output parameter in Oracle11gR2 using Spring's SimpleJdbcCall. However, when I call to get the length of the BLOB in order to get the byte array, I get the following error:

java.sql.SQLRecoverableException: Closed Connection
at oracle.sql.BLOB.getDBAccess(BLOB.java:1122) ~[ojdbc6-11.2.0.4.0.jar:11.2.0.4.0]
at oracle.sql.BLOB.length(BLOB.java:156) ~[ojdbc6-11.2.0.4.0.jar:11.2.0.4.0]

This is my stored procedure (names changed to protect the innocent):

FUNCTION my_stored_proc(in_param_1 IN VARCHAR2, in_param_2 IN VARCHAR2, out_param_1 OUT VARCHAR2, out_param_2 OUT VARCHAR2, out_param_3 OUT BLOB) 
  RETURN REF CURSOR
IS
  ...

And this is my Java code (or at least a reasonable facsimile):

SqlParameter[] parameters = {
    getResultParameter(getRowMapper()),
    new SqlParameter("in_param_1", Types.VARCHAR),
    new SqlParameter("in_param_2", Types.VARCHAR),
    new SqlOutParameter("out_param_1", Types.VARCHAR),
    new SqlOutParameter("out_param_2", Types.VARCHAR),
    new SqlOutParameter("out_param_3", Types.BLOB)
};

simpleJdbcCall = new SimpleJdbcCall(getDataSource());
simpleJdbcCall.setFunction(getResultIsCursor());
simpleJdbcCall.setProcedureName("my_stored_proc");
simpleJdbcCall.withoutProcedureColumnMetaDataAccess().declareParameters(parameters);

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("in_param_1", requestId);
params.addValue("in_param_2", compositionId);

Map<String, Object> result = simpleJdbcCall.execute(params);

_errorMessage = (String)result.get("out_param_1");
_account = (String)result.get("out_param_2");

Blob blob = (Blob)result.get("out_param_3");
if (null != blob) {
    final byte[] data = blob.getBytes(1, (int) blob.length());
    ...
}

(I understand that having a stored proc that has output paremeters and that returns a ref cursor may not be the best designed stored proc, but refactoring that is the beginning of unraveling a very large sweater which is well beyond my scope right now. Also, getting the blob data as a byte array in memory is okay here because the file being stored has a pretty small upper bound on its size.)

If possible, I'd like to stick to using the SimpleJdbcCall as it is the cornerstone of a framework I'm using. It seems like a LobHandler would solve this problem, but I haven't been able to find any reference on how to use that with SimpleJdbcCall and an out parameter (just ResultSets).

Upvotes: 0

Views: 3126

Answers (1)

victor.chm
victor.chm

Reputation: 11

I solved this situation with:

SimpleJdbcCall fObtenerDocumentoBinario = new SimpleJdbcCall(jdbcTemplate).withSchemaName(Constantes.SCHEMA) .withCatalogName(Constantes.CATALOG) .withFunctionName(Constantes.F_RECUPERA_BLOB_DOCUMENTO);

fObtenerDocumentoBinario.addDeclaredParameter(new SqlOutParameter("P_DOCUMENTO", OracleTypes.BLOB, "BLOB", new DocumentoBinarioSqlReturnType()));

Where DocumentoBinarioSqlReturnType is defined as:

private class DocumentoBinarioSqlReturnType implements SqlReturnType {

    /**
     * M&eacute;todo sobrescrito getTypeValue.<br>
     * @param cs
     * @param paramIndex
     * @param sqlType
     * @param typeName
     * @return
     * @throws SQLException
     * @see org.springframework.jdbc.core.SqlReturnType#getTypeValue(java.sql.CallableStatement, int, int,
     *      java.lang.String)
     */
    public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName)
                    throws SQLException {

        String pathname = Constantes.getTempDir() + System.currentTimeMillis() + Constantes.EXTENSION_PDF;

        InputStream inStream = null;
        try {

            inStream = cs.getBlob(3).getBinaryStream();

            UtilidadesIO.inputStreamToFile(inStream, pathname);

        } catch (SQLException e) {

            LOGGER.error("Error obteniendo el documento, comprobar en BBDD", e);
            throw e;

        } finally {
            if (null != inStream) {
                try {
                    inStream.close();
                } catch (IOException e) {

                    LOGGER.error(e.getMessage(), e);
                    inStream = null;
                }
            }
        }


        return pathname;

    }

Upvotes: 1

Related Questions