title: How to write / update Oracle blob in a reliable way?
body: <p>I'm trying to write and update a pdf document in a blob column but I'm just able to update the blob only writing more data than the previous stored data.
  If I try to update the blob column with a smaller document data I get only a corrupted pdf.</p>
  
  <p>First the blob column has been initialized using empty_blob() function. I wrote the sample Java class below to test this behaviour. I run it the first time with 'true' as first parameter of the <em>main</em> method so in the first row there's stored a document of about 31kB and in the second row there's a document of 278kB.
  Then I run it with 'false' as parameter, in this way the two rows should be updated swapping the documents. The result is that I get a correct result only when I write more data than the existing one.</p>
  
  <p>How is it possible to write a method that writes and updates a blob in a reliable way without worring about binary data's size?</p>
  
  <pre><code>import static org.apache.commons.io.IOUtils.copy;
  
  import java.io.FileInputStream;
  import java.io.OutputStream;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  
  import oracle.jdbc.OracleDriver;
  import oracle.jdbc.OracleResultSet;
  import oracle.sql.BLOB;
  
  import org.apache.commons.lang.ArrayUtils;
  /**
   * Prerequisites:
   * 1) a table named 'x' must exists [create table x (i number, j blob);] 
   * 2) that table should have two columns [insert into x (i, j) values (1, empty_blob()); insert into x (i, j) values (2, empty_blob()); commit;]
   * 3) download lsp.pdf from http://www.objectmentor.com/resources/articles/lsp.pdf
   * 4) download dotguide.pdf from http://www.graphviz.org/Documentation/dotguide.pdf
   */
  public class UpdateBlob {
      public static void main(String[] args) throws Exception {
          processFiles(new String[]{"lsp.pdf", "dotguide.pdf"}, Boolean.valueOf(args[0]));
      }
  
      public static void processFiles(String [] fileNames, boolean forward) throws Exception {
        if(!forward){
          ArrayUtils.reverse(a);
        }
        int idx = 1;
        for(String fname : fileNames){
          insert(idx++, fname);
        }
    }
  
      private static void insert(int idx, String fname) throws Exception{
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
              DriverManager.registerDriver(new OracleDriver());
              conn = DriverManager.getConnection("jdbc:oracle:thin:@"+db+":"+port+":"+sid, user, pwd);
              ps = conn.prepareStatement("select j from x where i = ? for update");
              ps.setLong(1, idx);
  
              rs = ps.executeQuery();
  
              if (rs.next()) {
                  FileInputStream instream = new FileInputStream(fname);
                  BLOB blob = ((OracleResultSet)rs).getBLOB(1);
                  OutputStream outstream = blob.setBinaryStream(1L);
                  copy(instream, outstream);
                  instream.close();
                  outstream.close();
              }
              rs.close();
              ps.close();
              conn.close();
          } catch (SQLException e) {
              e.printStackTrace();
              throw new Exception(e);
          }
      }
  }
  </code></pre>
  
  <p>Oracle version: 11.1.0.7.0 - 64bit</p>
  
  <p>I even tried the standard JDBC API without using Oracle's specific one (like in the example above) without any success.</p>