Create a Table with a LOB Column in Oracle 26ai

A customer record may contain a numeric identifier, a creation date, and a photograph. The identifier and date fit ordinary scalar columns. The photograph contains binary file data, so a BLOB column is appropriate. Oracle AI Database 26ai lets you declare that column in a relational table or as an attribute of an object type used by an object table.

This lesson develops a table definition for customer photographs, explains its storage clause, and then compares object-table and external-file alternatives. The central distinction is between the column's datatype and the database's storage choices. BLOB describes the value; STORE AS SECUREFILE describes how Oracle manages an internal LOB. Neither clause imports a photograph by itself.

Choose the column type before the storage options

The preceding lesson introduced BLOB, CLOB, NCLOB, and BFILE. For this example, a photograph should retain its original bytes, including its file-format information. A BLOB provides that binary representation. A CLOB is intended for character data, such as a report's text, rather than an arbitrary JPEG or PNG file.

LOB is the name of a family of datatypes, not the datatype you write after a column name. Declare customer_photo BLOB, rather than customer_photo LOB. A table can contain several LOB columns with different purposes. For example, a document-management table might use a BLOB for the original document and a CLOB for extracted text. Those values would remain separate application responsibilities.

Keep searchable business attributes in ordinary columns when they have ordinary scalar meanings. Customer identifiers, document titles, and dates should not be embedded only inside a large binary value. This design allows applications to locate the relevant row using its metadata before requesting the potentially much larger photograph.

Prepare a suitable practice schema

The examples below are illustrative SQL for Oracle 26ai. They have been reviewed against Oracle documentation, but their execution depends on your database configuration. Use a practice schema and object names that are not already in use. Creating the same table twice produces a name conflict; renaming an example is preferable to dropping an unfamiliar existing object.

Your account needs the CREATE TABLE privilege and sufficient quota on the tablespace used for the table and its LOB storage. The object-type example also requires permission to create a type. SecureFiles LOB storage requires a tablespace managed with Automatic Segment Space Management, abbreviated ASSM. The main example assumes that the schema's default tablespace satisfies that requirement.

Check these prerequisites with your database administrator if you do not manage the environment. A privilege or tablespace error does not necessarily mean that the column declaration is wrong. Separate access and configuration problems from SQL syntax problems before changing the statement.

Create a relational table with a BLOB column

Run the following statement in the intended practice schema. It creates an empty relational table named customer_photos and associates the storage clause with its customer_photo column.

CREATE TABLE customer_photos (
    customer_id    NUMBER(10)
        CONSTRAINT customer_photos_pk PRIMARY KEY,
    customer_photo BLOB,
    created_date   DATE DEFAULT SYSDATE
)
LOB (customer_photo) STORE AS SECUREFILE (
    ENABLE STORAGE IN ROW
    NOCACHE
    LOGGING
);

The first pair of parentheses contains column definitions and the primary-key constraint. After that closing parenthesis, the LOB clause identifies the column whose storage is being configured. Its own parentheses contain storage options. The final semicolon terminates the entire CREATE TABLE statement, not a separate command for the LOB.

The primary key also makes an important application assumption visible: this design allows at most one row per customer identifier. If a customer must have several photographs, introduce a separate photograph identifier and model the customer relationship accordingly. That is a table-design decision, independent of whether the image itself uses a BLOB.

The photograph column is nullable in this example. A row can therefore exist before its photograph is supplied. The creation date has a default but no NOT NULL constraint; an explicitly supplied NULL is not replaced by this ordinary default. Add constraints deliberately when the application's rules require stronger guarantees.

Understand locators and physical placement

An internal LOB uses a locator to identify its value. Eligible small values can reside in the table row; values stored outside the row use LOB storage separate from the table's row data. Out-of-row storage does not mean that the value must be in another tablespace. A separate segment and a separate tablespace are different concepts.

Think of a tablespace as a storage placement choice and a segment as storage allocated for a database object. The base table and its LOB segment can share a tablespace. Moving the LOB to a separate tablespace may support administrative requirements, but it is not necessary merely because photographs are larger than identifiers.

ENABLE STORAGE IN ROW allows eligible values to be stored inline. It does not promise that every photograph will fit inside its row. Conversely, disabling inline storage changes placement without changing the declared datatype. Oracle 26ai supports configuration-dependent inline options, so a single historical byte threshold should not be treated as a universal rule for every installation.

Consider the application's access pattern before changing placement. A screen that retrieves identifiers and dates has different needs from a gallery that also retrieves photographs. Start with a straightforward definition, measure the actual workload, and then make storage changes for a specific reason.

When to specify a tablespace

The main statement intentionally omits an explicit tablespace name so it does not depend on a tablespace invented for this lesson. In a managed application, an administrator may provide an existing ASSM tablespace for large-object storage. An optional TABLESPACE clause inside the LOB storage parentheses selects that location for the LOB.

Use the actual approved tablespace name and confirm the schema's quota before adding such a clause. Do not assume that separating the LOB automatically improves performance: the underlying storage, workload, and administration policy determine the benefit. The lesson's essential requirement remains the creation of a valid BLOB column.

Separate buffer caching from logging

The storage clause contains both NOCACHE and LOGGING because they answer different questions. Caching concerns the database buffer cache. Logging concerns recovery information. Neither setting describes parsing the photograph or interpreting its file format.

For example, an archive containing rarely viewed photographs differs from a small collection displayed repeatedly. Caching every large value without considering the rest of the database can displace other useful cached data. Select a policy using measured access patterns, rather than assuming that CACHE always means faster.

For the exact storage-option behavior and supported combinations, see Oracle's Creating a New LOB Column. Explicit options make the example readable, but a long storage clause is not mandatory for every LOB column.

Recognize BasicFiles settings in older examples

Older scripts often show CHUNK, PCTVERSION, and manual extent settings together. These reflect older storage guidance, especially BasicFiles LOBs. They should not be copied into a modern SecureFiles example as if they were required column-definition syntax.

For SecureFiles, retention settings govern management of older LOB versions used for consistent reads; RETENTION AUTO expresses automatic management. Retaining an old version for a query is different from retaining a business document for a legal or archival period. Application retention requirements need their own design.

Create an object table containing a LOB attribute

The relational table above declares its columns directly. An object table instead stores instances of a named object type. First define a type with the required attributes, then use CREATE TABLE ... OF to create a table based on it. The presence of a BLOB alone does not make a table an object table.

CREATE TYPE customer_photo_obj_type AS OBJECT (
    customer_id    NUMBER(10),
    customer_photo BLOB,
    created_date   DATE
);
/

CREATE TABLE customer_photo_obj_table
    OF customer_photo_obj_type (
        CONSTRAINT customer_photo_obj_pk
            PRIMARY KEY (customer_id)
    )
LOB (customer_photo) STORE AS SECUREFILE (
    NOCACHE
    LOGGING
);

This version uses CREATE TYPE for a new practice object. It avoids replacing a type that other schema objects might already use. The slash on its own line executes the buffered type definition in SQL*Plus or SQLcl script mode. It is a client command, not part of the SQL text that an application sends through a database driver.

The type declares three attributes, including a BLOB attribute. The object table supplies the primary-key constraint and the LOB storage settings. Attribute declaration and table storage are separate stages: defining the type alone does not create a table of customer photographs.

These examples use different table names so that you can compare their definitions without redeclaring the same object. Choose between the relational and object-table models according to the application's design. A name ending in OBJ_TABLE has no special effect; the OF clause is what establishes the object-table form.

The BLOB attribute is intentional. Do not substitute every LOB type indiscriminately: the NCLOB object-attribute restriction discussed in the previous lesson still matters. Oracle's CREATE TABLE reference documents table forms, privileges, and the applicable storage clauses.

Use BFILE when the content remains external

A different requirement arises when photographs remain in files managed outside Oracle. A BFILE column stores a reference to a server-side file instead of storing that file's content as an internal BLOB. The following definition illustrates the difference:

CREATE TABLE customer_photo_files (
    customer_id    NUMBER(10)
        CONSTRAINT customer_photo_files_pk PRIMARY KEY,
    customer_photo BFILE,
    created_date   DATE DEFAULT SYSDATE
);

There is no SecureFiles storage clause for customer_photo here because the referenced content is external. Creating this table does not create an operating-system file, copy a photograph, or create a database directory object. Those are separate setup and application operations.

A BFILE locator can be constructed using BFILENAME with a directory-object name and a filename. Reading the content requires the appropriate directory privilege and operating-system access. The filename is relative to the server-side directory represented by that object; it is not an instruction to read a file from the learner's workstation.

Oracle provides read-only access to BFILE content. An application can change the reference stored in a row, but writing new bytes to the external file is not the same operation as updating an internal BLOB. Plan external-file availability and backup separately from the table that holds the reference. See Oracle's BFILE documentation for this storage model.

Check the internal LOB definition

After creating the main table in your environment, inspect its LOB metadata instead of assuming that the submitted script tells the whole story. USER_LOBS describes internal LOBs owned by the current user.

SELECT table_name,
       column_name,
       tablespace_name,
       securefile,
       in_row,
       cache,
       logging
FROM user_lobs
WHERE table_name = 'CUSTOMER_PHOTOS';

Unquoted object names are stored in uppercase, which explains the filter. Check SECUREFILE to confirm the implementation and compare the reported cache and logging settings with your intended definition. IN_ROW describes the inline-storage setting; it is not proof that every value in the column physically resides in its row.

A BFILE column does not appear as an internal LOB in this view. If no row appears for the main example, check the current schema and whether CREATE TABLE succeeded. Oracle's LOB dictionary-view reference explains these fields and the related USER_LOBS view.

Distinguish an empty table from an empty LOB

A successful CREATE TABLE defines a structure with no customer rows. Inserting a row is a separate step, and inserting a locator does not necessarily populate it with photograph bytes. A NULL LOB and an initialized empty LOB are also different states.

INSERT INTO customer_photos (customer_id, customer_photo)
VALUES (1001, EMPTY_BLOB());

This optional practice insert initializes the photograph as an empty BLOB and uses the creation-date default. It does not load an image. The insert participates in the current transaction, so commit it only if you intend to retain the practice row, or roll it back. Reusing identifier 1001 after retaining that row would violate the primary key.

Oracle's EMPTY_BLOB reference distinguishes initialization from population. Actual byte loading requires an appropriate application or LOB operation. That is the next stage after designing and checking the table.

Review the completed design

Before using the definition in an application, confirm three decisions: whether Oracle should own the content, whether the table is relational or object-based, and which storage settings fit the workload. Those decisions lead respectively to the datatype, the table form, and the LOB storage clause.

The main example creates a relational table with a SecureFiles BLOB. The object-table variation declares a BLOB attribute in a named type. The BFILE variation keeps file content outside database tablespaces. In each case, table creation defines the structure; data loading remains a separate operation. In the next lesson, you will examine the components of the DBMS_LOB package.