How to Edit SecureFile LOBs with DBMS_LOB.FRAGMENT_ Procedures

Changing a word in the middle of a large LOB normally means rewriting everything after it. For LOBs stored as SecureFiles, DBMS_LOB has FRAGMENT_ procedures that insert, delete, replace, and move data in place, shifting the rest of the LOB without rewriting it.

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.fragment_insert(lob, amount, offset, data);
dbms_lob.fragment_delete(lob, amount, offset);
dbms_lob.fragment_replace(lob, old_amount, new_amount, offset, data);
dbms_lob.fragment_move(lob, amount, src_offset, dest_offset);

The examples use a small table with a SecureFile CLOB column:

Setup:

create table gate_notices (id number primary key, text clob)
  lob (text) store as securefile;

Insert, Replace, Delete

Example:

insert into gate_notices values (1, 'NA417 to LHR: gate A1, boarding 10:05');

declare
  v_text clob;
begin
  select text into v_text from gate_notices where id = 1 for update;
  dbms_lob.fragment_replace(v_text, 2, 3, 20, 'B12');       -- A1 becomes B12
  dbms_output.put_line(v_text);
  dbms_lob.fragment_insert(v_text, 9, 1, 'DELAYED: ');       -- insert at the start
  dbms_output.put_line(v_text);
  dbms_lob.fragment_delete(v_text, 9, 1);                    -- and take it out again
  dbms_output.put_line(v_text);
  commit;
end;
/

Output:

1 row inserted.

NA417 to LHR: gate B12, boarding 10:05
DELAYED: NA417 to LHR: gate B12, boarding 10:05
NA417 to LHR: gate B12, boarding 10:05

PL/SQL procedure successfully completed.

• FRAGMENT_REPLACE swaps the 2 characters A1 for the 3 characters B12, and the rest of the text moves along.
• FRAGMENT_INSERT adds DELAYED: at the start without overwriting anything.
• FRAGMENT_DELETE removes those 9 characters again.

The example drops the table afterward.

Move a Fragment

Example:

insert into gate_notices values (1, 'Boarding 10:05. NA417 to LHR. ');

declare
  v_text clob;
begin
  select text into v_text from gate_notices where id = 1 for update;
  dbms_lob.fragment_move(v_text, 16, 1, 31);       -- move the first 16 characters to the end
  dbms_output.put_line(v_text);
  rollback;
end;
/

Output:

1 row inserted.

NA417 to LHR. Boarding 10:05.

PL/SQL procedure successfully completed.

FRAGMENT_MOVE takes the first 16 characters, the boarding time, and moves them to the end of the notice. The block rolls back, and the example drops the table afterward.

Things to Know

• The procedures work only on SecureFile LOBs, the default for new LOB columns, not on BasicFile LOBs.
• Lock the row with SELECT ... FOR UPDATE before changing its LOB.
• Unlike WRITE, which overwrites, the FRAGMENT_ procedures change the length of the LOB.

Related Guides

• How to Work with Temporary LOBs in PL/SQL (DBMS_LOB)
• How to Lock Rows with SELECT FOR UPDATE in Oracle

Conclusion

DBMS_LOB.FRAGMENT_INSERT, FRAGMENT_DELETE, FRAGMENT_REPLACE, and FRAGMENT_MOVE edit SecureFile LOBs in place, inserting and removing data without rewriting the rest. Use them for small edits to large LOBs stored in tables.