Types of Oracle LOBs

An external LOB, also called a BFILE, is contained within the file system but outside the database. An internal LOB resides within the database tablespace^[1] and provides efficient access along with optimizing space. It has the same benefits as other object relational database management system (ORDBMS) objects, such as security, manageability, backup and recovery, and transactional control. You can perform parallel queries, but not parallel Data Manipulation Language (DML) or Data Definition Language (DDL) queries, on the LOB columns. There are three types of internal LOBs:

1. binary large objects (BLOBs)^[2],
2. character large objects,^[3] and
3. national character large objects or NCLOBs[4].

LOB data types work by storing a LOB locator with the table data, which is actually a pointer to the LOB data. Let us look at the details of each internal and external LOB data type, beginning with the internal LOBs.

A BLOB is unstructured binary data within the database. A BLOB can store up to 4 GB of binary data. BLOBs participate fully within transactions. You can commit or roll back the changes made to a BLOB value by the DBMS_LOB package, PL/SQL, or the OCI. However, a BLOB locator cannot span transactions or sessions.

A CLOB data type stores single-byte character set data that corresponds to the database character set defined for the Oracle8 database. A CLOB is similar to a BLOB, except in the type of data storage. A CLOB stores character data, whereas a BLOB stores binary data.

A NCLOB stores fixed- and varying-width multibyte national character set data (NCHAR data) that corresponds to the national character set defined for the Oracle8 database. A NCLOB is similar to a BLOB, except in the type of data storage. A NCLOB is based on the national character set, whereas a BLOB is binary data. You cannot create an object type with the NCLOB attributes; however, you can specify NCLOB parameters within a method for an object type.

External LOBs are stored outside the database, and consist of one form of data type. A BFILE data type stores unstructured binary data on CD-ROM or PhotoCD or directly within the file system outside the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. A BFILE can store up to 4 GB of data. A BFILE is used under one of the following two conditions:

1. To promote the inclusion of legacy data that is already stored as a set of documents within the file system. A BFILE can quickly make these files accessible to an Oracle database.
2. When there are applications that cannot connect to the database but that need to access the data.

BFILEs are read-only; you cannot modify them. They support only random (not sequential) reads, and they do not participate within transactions. The underlying operating system must maintain the file integrity and durability for BFILEs. The database administrator must ensure that the file exists and that the Oracle processes have operating system read permissions on the file.
In the next lesson, you will learn how to create a table with a LOB column.

[1]Tablespace: A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all the objects of an application to simplify some administrative operations.

[2]BLOB: Binary Large Objects, a LOB whose value is composed of unstructured binary

[3]CLOB: Character Large Object, a LOB whose value is composed of character data that corresponds to the database character set defined for the Oracle8 database.

[4]NCLOB: National Character Large Object, a LOB whose value is composed of character data that corresponds to the national character set defined for the Oracle8 database.