image upload problem

Question

I am writting the code to upload file on oracle as BLOB but while saving that file its giving me the exception java.sql.SQLException: ORA-01460: unimplemented or unreasonable

following are the functions to convert my blob type to byteArray

private byte[] convertToByteArray(Blob fromBlob) {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      try {
       return convertToByteArrayImpl(fromBlob, baos);
      } catch (SQLException e) {
       throw new RuntimeException(e);
      } catch (IOException e) {
       throw new RuntimeException(e);
      } finally {
       if (baos != null) {
        try {
         baos.close();
        } catch (IOException ex) {
        }
       }
      }
     }
private byte[] convertToByteArrayImpl(Blob fromBlob, ByteArrayOutputStream baos)
throws SQLException, IOException {
byte[] buf = new byte[4000];
InputStream is = fromBlob.getBinaryStream();
try {
 for (;;) {
  int dataSize = is.read(buf);

  if (dataSize == -1)
   break;
  baos.write(buf, 0, dataSize);
 }
} finally {
 if (is != null) {
  try {
   is.close();
  } catch (IOException ex) {
  }
 }
}
return baos.toByteArray();

}

I think its because my byte length is above 4000 but, what is the solution to save more than 4000 bytes?

Answer 1

One of the quirks of working with BLOBs in earlier versions of Oracle was that we could not include the full BLOB in an insert statement. It had to be a two-stage process.

The 4000 byte limit is the key, because that figure is the upper bound of what Oracle considers to be a SQL datatype. So Oracle can handle LOBs of 4000 bytes or less without a snatch but hurls the ORA-01460 exception if we ask it to accept a larger LOB. The workaround was to insert the row with an empty_blob() placeholder, and then update the new row.

insert into t42 (id, blob_col) values (1, empty_blob());

update t42 
set blob_col = some_blob_variable
where id = 1;

This might be the cause of your problem; it is difficult to tell without seeing the whole of your code.

NB: As far as I can tell the preceding does not apply to Oracle 11g: we can now easily insert rows containing oversize BLOBs.