How can I overwrite blob data in an Oracle database?

Bernie Acs

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


stmt1.execute("update mytable set myblob = empty_blob() " + 
               "where mycolumn='foobar'");

stmt = conn.prepareCall("select myblob from mytable " + 
                        "where mycolumn='foobar' for update");
BLOB bdata=rset.getBLOB("myblob");
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.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



About | Sitemap | Contact