Oracle PL/SQL Programming, Third Edition

The LOB Datatypes

Oracle and PL/SQL support several variations of LOB (large object) datatypes. LOBs can store large amounts—up to four gigabytes—of binary data (such as images) or character text data.

Within PL/SQL you can declare LOB variables of the following datatypes:

BFILE

Binary file. Declares a variable that holds a file locator pointing to an operating-system file outside the database. Oracle treats the data in the file as binary data.

BLOB

Binary large object. Declares a variable that holds a LOB locator pointing to a large binary object stored inside the database.

CLOB

Character large object. Declares a variable that holds a LOB locator pointing to a large block of single-byte, fixed-width character data stored inside the database.

NCLOB

National Language Support (NLS) character large object. Declares a variable that holds a LOB locator pointing to a large block of single-byte, fixed-width multibyte, or variable-width multibyte character data stored inside the database.

There are two types of LOBs: internal and external. Internal LOBs (BLOBs, CLOBs, and NCLOBs) are stored in the database and can participate in a transaction in the database server. External LOBs (BFILEs) represent binary data stored in operating-system files outside the database tablespaces. External LOBs cannot participate in transactions; in other words, you cannot commit or roll back changes to a BFILE. Instead, you must rely on the underlying filesystem for data integrity.

The BFILE Datatype ...