Oracle Store Procedure with BLOB parameter

Question

Hi i'm new working with this technologies (Oracle SP ), so I have some problems with it,

To be specific I want to insert a BLOB object over a Store Procedure, currently I work with spring, jboss, java and oracle, my SP is simple than :

PROCEDURE SAVE_DATA(data IN BLOB, date IN DATE) IS
next_id number;
BEGIN
  select s_id.nextval into next_id from dual;

  INSERT INTO DATA_TABLE( id, data , date)
  values
  (next_id, data , date);
  COMMIT;

  EXCEPTION
   WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20101,''||SQLCODE ||'-'||SUBSTR(SQLERRM,1,500));
  END SAVE_FAILED_EMAIL;

So in the java side, I do something like this:

  WrappedConnection wrappedCon = (WrappedConnection) this.getDataSource().getConnection();
        con = (OracleConnection) wrappedCon.getUnderlyingConnection();
        byte[] bytes= IOUtils.toByteArray(input);
        blobObj=con.createBlob(bytes);

  execute(new CallableStatementCreator() {

        public CallableStatement createCallableStatement(Connection con)
                throws SQLException {
             String procedure = "call SAVE_DATA(?,?)";

                CallableStatement stm=con.prepareCall(procedure);

                stm.setBlob(1, blobObj);
                stm.setDate(2, date);
            return stm;
        }
    }, new CallableStatementCallback<Map<Integer,Object>>() {

        public Map<Integer, Object> doInCallableStatement(CallableStatement cs) 
        throws SQLException,DataAccessException {
            cs.execute();
            return null;
        }} 
    );
    con.commit();
    con.close();

But when I run this part of the code I get the next exception that comes form DB side "ORA-22927 invalid LOB locator specified"

ElderMael · Accepted Answer · 2013-11-05 23:49:04Z

This one is a little bit tricky. The first problem you got here is that Oracle needs a propietary BLOB and CLOB instances; here is the javadoc from Spring OracleLobHandler:

While most databases are able to work with DefaultLobHandler, Oracle 9i (or more specifically, the Oracle 9i JDBC driver) just accepts Blob/Clob instances created via its own proprietary BLOB/CLOB API, and additionally doesn't accept large streams for PreparedStatement's corresponding setter methods.

But as you are working in JBoss you will also need a NativeJdbcExtractor so Spring can unwrap the underlying connection from JBoss thread pool wrapper and then insert the lob in Spring JdbcTemplate.

So, here is the code you need to change:

// ...
final byte[] bytes= IOUtils.toByteArray(input);

final OracleLobHandler lobHandler = new OracleLobHandler();
final lobHandler.setNativeJdbcExtractor(new JBossNativeJdbcExtractor());
// ...
new CallableStatementCreator() {

    public CallableStatement createCallableStatement(Connection con)
            throws SQLException {
         String procedure = "call SAVE_DATA(?,?)";

            CallableStatement stm=con.prepareCall(procedure);

            lobHandler.getLobCreator().setLobAsBytes(smt, 1, bytes, bytes.length);
            stm.setDate(2, date);
        return stm;
    }
}
// ...

Procrastinator · Accepted Answer · 2022-02-28 07:46:39Z

For those seeking Spring jdbc template solution to insert BLOB using stored procedure/query, the following syntax worked for me:

Insert via Queries

ByteArrayInputStream inputStream = new ByteArrayInputStream(file.getBytes());
ps.setBlob(1, inputStream);

Insert via Stored Procedure Call

Map<String, Object> inParams = new HashMap<>();
inParams.put("pi_some_id", id);
inParams.put("pi_file_blob",  new SqlLobValue(file.getBytes()));        
SqlParameterSource sqlParameterSource = new MapSqlParameterSource(inParams);        
SqlParameter[] sqlParameters = {
                new SqlParameter("pi_some_id", Types.VARCHAR),
                new SqlParameter("pi_file_blob", Types.BLOB),
                new SqlOutParameter("po_error_flag", Types.VARCHAR),
                new SqlOutParameter("po_message", Types.VARCHAR)};
        
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withoutProcedureColumnMetaDataAccess().
withProcedureName(storedProcName).withCatalogName(packageName).
declareParameters(sqlParameters);
    Map<String, Object> storedProcResult = simpleJdbcCall.execute(sqlParameterSource);

Collectives™ on Stack Overflow

Oracle Store Procedure with BLOB parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related