Oracle Built-in Packages

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:

BFILE

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

BLOB

Large objects consisting of unstructured binary data.

CLOB

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

NCLOB

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,

DECLARE
   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 ...