Choosing a Technique for Inserting LOB Data

A document application may receive a short text value, a streamed upload, a generated report, or a file already accessible to the database server. Each source suggests a different way to populate a large object column. The task is to choose a method that matches the source, the destination type, and the point at which the application is ready to store the content.

In Oracle AI Database 26ai, SQL, PL/SQL, application drivers, and loading utilities provide several approaches. This lesson explains how to choose among them and how to distinguish missing, initialized, and populated LOB values. INSERT creates a row; UPDATE changes an existing row. Neither statement automatically knows where an external document is located or how it should be interpreted.

Start with the source and intended result

Ask where the content is available: inside a PL/SQL variable, in a client application's stream, or in a server-side file. Next decide whether the database should retain the bytes or only reference an external file. Finally, decide whether the content can be stored as it is produced or must be assembled before insertion.

These choices are not ranked by a universal performance order. A method that suits an interactive upload may be inconvenient for a nightly file feed. Choose a clear data path first, then measure its behavior with representative content and the actual driver and database configuration.

A consistent practice table

The examples below use a separate relational practice table rather than assuming that a table from another lesson has a particular column order. The CLOB holds text, the BLOB holds binary content, and the BFILE holds an external reference. An application would include only the representations its requirements need.

CREATE TABLE lesson_lob_documents (
    document_id NUMBER PRIMARY KEY,
    title       VARCHAR2(200) NOT NULL,
    created_on  DATE DEFAULT SYSDATE,
    text_body   CLOB,
    binary_body BLOB,
    source_file BFILE
);

Create this table only in a practice schema where the name is available and the required privileges exist. Oracle DDL has implicit commit behavior, so perform setup separately from unrelated uncommitted work. The following DML examples use distinct identifiers, but repeating a retained example will conflict with its primary key.

The statements are illustrative and documentation-reviewed; they have not been executed against an Oracle instance for this lesson. DML examples leave the final transaction decision to the caller. Commit completed practice changes deliberately, or roll them back when they are no longer needed.

Distinguish NULL, empty, and populated

INSERT INTO lesson_lob_documents
    (document_id, title, created_on, binary_body)
VALUES
    (601, 'Photo not supplied', DATE '2026-10-21', NULL);

INSERT INTO lesson_lob_documents
    (document_id, title, created_on, binary_body)
VALUES
    (602, 'Photo awaiting content', DATE '2026-10-21',
     EMPTY_BLOB());

Give these states a business meaning. A missing photograph and a deliberately empty attachment should not become indistinguishable merely because both display no image. Likewise, an upload workflow should not mark a document complete solely because its row was initialized successfully.

Insert a complete value directly

When the complete value is already available and fits the chosen scalar representation, an INSERT can supply it directly. The following example uses a short text literal converted to CLOB. No piecewise construction is needed because the entire demonstration value is present in the statement.

INSERT INTO lesson_lob_documents
    (document_id, title, text_body)
VALUES
    (603, 'Short report', TO_CLOB('The inspection is complete.'));

In application code, use suitable bind parameters for variable input rather than assembling SQL by concatenating document text. Binding keeps the statement structure separate from its values. It also avoids treating quotation marks or other content as part of the SQL statement itself.

Do not turn this small example into a claim that every LOB insert has a 4,000-byte or 32,767-byte ceiling. SQL literals, SQL scalar types, PL/SQL variables, and application LOB binds have different rules. PL/SQL VARCHAR2 and RAW buffers have their own size limits; those buffers are not the same thing as the total LOB value.

Character encoding matters when converting between characters and byte-limited buffers. A document that fits a character count may require more bytes in a particular encoding. Check the actual operation and interface rather than choosing a technique from a single approximate “32 KB” rule.

Use a client stream when the client owns the source

If a Java application receives an upload, the source may be a stream in that application rather than a file visible to the database server. Oracle JDBC supports methods such as setCharacterStream and setBinaryStream for appropriate destinations. The driver can select among binding modes according to the method, overload, and input.

This means a large client value does not universally require the application to issue a separate EMPTY_CLOB() INSERT and manage a locator itself. Consult the deployed driver's supported APIs and length conventions. Oracle's JDBC LOB documentation describes the data and locator interfaces.

For example, a web service receiving a PDF should identify how its upload stream reaches a BLOB parameter. Supplying the user's local filename to BFILENAME would describe the wrong data path: the database server does not acquire access to the user's computer because that filename appears in SQL.

Define how the application detects an incomplete transfer and when it considers the document stored. A successful connection or accepted request is not the same as a completed insert. The transaction and error response should reflect whether the required content and metadata were stored together.

Build a persistent LOB through RETURNING

When PL/SQL produces content in pieces and can create the destination row first, initialize the LOB and retrieve its locator with RETURNING. The INSERT locks its new row. Subsequent LOB operations can use that locator while the modifying transaction remains appropriate.

DECLARE
    l_text  CLOB;
    l_chunk VARCHAR2(100);
BEGIN
    INSERT INTO lesson_lob_documents
        (document_id, title, text_body)
    VALUES
        (604, 'Generated report', EMPTY_CLOB())
    RETURNING text_body INTO l_text;

    FOR i IN 1..3 LOOP
        l_chunk := 'Report part ' || TO_CHAR(i) || CHR(10);
        DBMS_LOB.WRITEAPPEND(l_text, LENGTH(l_chunk), l_chunk);
    END LOOP;
END;
/

The example uses short ASCII chunks. For the CLOB operation, the amount is expressed in characters; a binary buffer uses byte-oriented handling. Consult the routine's detailed character and buffer rules before extending this pattern to arbitrary multilingual chunks. The slash is a script execution delimiter for clients such as SQL*Plus, not part of a PL/SQL block sent through every API.

The important sequence is initialization, locator retrieval, and population. A chunk's buffer size does not set the total size of the completed report. Conversely, repeated writes do not make the destination unlimited. Storage capacity and API restrictions still apply.

Do not commit between initialization and these writes and then assume the old transaction-associated locator remains writable. If an operation fails, the caller must handle the failure before committing incomplete work. This teaching block does not hide errors or add a rollback that might affect unrelated caller changes. See the locator transaction rules for the distinction between read and write validity.

Assemble first with a temporary LOB

Sometimes a helper must produce the complete document before the caller decides whether to insert it. A temporary LOB can support that intermediate result. For example, a reporting service may assemble several sections, check that required sections are present, and only then store an approved output.

DBMS_LOB.CREATETEMPORARY creates a temporary LOB; DBMS_LOB.FREETEMPORARY releases it. Explicitly created temporary resources need cleanup on successful and exceptional paths. If a helper returns a temporary LOB, define who owns that cleanup so that the helper does not free content before its caller uses it.

Caching and duration should match the workload and ownership model. There is no universal rule that cache set to TRUE is always fastest or eliminates disk activity. Rolling back persistent data changes is also not a replacement for releasing temporary resources. The DBMS_LOB reference explains these controls.

Choose temporary assembly because the workflow needs an intermediate document, not because every large input requires one. If a complete value already arrives through a supported client stream, an additional application-managed assembly stage may add complexity without meeting another requirement.

Reference a server file without loading it

BFILENAME identifies an external file using a directory object and a filename. The following example assumes that LOB_DIR is an appropriate database directory object. It stores the reference in source_file, which the practice schema declares as BFILE.

INSERT INTO lesson_lob_documents
    (document_id, title, source_file)
VALUES
    (605, 'External photograph',
     BFILENAME('LOB_DIR', 'customer_25.jpg'));

The statement does not copy the photograph into binary_body. The external file must exist and be readable when accessed, with suitable database privileges and operating-system permissions. Merely storing a reference does not verify those conditions. A directory object also does not create the corresponding operating-system directory.

Use this approach when external ownership is intentional. The file's availability and recovery remain separate from the database row. A database rollback does not undo another program's changes to those external bytes, and deleting the reference does not delete the file.

Load server-file contents into an internal LOB

If the database must retain its own copy, perform a loading or conversion operation. With the same server-file prerequisites, TO_BLOB can convert a BFILE value into a BLOB value for insertion. Here the destination is binary_body rather than source_file.

INSERT INTO lesson_lob_documents
    (document_id, title, binary_body)
VALUES
    (606, 'Stored photograph',
     TO_BLOB(BFILENAME('LOB_DIR', 'customer_25.jpg')));

This operation requires access to the source content. Oracle's TO_BLOB reference describes the conversion. DBMS_LOB.LOADBLOBFROMFILE is another option when the workflow requires explicit procedural loading.

For text files, the source encoding must be interpreted correctly. TO_CLOB(bfile, csid) accepts a source character-set identifier; DBMS_LOB.LOADCLOBFROMFILE provides procedural control including conversion warnings. Do not assume every text file is UTF-8. See the text conversion reference.

A file-loading block that explicitly opens resources must close them on normal and error paths and handle conversion warnings appropriately. A generic client path or cloud URL is not automatically a BFILE source. Verify the supported storage arrangement instead of substituting an inaccessible path into an otherwise valid statement.

Choose a batch-loading tool for batch input

SQL*Loader is appropriate to consider when files contain many input records or when document content is organized into separate LOB files. The loading configuration connects identifying metadata with the correct content. The next lesson develops that arrangement in detail.

For a nightly photograph feed, establish how each filename corresponds to a customer identifier and how rejected records will be reviewed. Loading many bytes successfully is not sufficient if a photograph is attached to the wrong customer. Validation should check those relationships as well as whether the load completed.

External tables can support suitable file-reading and SQL transformation workflows. Data Pump serves database export and import needs; it is not a general replacement for SQL*Loader when the source consists of arbitrary photographs or text files. Select the tool for the source format and operational purpose.

Apply the choice to a document workflow

Consider three documents arriving at the same application. A clerk enters a short note, a browser uploads a scanned agreement, and a scheduled process receives a server-side image file. Although all three eventually relate to a business record, there is no reason to force them through the same source interface.

The note can use a suitable bound character value. The upload can use the application's supported binary stream handling. The server-side image can use a file-loading operation when the database needs its own copy, or a BFILE reference when the external system remains responsible for retention. The destination requirement decides whether the last operation copies content or only records its location.

Now suppose the application also generates a combined report. If the caller can create the report row immediately, persistent piecewise construction may fit. If the complete report must be assembled and reviewed before insertion, temporary assembly may be appropriate. These are workflow choices rather than automatic consequences of exceeding a fixed document size.

Write down the source, destination, completion condition, and error owner for each path. That small description helps a maintainer understand why a technique was selected and which assumptions need rechecking when the application changes.

Verify the intended result

After an insertion, distinguish row existence from content completeness. Verify the expected identifier, content state, and relevant length or application checks. A returned row count establishes that a row was affected, but an intentionally empty LOB still contains no uploaded document.

Plan retries before processing important data. Repeating an insert with the same primary key can fail, while retrying with a different key can create duplicate business documents. Decide how the application recognizes an already completed request and how it handles an incomplete attempt.

Keep related metadata and content within the intended transaction. Do not add commits inside reusable helpers merely to make a demonstration appear finished. The caller needs a clear point at which the complete business operation becomes durable, including any related changes beyond this table.

In the next lesson, Inserting LOB Data Using SQL*Loader, you will examine loading from files. Later lessons develop updates to existing LOB content.

Querying Modifying LOBs-Quiz

Review the module material with the Querying Modifying LOBs-Quiz.