Storing binary into BLOB

Question

I have to use a store procedure via an Oracle DB.

I used spring to call it

public StoreOperation(JdbcTemplate jdbcTemplate) {
  super(jdbcTemplate, STORE);
  declareParameter(new SqlParameter("in_binary_data", Types.BINARY));
  //declareParameter(new SqlParameter("in_binary_data", OracleTypes.BLOB));
  declareParameter(new SqlParameter("in_int_id", Types.INTEGER));
  declareParameter(new SqlOutParameter("out_int_id", Types.BIGINT));
  declareParameter(new SqlOutParameter("out_checksum", Types.VARCHAR));
  compile();
}

public Map execute(byte[] signedPdf, Long intId) {
  Map inParams = new HashMap(2);
  inParams.put("in_binary_data", signedPdf);
  inParams.put("in_int_id", intId);
  return execute(inParams);
}

Apparenlty using Types.BINARY works

declareParameter(new SqlParameter("in_binary_data", Types.BINARY));

Using BLOB does not allow

declareParameter(new SqlParameter("in_binary_data", Types.BLOB));

==> java.lang.ClassCastException: [B cannot be cast to oracle.sql.BFILE]]

It is a legacy stored procedure so i do not have access to the Blob to stream to it so I am forced to use byte[]

It might be a problem if the sql type i use is Types.BINARY and the database type is BLOB ?

So storing a byte[] in a BLOB without using the java.sql.types.Blob and streaming is ok ?

Thanks

Santosh · Accepted Answer · 2012-09-12 14:28:00Z

4

The datatype oracle.sql.BFILE is not the same as oracle.sql.BLOB (for which setting Type.BLOB will work). Check this.
Your are trying to save Type.BLOB into a field of type BFILE and hence the casting that driver is trying to do is failing. ( java.lang.ClassCastException: [B cannot be cast to oracle.sql.BFILE]])
IMHO, the approach you have taken seems valid. A byte[] is safe to cast in a Binary File (BFILE)
Please also try using oracle specific type oracle.jdbc.OracleTypes.BFILE (This class is bundled with driver jar) instead of java.sql.BLOB.(I am not sure how spring will handle this)

Additionally, please check this link and (this as well )for dealing with BFILE types. This involves using Oracle specific class.

edited Sep 12, 2012 at 14:28

answered Sep 12, 2012 at 13:18

Santosh

18k4 gold badges58 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cris Over a year ago

I get byte[] and i pass this to the stored procedure that uses BLOB as type to store

Collectives™ on Stack Overflow

Storing binary into BLOB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related