Oracle – How to Update BLOB Columns in Oracle 11g R2

bloboracleoracle-11g-r2

I have table blobtable which has a BLOB column blobcol.
A user updated the table using a command similar to this:

UPDATE BLOBTABLE
SET BLOBCOL=BFILENAME('directory/subdirectory', 'filename.txt')
WHERE ROWID='ROWIDVALUE';

Note that the file filename.txt existed in the directory before this update was performed.

Now, the user is saying that when they select from blobtable (using PL/SQL developer), blobcol contains "Value Error" for this row. Normally, blobcol contains the value "BFILE". Out of curiosity, I tried selecting the same record in SQL*Plus and I get the following value for blobcol:

bfilename(directory/subdirectory', 'filename.txt')

I'm not very familiar with BLOBs and how they work, so my questions are:

  1. Did the user update the blobcol properly?
  2. What could cause the "Value Error" and how can this be corrected?

Best Answer

That is not how you load a BLOB field. BFILENAME returns a BFILE locator, and BFILE data is not stored in the database, but in files outside of but accessible to the database.

The above command would not even succeed if BLOBCOL is really of BLOB type, you would get the below error:

ORA-00932: inconsistent datatypes: expected BLOB got FILE

Another mistake is that BFILENAME expects the name of a directory database object, not a directory path on the filesystem.

If you want to update a BLOB field with the contents of a file, this is how you do that:

CREATE DIRECTORY MY_DIR AS '/directory/subdirectory';

DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'filename.txt');
  dest_blob BLOB;
BEGIN
  SELECT BLOBCOL into dest_blob FROM BLOBTABLE WHERE ROWID = 'ROWIDVALUE';

  DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.CLOSE(src_lob);

  COMMIT;
END;
/

As to what caused the Value Error message, we do not know your table structure nor the exact method how it was loaded, but it was done with an inappropriate method, I think.

Finally, if your file really is a plain text file (just guessing because of .txt), then you could store that in a CLOB instead of BLOB.

Related Question