CODEWITHSUNDEEP
javaoracle-databasespringstored-proceduresoracle10g
Cris
Cris

Reputation: 5007

Storing binary into BLOB

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

Upvotes: 2

Views: 2460

Answers (1)

Santosh
Santosh

Reputation: 17903

  1. The datatype oracle.sql.BFILE is not the same as oracle.sql.BLOB (for which setting Type.BLOB will work). Check this.
  2. 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]])
  3. IMHO, the approach you have taken seems valid. A byte[] is safe to cast in a Binary File (BFILE)
  4. 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.

Upvotes: 4

Related Questions