LOB Concepts

This section describes some basic LOB concepts you’ll need to understand when you work with large objects.

LOB Datatypes

Oracle8 provides four LOB datatypes:


Large binary objects stored in operating system files outside of the database; for example, a bitmap image file.


Large objects consisting of unstructured binary data.


Large objects consisting of single-byte fixed-width character data.


Large binary objects consisting of single-byte or multiple-byte fixed-width character data.

Internal and external LOBs

There are two categories of LOBs, depending upon their location with respect to the physical database:

  • Internal LOBs (of datatypes BLOB, CLOB, and NCLOB) are stored in the database and can participate in transactions. Changes to internal LOB values can be rolled back or committed. A cursor can select an internal LOB FOR UPDATE. Uncommitted changes to an internal LOB are not seen by a separate session.

  • External LOBs (of datatype BFILE) are stored outside of the database in operating system files and cannot participate in transactions. Instead, the underlying operating system provides the data integrity. Access to external LOBs is read-only.

The BFILE datatype

The BFILE datatype is used to store large binary objects (up to four gigabytes) in files outside of the database.

A BFILE could be a PL/SQL variable,

   my_book_file BFILE;

or a column in a table,

CREATE TABLE my_book_files
   ( file_descr VARCHAR2(40),  book_file BFILE  );

or an attribute ...

Get Oracle Built-in Packages now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.