Retrieve and Display LOB Data in Oracle 26ai

Choose the representation you intend to retrieve

The LOB datatype determines how the value should be interpreted. A locator identifies the LOB for an operation; a scalar buffer holds the portion being processed. Selecting a CLOB locator into a CLOB variable is therefore different from converting the entire document into VARCHAR2.

CLOB is not restricted to single-byte text, and NCLOB does not simply mean any multibyte content. Keep character-set semantics separate from file format. A BLOB containing a PNG image is not a character string, even though a text-based display could show a hexadecimal representation of its bytes.

Legacy applications may contain LONG or LONG RAW columns. For new large-object designs, use suitable LOB types rather than copying those historical declarations. The examples here use a relational table, not an object table. The same locator concepts also apply when retrieving supported LOB attributes from object-table rows.

Create a small practice dataset

Use an unused practice schema with CREATE TABLE privileges and sufficient tablespace quota. The explicit SecureFiles clauses assume that the default tablespace uses Automatic Segment Space Management and that the database configuration permits SecureFiles. Do not drop an existing application table to reuse the example name.

The following setup creates one short text value and a four-byte binary value. The binary sample is deliberately synthetic, so its expected hexadecimal output can be checked without an external file. The examples are documentation-reviewed teaching code; they have not been executed against your database.

CREATE TABLE app_documents (
    doc_id      NUMBER PRIMARY KEY,
    doc_title   VARCHAR2(100),
    doc_content CLOB,
    doc_binary  BLOB
)
LOB (doc_content) STORE AS SECUREFILE
LOB (doc_binary) STORE AS SECUREFILE;

INSERT INTO app_documents
    (doc_id, doc_title, doc_content, doc_binary)
VALUES
    (1, 'LOB retrieval example',
     TO_CLOB('Hello from Oracle 26ai.'),
     TO_BLOB(HEXTORAW('01020304')));

COMMIT;

COMMIT retains the inserted practice row. Run the setup once: repeating CREATE TABLE conflicts with the existing name, and reinserting identifier 1 violates the primary key. Later retrieval examples only read this dataset. They do not need to change the row or commit another transaction.

The text literal contains 22 characters, including spaces and the final period. The hexadecimal input describes four bytes, not eight text characters stored in a BLOB. Oracle's TO_BLOB reference describes converting RAW input into a BLOB for this kind of SQL expression.

Inspect lengths before displaying content

Start with a query that identifies the document and reports the sizes of its two LOB values. It is often more useful to inspect metadata first than to request an entire large document immediately.

SELECT doc_id,
       doc_title,
       DBMS_LOB.GETLENGTH(doc_content) AS text_length,
       DBMS_LOB.GETLENGTH(doc_binary) AS binary_bytes
FROM app_documents
ORDER BY doc_id;

For the sample row, the expected text length is 22 and the expected binary length is 4. These are calculated expectations rather than recorded execution results. The query passes the column values directly to GETLENGTH; a separate PL/SQL variable is not required when the function is used this way.

Distinguish three outcomes. No selected row means the identifier was not found. A selected row can instead contain a NULL LOB, meaning there is no value in that column. An initialized empty LOB is a non-null value with length zero. A useful diagnostic should not report all three as an empty document.

Display CLOB text directly in SQL*Plus

For a simple inspection, SQL*Plus can display a selected CLOB. Its SET LONG option controls the maximum amount displayed for relevant large values, despite the historical name of the setting.

SET LONG 10000

SELECT doc_content
FROM app_documents
WHERE doc_id = 1;

This changes the client's display limit, not the stored document. A display that ends early does not establish that the database value was truncated. Compare the reported length with your client settings before diagnosing missing data. Formatting and wrapping can also affect what appears on screen.

SET LONG is separate from DBMS_OUTPUT configuration. It does not increase a PL/SQL VARCHAR2 buffer or change DBMS_OUTPUT's per-line limit. See the SQL*Plus SET reference for client presentation controls.

Retrieve a short text preview with PL/SQL

A short preview is appropriate when the user needs the beginning of a document rather than its complete contents. Select the locator first, then evaluate DBMS_LOB.SUBSTR in PL/SQL. This avoids relying on an implicit conversion of an unknown-size CLOB into a scalar string.

DECLARE
    l_clob    CLOB;
    l_preview VARCHAR2(32767);
BEGIN
    BEGIN
        SELECT doc_content INTO l_clob
        FROM app_documents WHERE doc_id = 1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No document found for doc_id = 1.');
            RETURN;
    END;

    IF l_clob IS NOT NULL THEN
        l_preview := DBMS_LOB.SUBSTR(l_clob, 1000, 1);
        DBMS_OUTPUT.PUT_LINE(l_preview);
    END IF;
END;
/

Enable DBMS Output in your client before running the block. In SQL*Plus or SQLcl, use SET SERVEROUTPUT ON. The slash executes the buffered block in script mode; it is not part of the SQL text sent through an application driver.

The argument order is important: DBMS_LOB.SUBSTR takes the locator, the requested amount, and then the offset. Ordinary SQL SUBSTR takes the string, starting position, and then the length. Reversing these positions can produce a tiny or unexpected preview without expressing the operation you intended.

This example requests at most 1,000 LOB character units from position 1. It intentionally leaves later content undisplayed. In the NULL case it prints no preview; the larger example below gives explicit messages for NULL and empty values. Keeping the result bounded is more predictable than assuming that every stored document will always fit a VARCHAR2 variable.

Read CLOB text in bounded chunks

When a single preview is insufficient, read successive regions into a reusable buffer. The following diagnostic block assumes an AL32UTF8 database character set and requests 8,000 LOB character units per iteration. Its PL/SQL buffer allows 32,767 bytes. The character-set assumption matters because character counts and byte counts are not interchangeable.

The block limits the preview to approximately 32,000 LOB character units rather than sending an unlimited document to the output buffer. It checks for a missing row, NULL content, and an empty value before reading. The missing-row handler surrounds only SELECT INTO, so an unrelated read error is not mislabeled as a missing document.

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
    l_clob       CLOB;
    l_length     INTEGER;
    l_offset     INTEGER := 1;
    l_amount     BINARY_INTEGER;
    l_limit      INTEGER;
    l_buffer     VARCHAR2(32767);
BEGIN
    BEGIN
        SELECT doc_content
          INTO l_clob
          FROM app_documents
         WHERE doc_id = 1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No document found for doc_id = 1.');
            RETURN;
    END;

    IF l_clob IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('CLOB is NULL.');
    ELSE
        l_length := DBMS_LOB.GETLENGTH(l_clob);

        IF l_length = 0 THEN
            DBMS_OUTPUT.PUT_LINE('CLOB is empty.');
        ELSE
            l_limit := LEAST(l_length, 32000);

            WHILE l_offset <= l_limit LOOP
                l_amount := LEAST(8000, l_limit - l_offset + 1);
                DBMS_LOB.READ(l_clob, l_amount, l_offset, l_buffer);

                IF l_amount <= 0 THEN
                    RAISE_APPLICATION_ERROR(-20001, 'LOB read made no progress.');
                END IF;

                DBMS_OUTPUT.PUT_LINE(l_buffer);
                l_offset := l_offset + l_amount;
            END LOOP;

            IF l_offset <= l_length THEN
                DBMS_OUTPUT.PUT_LINE('[Remaining text omitted.]');
            END IF;
        END IF;
    END IF;
END;
/

The loop has three essential responsibilities. Before each read it sets the amount to the smaller of the chunk size and the remaining preview region. After the read it prints the returned buffer. It then advances the offset by the amount actually returned by READ.

The amount parameter is IN OUT: its input requests a size and its output reports the completed read. Advancing by the original request can skip content when the returned amount differs. Reusing a previous iteration's amount without resetting it can also change later requests unintentionally. The no-progress check prevents an unexpected zero-length read from creating an endless loop.

Offsets start at 1. The first read therefore begins with the first unit of content, not position zero. The loop stops when its next offset is beyond the preview region. If unread content remains, the final message makes the omission visible instead of presenting a partial document as complete.

Account for multibyte and supplementary characters

Oracle character LOB APIs use UCS2 code-point units for amounts and offsets. A supplementary Unicode character spans a surrogate pair in those units. A valid reader must not begin in the middle of that pair, and a read boundary may require adjustment to preserve a complete character.

That is another reason to use READ's returned amount. Do not substitute the requested size or an unrelated measurement of the converted buffer. The preview limit is approximate at a supplementary-character boundary because complete-character handling can affect the final read.

The 8,000-unit request has ample byte capacity under the stated AL32UTF8 assumption, but it is not a universal sizing formula for every encoding and client. An NCLOB variant also needs a buffer whose character form matches its source. Oracle's LOB character semantics explains boundary handling and why the returned amount may vary.

Understand what DBMS_OUTPUT does and does not provide

DBMS_OUTPUT buffers messages for retrieval by the client. SIZE UNLIMITED removes a configured total buffer limit; it does not remove the 32,767-byte line limit or eliminate memory and client-display costs. This example bounds the work even though the client command enables a large output buffer.

Each PUT_LINE adds a line terminator. Consequently, printing one line per chunk introduces boundaries that may not exist in the original document. A diagnostic display is useful for inspection but is not a faithful file-export mechanism. Do not compare a console transcript byte for byte with the stored document and assume every difference indicates corruption.

For a complete application download, use the driver's LOB streaming facilities and an appropriate output destination. Preserve the intended encoding and avoid inserting extra separators. A report viewer, text editor, and binary file download each have different presentation responsibilities. The DBMS_OUTPUT reference describes its buffering model and limits.

Inspect BLOB bytes as hexadecimal text

A BLOB must be handled as binary data unless its encoding and meaning are explicitly known. The following block reads no more than 2,000 bytes and converts them to hexadecimal for inspection. It does not attempt to interpret a photograph, compressed document, or arbitrary byte sequence as text.

DECLARE
    l_blob   BLOB;
    l_raw    RAW(2000);
    l_amount BINARY_INTEGER;
BEGIN
    BEGIN
        SELECT doc_binary INTO l_blob
        FROM app_documents WHERE doc_id = 1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No document found for doc_id = 1.');
            RETURN;
    END;

    IF l_blob IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('BLOB is NULL.');
    ELSIF DBMS_LOB.GETLENGTH(l_blob) = 0 THEN
        DBMS_OUTPUT.PUT_LINE('BLOB is empty.');
    ELSE
        l_amount := LEAST(2000, DBMS_LOB.GETLENGTH(l_blob));
        DBMS_LOB.READ(l_blob, l_amount, 1, l_raw);
        DBMS_OUTPUT.PUT_LINE('Hex preview: ' || RAWTOHEX(l_raw));
    END IF;
END;
/

For the practice row, the expected output is Hex preview: 01020304. Each byte becomes two hexadecimal characters, so a 2,000-byte read produces at most 4,000 hexadecimal characters before the short label is added. The expanded representation therefore fits comfortably within the output line limit.

This block performs one read from offset 1. If the BLOB is longer than the requested region, later bytes are not shown. A hexadecimal header can help identify or troubleshoot a file, but it is not a rendering of that file. Use an image viewer for a photograph and a suitable document viewer for a PDF.

Do not add UTL_RAW.CAST_TO_VARCHAR2 merely to make binary output appear readable. Such a cast is not a universal decoder. If the BLOB intentionally contains encoded text, establish the source encoding and use an appropriate conversion strategy, including handling character boundaries across reads.

Diagnose retrieval problems systematically

If SELECT INTO reports no row, confirm the identifier and current schema. If a query returns several rows where one is expected, correct the selection criteria rather than hiding the condition. The primary key in the demonstration ensures that identifier 1 selects at most one row.

If a read fails, inspect the locator, offset, requested amount, and buffer capacity. Do not catch every error and print "document not found." In these examples, the nested handler handles the lookup only; later errors remain visible to the caller. That distinction preserves information needed to diagnose an invalid read.

Plain retrieval does not require SELECT FOR UPDATE. That clause is relevant when acquiring locks for persistent LOB modification, which is a later topic. Similarly, these examples do not create application-owned temporary LOBs that require explicit FREETEMPORARY cleanup. They retrieve existing stored values for read-only inspection.

A BFILE reader introduces additional requirements: a valid server-side directory reference, an existing file, sufficient permissions, and appropriate open/close handling. The CLOB and BLOB examples here avoid those dependencies so you can learn the retrieval pattern before adding external-file management.

Practice the boundary cases

After running the small example in a practice environment, test a missing identifier, NULL values, and initialized empty LOBs. Then test text longer than one chunk and longer than the preview cap. Confirm that the displayed portion has no skipped or duplicated regions and that the omission message appears when expected.

Include multibyte text and supplementary characters near chunk boundaries. For binary data, test values shorter and longer than the preview size and confirm the two-hexadecimal-characters-per-byte expansion. These checks are necessary before adapting the demonstration into a reusable application reader; the examples do not claim database-executed validation.

The retrieval sequence remains consistent: select the locator, identify its state and length, choose a representation, and read within explicit limits. In the next lesson, you will explore techniques and tools for inserting LOB data. For individual routine signatures and restrictions, consult the Oracle 26ai DBMS_LOB reference.