Problem solve Get help with specific problems with your technologies, process and projects.

Updating a BLOB column without BFILE/create directory options

Howcan you update a BLOB column within a table and not use the bfile/create directory options?

In order to answer this question, I think it might be best to clarify some terminology. Oracle introduced Large Objects (LOBs) back in Oracle 8. The collection of LOB datatypes are meant to replace the LONG and LONG RAW datatypes available in earlier Oracle versions.

For storing large character data, Oracle has the Character LOB, or CLOB. This replaces the LONG datatype. For storing binary large objects directly in the database, use the Binary LOB datatype, or BLOB. This replaces the LONG RAW datatype.

Additionally, one can reference binary objects from the table, but the object does not even exist in the database. This is done with the BFILE datatype. The BFILE datatype is nothing more than a pointer to a Binary File located on your database server's filesystem.

Hopefully, you can see that there is a difference between a "BLOB" and a "BFILE". A BLOB is stored internally, and a BFILE is stored externally. A BLOB gets the benefits of any internal datatype. A BFILE must be managed by the OS. The OS is responsible for the security of the object, backing up the object, and handling the object. The database does all of this for the BLOB.

Since a BFILE is stored externally, the database can only read the binary object. It cannot write to the BFILE. The only way to modify the BFILE object is to use external programs, utilities, or OS commands to modify the contents of the file itself. For many applications, the easiest way to accomplish this is to just erase the file and put a new file in it's place. The database does not need to be updated since it just has a pointer to the BFILE.

Oracle has supplied a PL/SQL package, DBMS_LOB, to manipulate CLOBs and BLOBs right from your PL/SQL routines, i.e. packages, procedures, triggers, functions. If you do not like using the DBMS_LOB package for your LOB manipulation, then many application interfaces support LOB manipulation. For instance, if you are coding in Java, then the JDBC libraries contain functions for LOB manipulation. Other application programming interfaces have similar routines and you may want to check them out!

For More Information

Dig Deeper on Oracle database design and architecture