带有 BLOB 参数的 Oracle 存储过程

Question

嗨，我是使用这种技术（Oracle SP）的新手，所以我有一些问题，

具体来说，我想在存储过程中插入一个 BLOB 对象，目前我使用 spring、jboss、java 和 oracle，我的 SP 比：

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;

所以在java方面，我做了这样的事情：

  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();

但是当我运行这部分代码时，我得到来自数据库端的下一个异常"ORA-22927 指定的 LOB 定位器无效"

Answer 1

这个有点棘手。你在这里遇到的第一个问题是 Oracle 需要一个专有的 BLOB 和 CLOB 实例;以下是Spring OracleLobHandler的javadoc：

虽然大多数数据库都能够与DefaultLobHandler一起使用，但Oracle。 9i（或者更具体地说，Oracle 9i JDBC驱动程序）只接受 通过其自己的专有 BLOB/CLOB API 创建的 Blob/Clob 实例，以及 此外，不接受 PreparedStatement 的大型流 相应的二传手方法。

但是当你在JBoss中工作时，你还需要一个NativeJdbcExtractor，这样Spring就可以从JBoss线程池包装器中解开底层连接，然后在Spring JdbcTemplate中插入lob。

因此，这是您需要更改的代码：

// ...
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;
    }
}
// ...

Answer 2

对于那些寻求Spring jdbc模板解决方案以使用存储过程/查询插入BLOB的人来说，以下语法对我有用：

通过查询插入

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

通过存储过程调用插入

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);