How to Work with Temporary LOBs in PL/SQL (DBMS_LOB)

A temporary LOB is a CLOB or BLOB that lives in the temporary tablespace instead of a table. It is how PL/SQL builds large text or binary values: create one, write and append to it, read pieces back, and free it when done. DBMS_LOB has the procedures for every step.

Code for This Guide

The main examples are in the examples/pkg-dbms-lob folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_lob.createtemporary(lob, cache => true);
dbms_lob.write(lob, amount, offset, buffer);
dbms_lob.writeappend(lob, amount, buffer);
dbms_lob.read(lob, amount_in_out, offset, buffer_out);
dbms_lob.freetemporary(lob);

Offsets start at 1, and amounts and offsets are in characters for CLOBs and bytes for BLOBs.

Create, Write, and Read

Example:

declare
  v_doc   clob;
  v_part  varchar2(100);
  v_amt   integer := 20;
begin
  dbms_lob.createtemporary(v_doc, cache => true);
  dbms_lob.write(v_doc, 13, 1, 'Nimbus Air - ');
  dbms_lob.writeappend(v_doc, 29, 'baggage policy, version 2026.');
  dbms_output.put_line('length: ' || dbms_lob.getlength(v_doc));
  dbms_lob.read(v_doc, v_amt, 14, v_part);
  dbms_output.put_line('read ' || v_amt || ' chars from 14: ' || v_part);
  dbms_output.put_line('substr: ' || dbms_lob.substr(v_doc, 6, 1));
  dbms_output.put_line('instr of "version": ' || dbms_lob.instr(v_doc, 'version'));
  dbms_output.put_line('temporary? ' || dbms_lob.istemporary(v_doc));
  dbms_lob.freetemporary(v_doc);
end;
/

Output:

length: 42
read 20 chars from 14: baggage policy, vers
substr: Nimbus
instr of "version": 30
temporary? 1

PL/SQL procedure successfully completed.

• WRITE puts 13 characters at offset 1, and WRITEAPPEND adds 29 more at the end, for 42.
• READ returns up to the amount asked for and sets the amount to what it actually read.
• SUBSTR and INSTR work like their SQL namesakes, and ISTEMPORARY returns 1.

Copy, Append, Trim, Erase

Example:

declare
  v_a clob;
  v_b clob;
  v_amount integer := 5;
begin
  dbms_lob.createtemporary(v_a, true);
  dbms_lob.createtemporary(v_b, true);
  dbms_lob.writeappend(v_a, 26, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
  dbms_lob.copy(v_b, v_a, 10, 1, 1);                        -- first 10 characters
  dbms_lob.append(v_b, v_a);
  dbms_lob.trim(v_b, 20);
  dbms_lob.erase(v_b, v_amount, 3);                        -- 5 characters become spaces
  dbms_output.put_line('[' || v_b || ']');
  dbms_output.put_line('compare: ' || dbms_lob.compare(v_a, v_b));
  dbms_lob.freetemporary(v_a);
  dbms_lob.freetemporary(v_b);
end;
/

Output:

[AB     HIJABCDEFGHIJ]
compare: 1

PL/SQL procedure successfully completed.

COPY takes the first 10 letters, APPEND adds all 26, TRIM cuts the result to 20, and ERASE turns 5 characters from position 3 into spaces without changing the length. COMPARE returns 0 only for equal LOBs.

Value LOBs Are Read-Only

A LOB returned by a SQL function with the VALUE keyword is a read-only temporary LOB that Oracle frees for you.

Example:

declare
  v_doc clob;
begin
  select json_serialize(json('{"flight":"NA417"}') returning clob value)
  into   v_doc from dual;
  dbms_output.put_line(v_doc || ', length ' || dbms_lob.getlength(v_doc));
  dbms_lob.writeappend(v_doc, 1, ' ');                       -- a value LOB is read-only
end;
/

Output:

{"flight":"NA417"}, length 18

declare
*
ERROR at line 1:
ORA-24822: operation not allowed for value LOBs
ORA-06512: at "SYS.DBMS_LOB", line 1864
ORA-06512: at line 7

Reading works, but WRITEAPPEND fails with ORA-24822. Copy it into your own temporary LOB if you need to change it.

Things to Know

• Free temporary LOBs you create, especially in loops; otherwise they hold temporary space until the session ends.
• CACHE => TRUE keeps the LOB in the buffer cache, which is faster for LOBs you read and write often.
• For CLOBs up to 32,767 bytes, plain PL/SQL string operations on the CLOB variable also work.

Related Guides

• Oracle PL/SQL: Check If BLOB or CLOB is Empty
• Oracle EMPTY_CLOB Function: A Simple Guide

Conclusion

Temporary LOBs hold large text and binary values in PL/SQL. Create them with CREATETEMPORARY, build them with WRITE, WRITEAPPEND, COPY, and APPEND, read them with READ and SUBSTR, and free them with FREETEMPORARY when you are done.