Working With LOBs in PL/SQL: CLOB and BLOB Best Practices

Large Objects Are Different

CLOBs (Character Large Objects) and BLOBs (Binary Large Objects) can store up to 128 terabytes of data per column value. While Oracle makes them appear similar to VARCHAR2 and RAW in many contexts, they behave very differently under the hood. Understanding these differences is essential for writing efficient, correct PL/SQL that works with large text documents, PDFs, images, and other large content.

Implicit Conversion Pitfalls

Oracle will implicitly convert between VARCHAR2 and CLOB in many situations, but this conversion has a 32,767 byte limit in PL/SQL. Concatenating a VARCHAR2 onto a CLOB works fine, but concatenating a CLOB onto a VARCHAR2 will silently truncate if the result exceeds 32,767 bytes:

DECLARE
    l_clob  CLOB := 'Some large text...';
    l_text  VARCHAR2(32767);
BEGIN
    -- This works: VARCHAR2 is converted to CLOB
    l_clob := l_clob || ' more text';

    -- DANGER: This truncates if l_clob > 32767 bytes
    l_text := l_clob;

    -- Safe approach: use DBMS_LOB.SUBSTR
    l_text := DBMS_LOB.SUBSTR(l_clob, 32767, 1);
END;

Building Large CLOBs Efficiently

When constructing a large CLOB from many small pieces, avoid repeated concatenation with the || operator. Each concatenation can create a temporary LOB. Instead, use DBMS_LOB.WRITEAPPEND:

DECLARE
    l_clob CLOB;
    l_line VARCHAR2(4000);
BEGIN
    DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);

    FOR rec IN (SELECT line_text FROM document_lines ORDER BY line_no)
    LOOP
        l_line := rec.line_text || CHR(10);
        DBMS_LOB.WRITEAPPEND(l_clob, LENGTH(l_line), l_line);
    END LOOP;

    -- Use l_clob...

    DBMS_LOB.FREETEMPORARY(l_clob);
END;

Reading BLOBs in Chunks

When processing a BLOB, such as reading an uploaded file, read it in chunks rather than trying to load the entire content into memory:

DECLARE
    l_blob   BLOB;
    l_amount PLS_INTEGER := 32767;
    l_offset PLS_INTEGER := 1;
    l_buffer RAW(32767);
    l_length PLS_INTEGER;
BEGIN
    SELECT file_content INTO l_blob FROM uploads WHERE id = 100;
    l_length := DBMS_LOB.GETLENGTH(l_blob);

    WHILE l_offset <= l_length LOOP
        DBMS_LOB.READ(l_blob, l_amount, l_offset, l_buffer);
        -- Process chunk in l_buffer
        l_offset := l_offset + l_amount;
    END LOOP;
END;

Temporary LOBs and Memory

Temporary LOBs created with DBMS_LOB.CREATETEMPORARY consume temporary tablespace. Always free them with DBMS_LOB.FREETEMPORARY when done. If your process creates many temporary LOBs in a loop without freeing them, you can exhaust temporary tablespace. Monitor temporary LOB usage through V$TEMPORARY_LOBS.

SecureFiles vs BasicFiles

Since Oracle 11g, the default LOB storage is SecureFiles, which provides better performance, compression, and deduplication compared to the older BasicFiles format. If you are working with a database upgraded from an older version, check whether your LOB columns are still using BasicFiles and consider migrating them to SecureFiles for improved performance.