PL/SQL   «Prev  Next»

Lesson 9Deleting LOB data using PL/SQL
ObjectiveUse DBMS_LOB to erase or shorten internal LOB data in Oracle 26ai, and distinguish these operations from deleting a row.

Deleting and Erasing LOB Data

Deleting LOB data can mean several different things. A document may need a range blanked out, a shorter ending, an empty value, or removal of its entire database row. Those outcomes are not interchangeable. A successful procedure call can still produce the wrong result if the operation does not match the requirement.

In Oracle AI Database 26ai, DBMS_LOB provides operations on internal LOB contents, while SQL controls assignments and row deletion. This lesson uses short CLOB and BLOB values to make the differences visible. It builds on the locator and transaction techniques from the previous lesson and uses independent experiments that you inspect and roll back.

Choose the intended result

Begin by asking whether the row must remain and whether the LOB should still be a non-NULL value. Then decide whether the requirement concerns content, length, or both. The following comparison keeps these questions separate.

Ways to remove or replace LOB content
OperationResult for the selected valueRow remains?
DBMS_LOB.ERASEReplaces a range with fillers; length unchangedYes
DBMS_LOB.TRIMShortens the value to a specified lengthYes
TRIM to zero or assign EMPTY_CLOB()Initialized empty CLOBYes
Assign NULLNo non-NULL LOB value in that columnYes
SQL DELETERemoves the selected row from the tableNo

ERASE is a procedure, not a row-deletion function. For a CLOB, erased characters become spaces. For a BLOB, erased bytes become zero bytes. Neither operation closes the gap by shifting subsequent content left. The procedure signatures and restrictions appear in the DBMS_LOB reference.

Prepare a dedicated practice table

Use a practice account with permission to create a table, a SQL client with autocommit disabled, and no unrelated pending work. The table name below must be available. Inspect an existing table before reusing its name; do not drop it merely to make the demonstration run. CREATE TABLE has implicit commit effects, so setup is separate from the rollback experiments.

CREATE TABLE lesson9_documents (
    doc_id         NUMBER PRIMARY KEY,
    doc_content    CLOB,
    binary_content BLOB
);

INSERT INTO lesson9_documents VALUES
    (1, TO_CLOB('ABCDEFGHIJ'), HEXTORAW('1122334455'));
INSERT INTO lesson9_documents VALUES
    (2, TO_CLOB('ABCDEFGHIJ'), HEXTORAW('1122334455'));
COMMIT;

SET SERVEROUTPUT ON

Both rows begin with the same small values. The binary data is five illustrative bytes, not an image file. Populated LOB values can be inserted directly; EMPTY_CLOB and EMPTY_BLOB are useful when an initialized empty value is wanted, but are not mandatory for every insertion.

SET SERVEROUTPUT ON enables DBMS_OUTPUT display in SQL*Plus-compatible clients. Other clients have their own output controls. The slash following each PL/SQL block is a client script delimiter. Use the execution mode appropriate to your client rather than sending that slash as part of a database API call.

Use one verification query throughout

The expected-ID list lets the query report a missing row. Without it, a deleted row would simply disappear from the result, which is different from a row whose CLOB is NULL. The query also shows short text and hexadecimal previews alongside lengths.

WITH expected (doc_id) AS (
    SELECT 1 FROM dual
    UNION ALL
    SELECT 2 FROM dual
)
SELECT e.doc_id,
       CASE
           WHEN d.doc_id IS NULL THEN 'MISSING ROW'
           WHEN d.doc_content IS NULL THEN 'NULL LOB'
           WHEN DBMS_LOB.GETLENGTH(d.doc_content) = 0 THEN 'EMPTY LOB'
           ELSE 'POPULATED LOB'
       END AS text_state,
       DBMS_LOB.GETLENGTH(d.doc_content) AS text_length,
       '[' || DBMS_LOB.SUBSTR(d.doc_content, 30, 1) || ']' AS text_preview,
       DBMS_LOB.GETLENGTH(d.binary_content) AS binary_bytes,
       RAWTOHEX(DBMS_LOB.SUBSTR(d.binary_content, 30, 1)) AS binary_hex
FROM expected e
LEFT JOIN lesson9_documents d ON d.doc_id = e.doc_id
ORDER BY e.doc_id;

Initially, each row should report POPULATED LOB, text length 10, preview [ABCDEFGHIJ], binary length 5, and hexadecimal 1122334455. These are expected results derived from the supplied inputs. The examples have been reviewed against documentation but have not been executed against an Oracle database for this lesson.

After each experiment, run this query before issuing ROLLBACK. Then run it again to confirm that the committed starting values are restored. Do not commit the experiments if you intend to follow the independent sequence below. The preview brackets alone cannot distinguish NULL from empty; use the state and row-presence checks.

Erase a range of CLOB characters

Select the persistent locator and lock its row before modifying it. The amount argument is IN OUT, so it must be a writable variable. Oracle returns the number of units actually erased through that variable. The offset starts at 1, making offset 4 the position of D in the original sample.

DECLARE
    l_text   CLOB;
    l_amount INTEGER := 3;
BEGIN
    SAVEPOINT lesson9_step;
    SELECT doc_content INTO l_text
    FROM lesson9_documents WHERE doc_id = 1 FOR UPDATE;

    DBMS_LOB.ERASE(l_text, l_amount, 4);
    DBMS_OUTPUT.PUT_LINE('Characters erased: ' || l_amount);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson9_step;
        RAISE;
END;
/

The expected message reports three characters erased. Row 1 should have preview [ABC   GHIJ] and length 10. D, E, and F became spaces; G through J stayed at their original positions. Row 2 should remain unchanged. Run the verification query, then issue the following statement and verify the original state again.

ROLLBACK;

The exception handler rolls back changes made after the block’s savepoint and re-raises the error. It does not hide failure or commit successful work. A successful block leaves its change pending so that you can inspect it. In an application, transaction ownership belongs to the code responsible for the complete business operation.

Observe an erase that reaches the end

Start again from the restored ten-character value. Request five characters beginning at offset 9. Only I and J remain from that position, so the actual amount is two. Reusing the requested amount without inspecting its returned value could lead to an incorrect progress report.

DECLARE
    l_text   CLOB;
    l_amount INTEGER := 5;
BEGIN
    SAVEPOINT lesson9_step;
    SELECT doc_content INTO l_text
    FROM lesson9_documents WHERE doc_id = 1 FOR UPDATE;
    DBMS_LOB.ERASE(l_text, l_amount, 9);
    DBMS_OUTPUT.PUT_LINE('Characters erased: ' || l_amount);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson9_step;
        RAISE;
END;
/

Expect [ABCDEFGH  ], length 10, and an actual amount of two. Verify and ROLLBACK before continuing. If you write a routine to erase an entire value, obtain its length first and skip ERASE when that length is zero. Passing zero as the requested amount is not a valid way to handle an empty value. Treat a NULL locator separately.

Erase BLOB bytes and inspect hexadecimal output

A zero byte is binary data with value 00; it is not a missing byte. Hexadecimal display makes this distinction clear without trying to interpret arbitrary binary data as text. The following block targets the BLOB in row 1 and leaves the CLOB untouched.

DECLARE
    l_binary BLOB;
    l_amount INTEGER := 2;
BEGIN
    SAVEPOINT lesson9_step;
    SELECT binary_content INTO l_binary
    FROM lesson9_documents WHERE doc_id = 1 FOR UPDATE;
    DBMS_LOB.ERASE(l_binary, l_amount, 2);
    DBMS_OUTPUT.PUT_LINE('Bytes erased: ' || l_amount);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson9_step;
        RAISE;
END;
/

After verification, expect binary_hex 1100004455 and binary_bytes 5. The bytes 22 and 33 became 00 and 00. The final bytes did not move. Roll back and check that 1122334455 is restored. Changing bytes inside a real image or document can invalidate its format, even when the database operation itself succeeds.

Shorten a value with TRIM

Use TRIM when the requirement is to retain only a leading portion. Its new length is a target length, not the number of units to remove. Starting from ABCDEFGHIJ, a target length of four keeps ABCD and removes the suffix.

DECLARE
    l_text CLOB;
BEGIN
    SAVEPOINT lesson9_step;
    SELECT doc_content INTO l_text
    FROM lesson9_documents WHERE doc_id = 1 FOR UPDATE;
    DBMS_LOB.TRIM(l_text, 4);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson9_step;
        RAISE;
END;
/

Verify [ABCD] and length 4, then ROLLBACK. TRIM cannot be used to extend a value beyond its existing length. It also does not identify an arbitrary middle range. State the desired final length explicitly when adapting the example, and check it against the current value.

Empty the LOB while keeping the row

Trimming to zero removes all characters while retaining an initialized non-NULL LOB. It is useful when the document slot must remain available but have no current contents. Run this separate experiment from the restored starting state.

DECLARE
    l_text CLOB;
BEGIN
    SAVEPOINT lesson9_step;
    SELECT doc_content INTO l_text
    FROM lesson9_documents WHERE doc_id = 1 FOR UPDATE;
    DBMS_LOB.TRIM(l_text, 0);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO lesson9_step;
        RAISE;
END;
/

The verification query should show EMPTY LOB and length 0 for row 1. The primary key and BLOB remain present. After inspecting the result, ROLLBACK. You can also assign an empty value through SQL; the following alternative empties both columns in the selected row.

UPDATE lesson9_documents
SET doc_content = EMPTY_CLOB(),
    binary_content = EMPTY_BLOB()
WHERE doc_id = 1;

Verify zero lengths for both LOBs, then ROLLBACK again. SQL assignment does not require an earlier SELECT FOR UPDATE because the UPDATE performs its own row locking. If you subsequently modify the assigned value through DBMS_LOB, acquire the appropriate stored locator rather than passing a standalone EMPTY_CLOB expression as a writable persistent value.

Assign NULL when the value should be absent

The practice columns allow NULL. This experiment preserves the row but assigns no non-NULL CLOB value. It is a different data state from an empty initialized document, and an application may give those states different meanings.

UPDATE lesson9_documents
SET doc_content = NULL
WHERE doc_id = 1;

Verify NULL LOB and a NULL text length while row 1 and its BLOB still exist. Then ROLLBACK. An empty preview is insufficient evidence: NULL and empty values can look similar in a client display. Oracle explains the distinction in its SQL LOB semantics. A NOT NULL constraint would prevent this assignment.

Delete the selected row

Use SQL DELETE when the containing record should disappear. The predicate below selects only the first practice row. Removing that row also removes its CLOB and BLOB values from the table’s current logical state; DBMS_LOB does not perform this row-level operation.

DELETE FROM lesson9_documents
WHERE doc_id = 1;

Run the verification query and expect MISSING ROW for identifier 1. Identifier 2 should still contain its original values. ROLLBACK restores row 1 in this uncommitted experiment. In a real application, check the affected-row count and intended identifier before deciding to commit a deletion.

Identical contents do not make two rows the same document instance. Deleting one does not inherently delete the other merely because their LOB values match or were copied. Application triggers or cascading relationships can introduce additional effects, so inspect those separately. Logical independence does not require separate physical copies of every byte; storage features can share data internally.

Recover the starting state before proceeding

If an experiment produces an unexpected result, stop the sequence and inspect the error and current row state. Do not substitute COMMIT merely because the client has returned to its prompt. A client with autocommit enabled can make the planned rollback ineffective. Repeating the setup inserts against existing identifiers can also fail rather than reset the data. Confirm the two original rows before resuming.

The verification query is intentionally small enough to show every byte and character in this example. For larger values, choose checks appropriate to the required change: compare the retained prefix and suffix, confirm the exact changed range, and validate the document through its consuming application. A shorter value is not automatically the correct value, and a successful SQL statement is not evidence that the intended business record was selected.

Respect transaction and storage boundaries

The locator-based examples keep the row lock and modification in one transaction. After rollback or commit, retrieve a fresh locator for another write. Do not hold a locator across transaction boundaries and assume that it remains suitable for modification. These rules concern persistent LOBs; temporary LOB lifecycle management is a different topic.

OPEN and CLOSE are optional for the simple internal-LOB calls shown here. They do not replace destination locking. If you explicitly open a LOB to group operations, follow the documented close-before-commit requirements and handle cleanup on exceptions. Keep the basic correctness of the operation clear before adding optimizations.

Amounts and offsets for BLOBs are measured in bytes. Character LOB APIs use character-oriented units under their character-set rules. The ASCII examples avoid ambiguity. Multilingual text requires attention to character encoding and supplementary characters; counting displayed symbols is not a universal way to calculate byte offsets.

DROP TABLE and TRUNCATE TABLE are broader DDL operations, not substitutes for a selected-row DELETE. They have different scope and transaction behavior and do not belong in this rollback exercise. Dropping a tablespace is broader still. None is required to learn how to erase or empty one document.

Logical deletion does not promise immediate segment shrinkage, released operating-system disk space, or destruction of historical versions and backups. Similarly, overwriting a visible range with zeros is not a complete data-sanitization procedure. Storage reclamation and retention policies require separate administration decisions.

BFILE values refer to external files, whose contents are read-only through these LOB APIs. Deleting a database reference does not delete the operating-system file. Do not adapt an internal-BLOB erasure example into an external-file deletion recipe.

When a middle fragment must actually disappear

ERASE and TRIM are not the only available LOB operations. Oracle provides SecureFiles-specific FRAGMENT_DELETE for deleting a fragment rather than filling it. Its storage-type and range restrictions must be satisfied. Do not assume that a table uses a compatible storage type merely because its column is declared CLOB or BLOB.

For this introductory lesson, use the demonstrated operations according to their stated outcomes. If a business requirement specifically calls for removing a middle fragment, review the relevant API and validate the complete resulting document. A text preview and matching length alone cannot establish that a large structured document remains valid.

Erasing Data within LOB: Exercise

Practice with the Erasing Data within Lob - Exercise. DBMS_LOB.ERASE receives a locator, a writable amount variable, and a starting offset. Interpret the units according to the LOB type and compare the returned amount with the request. Opening the linked exercise does not itself execute a database operation.

Before continuing, explain why erasing every character can leave a nonempty-length LOB, while trimming to zero leaves an empty value and assigning NULL leaves a different state. Then continue to the module conclusion to review the complete LOB workflow.

SEMrush Software 9 SEMrush Banner 9