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 7Reading 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
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.
