DEVFYI - Developer Resource - FYI

How can I overwrite blob data in an Oracle database? ...

JDBC Interview Questions and Answers

(Continued from previous question...)

How can I overwrite blob data in an Oracle database? ...

How can I overwrite blob data in an Oracle database? I want to replace the blob in the selected row with byte[] blobData using the following code:
OracleResultSet rset=null;
OutputStream os=null;
CallableStatement stmt=null;

conn.setAutoCommit(false);
stmt = conn.prepareCall("select myblob from mytable " +
"where mycolumn='foobar' for update");
stmt.execute();
rset=(OracleResultSet)stmt.getResultSet();
rset.next();
BLOB bdata=rset.getBLOB("myblob");
os=bdata.getBinaryOutputStream();
os.write(blobData);
os.flush();
os.close();
conn.commit();

However, this only replaces the first blobData.length bytes of the blob and leaves the rest. How can I replace the entire blob with the byte[]?


This behavior is exactly how the function is designed to work; to accomplish a complete replacement of the orginal data you could set the blob column to the value empty_blob() prior to inputting your new data which would do the trick, or you must use the length written to determine the point to trim out the old blob data. The first is probably the best approach and would look something like the following:

OracleResultSet rset=null;
OutputStream os=null;
CallableStatement stmt=null;
Statement stmt1 = null;

// do connection stuff and set up statements
conn.setAutoCommit(false);
stmt1.execute("update mytable set myblob = empty_blob() " +
"where mycolumn='foobar'");
stmt = conn.prepareCall("select myblob from mytable " +
"where mycolumn='foobar' for update");
stmt.execute();
rset=(OracleResultSet)stmt.getResultSet();
rset.next();
BLOB bdata=rset.getBLOB("myblob");
os=bdata.getBinaryOutputStream();
os.write(blobData);
os.flush();
os.close();
conn.commit();

Note that BLOB and CLOB columns in an Oracle database can be modified at any offset from the beginning of the data. Special care must be exercised if the intent is to replace the entire existing data body.

(Continued on next question...)

Other Interview Questions