1. SecureFiles and Large Objects Developer's Guide
  2. Introduction to Large Objects and SecureFiles
  3. LOB Classifications

1.4 LOB Classifications

LOBs store a variety of data such as audio, video, documents, and so on. Based on the type of data stored in the LOB or memory management mechanism used, there are different classifications.

1.4.1 Large Object Data Types

Oracle Database provides a set of large object data types as SQL data types, where the term LOB generally refers to the set.

In general, the descriptions given for the data types in this table and related sections, also apply to the corresponding data types provided for other programmatic environments.

The following table describes each large object data type that the database supports and describes the kind of data that uses it.

Table 1-1 Types of Large Object Data

SQL Data Type Description

BLOB

Binary Large Object

Stores any kinds of data in binary format. Used for images, audio, and video.

CLOB

Character Large Object

Stores string data in the database character set format. Used for large strings or documents that use the database character set exclusively. Characters in the database character set are in a fixed width format.

NCLOB

National Character Set Large Object

Stores string data in National Character Set format, typically large strings or documents. Supports characters of varying width format.

BFILE

External Binary File

A binary file stored outside of the database in the host operating system file system, but accessible from database tables. BFILEs can be accessed from your application on a read-only basis. Use BFILEs to store static data, such as image data, that is not manipulated in applications.

Any kind of data, that is, any operating system file, can be stored in a BFILE. For example, you can store character data in a BFILE and then load the BFILE data into a CLOB, specifying the character set upon loading.

1.4.2 Types of LOBs

This section describes the three types of LOB data that Oracle supports.

Persistent LOBs

A persistent LOB is a LOB instance that exists in a table row in the database. Persistent LOBs participate in database transactions. You can recover persistent LOBs in the event of transaction or media failure, and any changes to a persistent LOB value can be committed or rolled back. In other words, all the Atomicity, Consistency, Isolation, and Durability (ACID) properties that apply to database objects apply to persistent LOBs. Persistent LOBs can be of data types BLOB, CLOB and NCLOB.

Temporary LOBs

A temporary LOB instance is created when you instantiate a LOB only within the scope of your local application. Temporary LOBs are transient, just like other local variables in an application. A temporary LOB becomes persistent when you insert it into a table row. Temporary LOBs can be of data types BLOB, CLOB and NCLOB.

BFILEs

BFILEs are data objects stored in operating system files, outside the database tablespaces. Data stored in a table column of type BFILE is physically located in an operating system file, not in the database.

BFILEs are read-only data types. The database allows read-only byte stream access to data stored in BFILEs. You cannot write to or update a BFILE from within your application.

You typically use BFILEs to hold:

  • Binary data that does not change while your application is running, such as graphics

  • Data that is loaded into other large object types, such as a BLOB or CLOB, where the data can then be manipulated

  • Data that is appropriate for byte-stream access, such as multimedia

Any storage device accessed by your operating system can hold BFILE data, including hard disk drives, CD-ROMs, PhotoCDs, and DVDs. The database can access BFILEs provided the operating system supports stream-mode access to the operating system files.

Note:

All the information related to BFILEs is exclusively documented either in BFILEs or in Managing LOBs: Database Administration.

1.4.3 LOBs in Object Data Types

Typically, there is no difference in the use of a LOB instance in a LOB column or in an object data type, as its member.

In this guide, the term LOB attribute refers to a LOB instance that is a member of an object data type. Unless otherwise specified, discussions that apply to LOB columns also apply to LOB attributes.

1.4.4 Oracle Data Types Stored in LOBs

Many data types provided with Oracle Database are stored as or created with LOB types.

The following list mentions a few data types that you can store with LOB types:

  • VARCHAR2 or RAW data types of size greater than 4000 bytes
  • JSON data type
  • XMLType stored as BINARY XML or CLOB
  • VARRAY stored as LOB