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

Large Objects Are Different

CLOBs and BLOBs can store up to 128 terabytes per column value. While Oracle makes them appear similar to VARCHAR2 and RAW, they behave differently under the hood. Understanding these differences is essential for writing efficient 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, but this has a 32,767 byte limit in PL/SQL. Concatenating a VARCHAR2 onto a CLOB works, but assigning a large CLOB to a VARCHAR2 will silently truncate:

DECLARE
    l_clob CLOB := 'Some large text...';
    l_text VARCHAR2(32767);
BEGIN
    l_clob := l_clob || ' more text';           -- Works fine
    l_text := DBMS_LOB.SUBSTR(l_clob, 32767, 1); -- Safe approach
END;

Building Large CLOBs Efficiently

Avoid repeated concatenation with the || operator, which creates temporary LOBs. Use DBMS_LOB.WRITEAPPEND instead:

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, then:
    DBMS_LOB.FREETEMPORARY(l_clob);
END;

Reading BLOBs in Chunks

DECLARE
    l_blob   BLOB;
    l_amount PLS_INTEGER := 32767;
    l_offset PLS_INTEGER := 1;
    l_buffer RAW(32767);
BEGIN
    SELECT file_content INTO l_blob FROM uploads WHERE id = 100;
    WHILE l_offset <= DBMS_LOB.GETLENGTH(l_blob) LOOP
        DBMS_LOB.READ(l_blob, l_amount, l_offset, l_buffer);
        l_offset := l_offset + l_amount;
    END LOOP;
END;

Temporary LOBs and SecureFiles

Always free temporary LOBs with DBMS_LOB.FREETEMPORARY. If your process creates many without freeing them, you can exhaust temporary tablespace. Monitor usage through V$TEMPORARY_LOBS. Also check whether your LOB columns use the newer SecureFiles storage (default since 11g) which provides better performance, compression, and deduplication compared to BasicFiles.