• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 843
  • Last Modified:

Oracle BLOB Writes Twice the Number of Bytes to the OutputStream


I have a very strange problem when working with oracle.sql.BLOB; I cannot figure out what is causing my BLOB's output stream doubling the amount of data inserted into the Oracle database. I have a table that contains two BLOB objects(image files) and the goal is to insert two images into each row by a BLOB stream.

For example, if the image_bin data size is 820k and image_thumbnail size is 105k, this code actually writes 1640k (double) and 210k (double) the amount of bytes to each BLOB column, respectively. However, the print method at the end of insertBlob() indicates a correct number of bytes being written to the output stream (820k for the first image file and 105k for the second image file).

I know for the fact the retrieval method (not mentioned here) doesn't duplicate the bytes when it reads the data from the BLOB column because I have written another test program that does not utilize oracle.sql.BLOB but instead uses PreparedStatement's setBinaryStream(index, InputStream, size_of_file) to insert a binary image into BLOB column and it accurately writes the exact image size (no double sizing) to the database -- but not with BLOB. Here's a snippet of my code, note that the actual writing occurs in insertBlob():

Any help would be appreciated
private void insertBlob(java.sql.Blob lobImage, String imgName) 
                           throws SQLException, IOException {
    File imgFile                   = null;
    FileInputStream imgOnDisk  = null;
    OutputStream imgToDB           = null;
    int bufferSize                 = 0;
    oracle.sql.BLOB blobImage      = (oracle.sql.BLOB) lobImage;
    try {
      int bytesRead    = 0;
      long bytesWritten = 0L;
      byte[] byteBuffer = null; 
      bufferSize = blobImage.getBufferSize();
      byteBuffer = new byte[bufferSize];
      imgFile = new File(imgName);
      // Stream to read the file from the local disk
      imgOnDisk = new FileInputStream(imgFile);
      // Stream to write to the Oracle database
      imgToDB = blobImage.setBinaryStream(imgFile.length());
      while ((bytesRead = imgOnDisk.read(byteBuffer)) != -1 )  {
        imgToDB.write(byteBuffer, 0, bytesRead);
        bytesWritten += bytesRead;
      } // end of while
      System.out.print("Done. " + bytesWritten + "-bytes inserted, buffer size: " +
                       bufferSize + "-bytes, chunk size: " + 
                       blobImage.getChunkSize() + ".\n");
    } catch (SQLException sqlEx) {
      System.out.println("SQLException caught: JDBCOracleLOBBinaryStream.insertBlob()");
      throw sqlEx;
    } catch (IOException ioe) {
      System.out.println("IOException caught: JDBCOracleLOBBinaryStream.insertBlob()");
      throw ioe;
    } finally {
      try {
        if (imgOnDisk != null ) {
        if (imgToDB != null ) {
      } catch (IOException ioeClosing) {
        System.out.println("IOException caught: JDBCOracleLOBBinaryStream.insertBlob() " +
                           "on closing stream.");
    } // end of finally
  public void insertImageIntoOracleDB() throws SQLException, IOException {
    PreparedStatement pstmt = null;
    Statement         stmt  = null;
    ResultSet rset          = null;
    try {
      this.getConnection(_driver, _host, _port, _database, _user, _pass);
      pstmt = conn.prepareStatement("INSERT INTO " + 
               " gallery_v (picture_id, picture_title, image_bin, image_thumbnail) " +
               " VALUES (?, ?, EMPTY_BLOB(), EMPTY_BLOB())");
      pstmt.setInt(1, picID);
      pstmt.setString(2, picTitle);
      stmt = conn.createStatement();
      rset = stmt.executeQuery("SELECT image_bin, image_thumbnail FROM gallery_v " +
                               " WHERE picture_id = " + picID + "  FOR UPDATE");
      int rsetCount                = 0;
      oracle.sql.BLOB imgBlob      = null;
      oracle.sql.BLOB imgThumbBlob = null;
      while (rset.next()) {
        imgBlob = ((OracleResultSet) rset).getBLOB("image_bin");
        System.out.print("Inserting " + img + "... ");
        insertBlob(imgBlob, img);
        imgThumbBlob = ((OracleResultSet) rset).getBLOB("image_thumbnail");
        System.out.print("Inserting " + imgThumb + "... ");
        insertBlob(imgThumbBlob, imgThumb);
      System.out.println("\nNumber of rows updated: " + rsetCount);
    } catch (SQLException sqlEx) {
      System.out.println("SQLException caught: JDBCOracleLOBBinaryStream.insertImageIntoOracleDB()");
      throw sqlEx;
    } catch (IOException ioe) {
      throw ioe; 
    } finally {
      try {
        if (rset != null) {
        if (pstmt != null) { 
        if (stmt != null) { 
      } catch (SQLException closingSqlEx) {
        System.out.println("SQLException caught: JDBCOracleLOBBinaryStream.insertImageIntoOracleDB() " +
                           "on closing ResultSet or PreparedStatement.");
    } // end of finally

Open in new window

  • 2
1 Solution
Sounds to me like you've got the column set in double-byte (Unicode) mode somehow
oxbloodAuthor Commented:

Thanks for your reply. This is a raw transmission of binary bytes (not an encoded bytes) so  there is no conversion performed by the driver to and from the database. If the LOB object was a CLOB type, I would consider that.

But in either case, I found the culprit and I feel like a moron. The new BLOB#setBinaryStream() method takes a position of where the data is read from in the stream given to it. So the following code:

imgToDB = blobImage.setBinaryStream(imgFile.length());

Starts off from the end of the file. Now I don't understand how this position would result in the duplicated amount of bytes read from the binary file (an image here) to the output stream! The correct line should be:

imgToDB = blobImage.setBinaryStream(0L);

ARGH!!! Now everything works as it expected. I gotta read the API's more carefully as I was expecting the same semantic parameter as PreparedStatement#setBinaryStream() which takes the length of the stream as one of its parameters.
Glad you got it sorted out
PAQed with points refunded (100)

EE Admin

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now