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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002