Types of Oracle LOBs

BLOB, CLOB, and NCLOB are internal LOB types. When their values are stored persistently in table rows, the database manages the content. BFILE provides a reference to an external operating-system file whose contents remain outside database tablespaces. A tablespace is a logical database storage unit; an external directory is a different kind of location.

The distinction concerns the content, not merely the presence of a column. A BFILE column is part of a database table, but the referenced file's bytes are not thereby copied into that table. Conversely, an internal LOB can have its content stored outside the table row while still remaining within database storage.

Use this table as a starting point. The following sections explain the questions behind each choice, including what the datatype does not decide for the application.

A binary large object, or BLOB, represents bytes without interpreting them as characters. Typical examples include a photograph, an audio recording, and a PDF. Although a PDF may display readable text, its file representation contains more than a plain character string. Retaining that representation is a binary-storage requirement.

Suppose a contract application must preserve the exact file supplied by a customer. A BLOB can hold that file while ordinary columns hold its identifier, title, media type, and upload date. Those descriptive values help the application find and present the document without opening its contents. The document format remains the responsibility of the software that interprets the bytes.

Binary storage does not automatically recognize signatures, extract paragraphs, or establish that a file is valid. If the application also needs extracted text, that is another representation with its own processing steps. Storing the original and the extracted text separately can make their different purposes clear. The extracted version should not silently replace the original when preserving the upload matters.

A BLOB can contain text encoded as bytes, but the application must know how to interpret that encoding. Use this possibility deliberately. It does not make a BLOB interchangeable with a character LOB, whose operations have character semantics. Ask whether the requirement is to preserve a file representation or to work with the text itself.

A character large object, or CLOB, represents text using the database character set. It is appropriate for substantial textual content such as report bodies, descriptions, and transcribed documents. Unlike the legacy description of CLOBs as single-byte-only data, a CLOB can represent multilingual text when the database character set supports the required characters.

Consider an application in which editors revise the body of a technical report. The report body is character data; its title and identifier are separate metadata. A CLOB can represent the body while the application applies its editing rules. Choosing CLOB does not by itself supply revision history, document approval, or collaborative editing. Those features still require an application design.

The database character set is therefore part of the choice. Determine which characters the application must preserve and whether the database and client configuration support them. Do not infer character support from the fact that a document looks correct in one editor. The complete path from input through storage to retrieval must represent the intended text.

Keep small descriptive values in suitable ordinary columns when their requirements fit those types. A document title does not need a CLOB simply because the document body uses one. Separating title from body also gives the application a clear basis for listing and identifying documents without treating their entire contents as labels.

A national character large object, or NCLOB, represents text using the national character set. The key comparison with CLOB is the character-set relationship, not whether the text is important, international, or particularly large. Evaluate the actual database and application requirements before selecting between the two.

For example, a system may have an established requirement to process certain text through national-character types. NCLOB can fit that requirement for large text. In another system, the database character set may already support all required languages, and a CLOB may meet the same document-content need. The label “multilingual” alone does not determine the answer.

This course also examines object-relational structures, so one restriction matters: an NCLOB can be a table column, but it cannot be an attribute of an Oracle object type. Oracle documents support for NCLOB parameters in object methods separately. A method parameter does not become a stored attribute merely because the method belongs to an object type.

Check that distinction before designing a document object. If the object needs a text attribute, verify which supported type and character-set arrangement meet the requirement. If NCLOB storage is necessary, consider an appropriate relational design instead of assuming that every table-column type can be used unchanged as an object attribute. The next lesson's table examples should be read with that boundary in mind.

A BFILE gives Oracle read-only byte-stream access to a file outside database tablespaces. The column stores a locator identifying the external file through a directory object and a filename. The content remains in the server-side file system. This is not a reference to an arbitrary document on the computer of someone viewing a web application.

The referenced file must be available when the application reads it. Database privileges and operating-system access both matter. Creating or storing a locator does not prove that the file currently exists, that the server can read it, or that another process will keep it at the same location.

BFILE access is read-only through Oracle's BFILE interfaces. An external application may still change the operating-system file. Updating a locator to identify another file changes the database reference, not the bytes of either file. Likewise, deleting a row containing a BFILE reference does not delete the external file.

Byte-stream access supports processing a file in portions. The presence of offset-based reads does not mean that an application cannot read successive portions in order. Avoid the old claim that BFILE supports random access but makes sequential reading impossible. What matters here is that the content is read externally rather than modified through the BFILE interface.

Imagine that a separate publishing system maintains source files in an agreed server directory. A database application needs to associate those files with catalog records and read them as input to a processing task. BFILE may fit when the publishing system continues to own the files and the database does not need to modify their bytes.

Now change the requirement: the database application must retain its own authoritative copy even if the publishing system replaces or removes its source. A reference alone does not satisfy that requirement. Loading the content into an internal LOB changes the ownership arrangement by creating database-managed content. The choice follows the retention requirement rather than the filename extension.

For external files, agree on naming, availability, replacement, and recovery procedures. A database backup does not automatically back up the referenced bytes. Restoring a row with its locator will not reconstruct a missing file. The team responsible for the file system must coordinate recovery with the team responsible for the database metadata.

These responsibilities are relevant even when the files rarely change. A stable document can become unavailable because a directory is renamed, permissions change, or storage is moved. Treat the database reference and the external content as related resources with separate lifecycles.