Updating LOB Data with DBMS_LOB

A stored document can change in several ways. You might correct three characters, add a note at the end, replace the entire document, or copy a revised version from another row. These operations can produce different results even when they start with the same text. Choosing the right operation means specifying both the required content and its final length.

Oracle AI Database 26ai supports SQL assignment to LOB columns and procedural changes through DBMS_LOB. This lesson uses small CLOB values so that every change is visible. The same discipline applies to larger documents: obtain the correct locator, protect the destination row, use the appropriate units, verify the result, and decide whether to commit.

Choose between assignment and a positional change

Set up two practice documents

Use a dedicated practice schema with permission to create a table. Run the examples in order in one SQL client with autocommit disabled. The table name must be available. If it already exists, inspect it before proceeding; this lesson does not drop an existing table. CREATE TABLE has implicit commit effects, so finish unrelated work before running setup.

CREATE TABLE lesson8_documents (
    doc_id      NUMBER PRIMARY KEY,
    doc_name    VARCHAR2(100),
    doc_content CLOB
);

INSERT INTO lesson8_documents VALUES
    (1, 'Working document', TO_CLOB('ABCDEFGHIJ'));
INSERT INTO lesson8_documents VALUES
    (2, 'Copy destination', TO_CLOB('Destination text is longer'));
COMMIT;

Both rows are supplied because the later COPY example needs an existing destination. Each CLOB is populated and therefore has a persistent locator. These are ordinary relational rows, not object-table instances. The examples do not depend on a customer-photo table created in another lesson.

The blocks and expected values below are documentation-reviewed teaching examples, not results from an executed Oracle session. Run the verification query after each successful block. If the result matches the stated expectation, issue COMMIT before continuing. Otherwise issue ROLLBACK and investigate before running the next step. Never automatically run a commit after a reported error.

SELECT doc_id, DBMS_LOB.GETLENGTH(doc_content) AS content_length,
       '[' || DBMS_LOB.SUBSTR(doc_content, 100, 1) || ']' AS preview
FROM lesson8_documents
WHERE doc_id IN (1, 2)
ORDER BY doc_id;

Initially, row 1 has length 10 and preview [ABCDEFGHIJ]. Row 2 contains the longer destination text. The brackets make leading and trailing spaces visible. The preview is deliberately limited to 100 characters; it is a convenient display for these tiny values, not proof that a large document matches in full.

Acquire a locator and protect the destination

Before changing a persistent LOB through DBMS_LOB, lock its containing row. The examples use SELECT ... FOR UPDATE to retrieve the locator and obtain that lock together. A normal SELECT is appropriate for many reads, but merely reading a locator does not supply the destination lock required for these writes.

The lock belongs to the transaction. Do not commit between selecting the locator and modifying it. After a commit or rollback, obtain a fresh locator and lock for the next modification. Oracle describes this restriction in its LOB transaction-boundary guidance. The examples declare fresh local variables in every block.

Each block sets a savepoint before its work. Its exception handler rolls back that step and re-raises the error, allowing the client to report the failure. This is particularly useful when a write and a trim must succeed together. A successful block deliberately leaves the transaction open for inspection. Application code should let the owning transaction decide when to commit.

Overwrite three characters with WRITE

Start with row 1 still containing ABCDEFGHIJ. The fourth character is D. Writing xyz from offset 4 overwrites D, E, and F while retaining the other characters. This is a positional overwrite, not an insertion that pushes the remaining text to the right.

DECLARE
    l_doc    CLOB;
    l_buffer VARCHAR2(20) := 'xyz';
BEGIN
    SAVEPOINT lesson8_step;
    SELECT doc_content INTO l_doc
    FROM lesson8_documents WHERE doc_id = 1 FOR UPDATE;

    DBMS_LOB.WRITE(l_doc, LENGTH(l_buffer), 4, l_buffer);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson8_step;
        RAISE;
END;
/

Run the verification query above. Row 1 should show [ABCxyzGHIJ] and length 10. Confirm the result, then COMMIT. The amount is derived from the actual buffer, rather than copied from an unrelated example. For this ASCII CLOB example, it is three characters. The offset starts at 1, not 0.

WRITE does not mean “replace everything.” If the existing value is longer than the written range, the unaffected suffix remains. Writes beyond the current end have additional filler behavior documented by Oracle; they are unnecessary here. Keep offsets within the intended range when teaching a precise correction.

Append a scalar buffer with WRITEAPPEND

Continue after committing the previous change. WRITEAPPEND writes a buffer at the current end, so the caller does not calculate the starting position. Here it adds one exclamation mark to the modified document.

DECLARE
    l_doc    CLOB;
    l_buffer VARCHAR2(20) := '!';
BEGIN
    SAVEPOINT lesson8_step;
    SELECT doc_content INTO l_doc
    FROM lesson8_documents WHERE doc_id = 1 FOR UPDATE;

    DBMS_LOB.WRITEAPPEND(l_doc, LENGTH(l_buffer), l_buffer);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson8_step;
        RAISE;
END;
/

Verify [ABCxyzGHIJ!] with length 11, then COMMIT. Repeating this block appends another exclamation mark. It is not idempotent, so a retry after an uncertain client response requires checking the stored value first. A production application may need an operation identifier or another duplicate-prevention mechanism.

WRITEAPPEND accepts a scalar buffer. APPEND accepts another LOB as its source. Choose according to the input you already have; do not convert an entire large document into VARCHAR2 merely to call WRITEAPPEND. The DBMS_LOB reference defines the overloads and argument limits.

See the retained-suffix problem

DECLARE
    l_doc    CLOB;
    l_buffer VARCHAR2(20) := 'New';
BEGIN
    SAVEPOINT lesson8_step;
    SELECT doc_content INTO l_doc
    FROM lesson8_documents WHERE doc_id = 1 FOR UPDATE;
    DBMS_LOB.WRITE(l_doc, LENGTH(l_buffer), 1, l_buffer);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson8_step;
        RAISE;
END;
/

SELECT doc_id, DBMS_LOB.GETLENGTH(doc_content) AS content_length,
       '[' || DBMS_LOB.SUBSTR(doc_content, 100, 1) || ']' AS preview
FROM lesson8_documents
WHERE doc_id IN (1, 2)
ORDER BY doc_id;
ROLLBACK;

DECLARE
    l_doc    CLOB;
    l_buffer VARCHAR2(100) := 'New';
BEGIN
    SAVEPOINT lesson8_step;
    SELECT doc_content INTO l_doc
    FROM lesson8_documents WHERE doc_id = 1 FOR UPDATE;

    IF l_buffer IS NULL THEN
        DBMS_LOB.TRIM(l_doc, 0);
    ELSE
        DBMS_LOB.WRITE(l_doc, LENGTH(l_buffer), 1, l_buffer);
        DBMS_LOB.TRIM(l_doc, LENGTH(l_buffer));
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson8_step;
        RAISE;
END;
/

UPDATE lesson8_documents
SET doc_content = TO_CLOB('New')
WHERE doc_id = 1;

Copy into a longer destination

Row 1 now contains New. Row 2 still contains the longer text inserted during setup. COPY can transfer the source LOB contents directly, without a VARCHAR2 buffer. For exact replacement, copy the source and trim the destination to the source length. The branch for an empty source avoids a zero-amount COPY.

DECLARE
    l_source CLOB;
    l_dest   CLOB;
    l_length INTEGER;
BEGIN
    SAVEPOINT lesson8_step;
    SELECT doc_content INTO l_source
    FROM lesson8_documents WHERE doc_id = 1;
    SELECT doc_content INTO l_dest
    FROM lesson8_documents WHERE doc_id = 2 FOR UPDATE;

    IF l_source IS NULL OR l_dest IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Both LOBs must be initialized');
    END IF;
    l_length := DBMS_LOB.GETLENGTH(l_source);
    IF l_length > 0 THEN
        DBMS_LOB.COPY(l_dest, l_source, l_length, 1, 1);
    END IF;
    DBMS_LOB.TRIM(l_dest, l_length);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson8_step;
        RAISE;
END;
/

SELECT doc_id, DBMS_LOB.GETLENGTH(doc_content) AS content_length,
       '[' || DBMS_LOB.SUBSTR(doc_content, 100, 1) || ']' AS preview
FROM lesson8_documents
WHERE doc_id IN (1, 2)
ORDER BY doc_id;

Both rows should now show [New] and length 3. Verify, then COMMIT. Without the trim, the longer destination could retain text beyond the copied range. Copying every source character is not the same promise as replacing every destination character.

This demonstration assumes no other session edits the practice source during the operation. Production document copying needs an explicit source-version and concurrency policy. Destination locking protects the row being modified; it does not by itself establish which business version of a source document the user intended to copy.

Distinguish erasing from shortening

ERASE changes the contents of a range without shortening the LOB. For a CLOB it writes spaces; for a BLOB it writes zero bytes. The amount parameter is IN OUT, so supply a variable that can receive the actual amount processed. A literal is not suitable for that parameter.

DECLARE
    l_doc    CLOB;
    l_amount INTEGER := 1;
BEGIN
    SAVEPOINT lesson8_step;
    SELECT doc_content INTO l_doc
    FROM lesson8_documents WHERE doc_id = 2 FOR UPDATE;
    DBMS_LOB.ERASE(l_doc, l_amount, 1);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson8_step;
        RAISE;
END;
/

SELECT doc_id, DBMS_LOB.GETLENGTH(doc_content) AS content_length,
       '[' || DBMS_LOB.SUBSTR(doc_content, 100, 1) || ']' AS preview
FROM lesson8_documents
WHERE doc_id IN (1, 2)
ORDER BY doc_id;
ROLLBACK;

Before rollback, row 2 should show [ ew] and length 3. The leading blank replaces N; it has not disappeared. Rollback restores New. This short experiment separates content erasure from trimming or row deletion, which the next lesson explores further.

Handle empty values and larger inputs

A NULL LOB and an initialized empty LOB are different states. GETLENGTH returns NULL for a NULL LOB and zero for an empty one. Before modifying a NULL column through a locator, initialize it in SQL and obtain the stored locator, for example through UPDATE ... RETURNING or a subsequent SELECT FOR UPDATE. Do not treat a standalone EMPTY_CLOB() expression as a writable persistent document.

CLOB and NCLOB calls use character-oriented amounts under the API rules; BLOB calls use byte amounts. The ASCII examples make the positions unambiguous. Real multilingual text requires attention to character sets, supplementary characters, and buffer sizing. A displayed character is not a reliable universal byte-counting unit.

The PL/SQL scalar buffers used by these APIs are bounded by 32,767 bytes. That limit is not the capacity of the LOB itself. A multibyte character buffer can hold fewer characters. For large content, use bounded chunks or suitable LOB-to-LOB operations and validate the final result. For binary writes, use RAW input and byte lengths, such as UTL_RAW.LENGTH, rather than applying text conversion to arbitrary binary content.

Use a checkpoint after each operation

The sequence matters. Setup establishes a known committed starting point. The overwrite and append each become the next starting point only after you verify and commit them. The retained-suffix experiment is rolled back, while the corrected replacement is committed. Copy then uses that committed replacement as its source. Finally, the erasure experiment is rolled back. Keeping this order makes an unexpected value easier to trace to a specific step.

If you choose rollback after an unexpected result, do not continue as though the expected change had been committed. Recheck the current value and repair or repeat the failed step before proceeding. Similarly, a second execution of the setup inserts will encounter existing primary keys. That is a reason to inspect the practice data, not to remove every row automatically.

The slash after each PL/SQL block is a script-execution delimiter used by clients such as SQL*Plus. In another client, use its appropriate block or script execution mode. Execute the verification SELECT separately, and keep the explicit COMMIT or ROLLBACK decision visible. A client setting that commits automatically would defeat the rollback experiments.

Keep transaction ownership explicit

These persistent-LOB examples intentionally omit explicit OPEN and CLOSE calls. Those calls are optional for the basic operations shown and do not replace row locks. If you introduce OPEN to group operations, follow the documented CLOSE-before-commit requirements and provide exception cleanup. Do not add resource management merely as unexplained boilerplate.

After a failure, read the actual error and verify the transaction state. Missing rows, NULL locators, incorrect argument sizes, and stale locators require different corrections. Repeating a write blindly can make matters worse, especially for appends. The savepoint protects one teaching step, while the application remains responsible for its wider transaction.

Before committing a real document update, verify the row identifier, expected source version, complete intended content, and length. A short preview is helpful for this lesson but insufficient for a large production value. Appropriate full-content comparison or application validation should reflect the document format and business requirements.

You have now practiced an overwrite, an append, exact replacement, a copy, and an erasure with observable results. Continue to Deleting LOB Data to distinguish further between changing a value, emptying it, and removing its containing row.