One way to get a smaller CLOB from another CLOB is to follow the extract CLOB from JSON example: use DBMS_LOB INSTR/SUBSTR functions to obtain the CLOB fragment of interest. We could look at the same problem from another angle: delete the CLOB fragments *not* of interest, retaining only what we want, with DBMS_LOB.FRAGMENT_DELETE.
Deleting CLOB fragments would be apt for your use case if:
- the CLOB persists in the database
- the fragment of interest forms the bulk of the CLOB data
- it is not necessary to retain the original CLOB i.e. the fragment of interest can update the same attribute that holds the original CLOB data
Here’s how to crop a CLOB from either end, using the procedure DBMS_LOB.FRAGMENT_DELETE.
CLOB Data Example and Cropping Needs
Consider the same CLOB data and "payload" extraction requirement as in the article on JSON CLOB retrieval.
CLOB Data:
[
{
"id": "1",
"payloadType": "csv",
"payload": "Very big payload 100K bytes"
}
]
To extract:
Very big payload 100K bytes
DBMS_LOB.FRAGMENT_DELETE Solution
Approach:
- Select the CLOB data FOR UPDATE
- Use dbms_lob.instr to find the start/end/length of the payload, based on the input keyname and delimiters
- Delete the pre- fragment: from character position 1 till the start of the payload
- Delete the post- fragment: from character position just after the length of the payload, till the end of the CLOB data
- Commit the transaction in the caller if the fragments have been deleted successfully
crop_clob function:
-- Function that takes as input (1) an id to pick CLOB data
-- for update (2) key name based on which CLOB fragments
-- are to be deleted
CREATE OR REPLACE FUNCTION crop_clob
(p_clob_ref_id IN NUMBER
, p_keyname IN VARCHAR2)
RETURN BOOLEAN
IS
l_payload CLOB := empty_clob();
l_payload_init PLS_INTEGER;
l_payload_end PLS_INTEGER;
l_payload_size PLS_INTEGER;
l_orig_clob_size PLS_INTEGER;
l_keyname VARCHAR2(100) := '"'||p_keyname||'":';
BEGIN
-- Select the CLOB data FOR UPDATE
select data into l_payload
from test_clobdata
where id = p_clob_ref_id
for update;
l_orig_clob_size := dbms_lob.getlength(l_payload);
-- Use dbms_lob.instr to find the start/end/length
-- of the target payload, based on input keyname
-- and delimiters
l_payload_init := dbms_lob.instr(l_payload
, l_keyname)
+ length(l_keyname) + 1;
l_payload_end := dbms_lob.instr(l_payload
, '"', l_payload_init + 1) - 1;
l_payload_size := l_payload_end - l_payload_init;
-- FRAGMENT_DELETE takes as input:
-- (1) the CLOB data
-- (2) the fragment length to be deleted
-- (3) the offset from the start of the CLOB data
-- Delete the pre- fragment: from character position
-- 1 till the start of the payload
dbms_lob.fragment_delete(
l_payload
, l_payload_init
, 1
);
-- Delete the post- fragment: from character position
-- just after the length of the payload, till the end
-- of the end of the CLOB data
dbms_lob.fragment_delete(
l_payload -- CLOB data
, (l_orig_clob_size - l_payload_end)
, l_payload_size + 1
);
RETURN TRUE;
END crop_clob;
When compiled:
SQL> -- Function that takes as input (1) an id to pick CLOB data SQL> -- for update (2) key name based on which CLOB fragments SQL> -- are to be deleted SQL> CREATE OR REPLACE FUNCTION crop_clob 2 (p_clob_ref_id IN NUMBER 3 , p_keyname IN VARCHAR2) 4 RETURN BOOLEAN 5 IS 6 l_payload CLOB; 7 l_payload_init PLS_INTEGER; 8 l_payload_end PLS_INTEGER; 9 l_payload_size PLS_INTEGER; 10 l_keyname VARCHAR2(100) := '"'||p_keyname||'":'; 11 BEGIN 12 13 -- Select the CLOB data FOR UPDATE 14 select data into l_payload 15 from test_clobdata 16 where id = p_clob_ref_id 17 for update; 18 19 -- Use dbms_lob.instr to find the start/end/length 20 -- of the payload, based on input keyname and delimiters 21 -- 22 l_payload_init := dbms_lob.instr(l_payload 23 , l_keyname) 24 + length(l_keyname) + 1; 25 26 -- End position of payload 27 l_payload_end := dbms_lob.instr(l_payload 28 , '"', l_payload_init + 1) - 1; 29 30 l_payload_size := l_payload_end - l_payload_init; 31 32 -- FRAGMENT_DELETE takes as input: 33 -- (1) the CLOB data 34 -- (2) the fragment length to be deleted 35 -- (3) the offset from the start of the CLOB data 36 37 -- Delete the pre- fragment: from character position 1 38 -- till the start of the payload 39 dbms_lob.fragment_delete( 40 l_payload 41 , l_payload_init 42 , 1 43 ); 44 -- Delete the post- fragment: from character position 45 -- just after the length of the payload, till the end 46 -- of the CLOB data 47 dbms_lob.fragment_delete( 48 l_payload -- CLOB data 49 , (l_orig_clob_size - l_payload_end) 50 , l_payload_size + 1 51 ); 52 RETURN TRUE; 53 54 END crop_clob; 55 / Function created.
Testing the Solution: Crop a CLOB with DBMS_LOB.FRAGMENT_DELETE
Table to hold the CLOB data:
SQL> -- Table to hold the CLOB data SQL> desc test_clobdata Name Null? Type ----------------------- -------- ----------- ID NUMBER DATA CLOB
Original CLOB data (before cropping):
SQL> -- Original CLOB data
SQL> select * from test_clobdata;
ID DATA
--- -----------------------------------
1 [
{
"id": "1",
"payloadType": "csv",
"payload": "Test data"
}
]
Call crop_clob in an anonymous block:
SQL> -- Call crop_clob in an anonymous block SQL> begin 2 if (crop_clob(1, 'payload')) then 3 commit; 4 end if; 5 end; 6 / PL/SQL procedure successfully completed.
Cropped CLOB data:
SQL> -- Cropped CLOB data SQL> select * from test_clobdata; ID DATA --- ------------------------------ 1 Test data
Summary
This article shows how to delete fragments from a CLOB, from either end, using DBMS_LOB.FRAGMENT_DELETE. This solution can be used to retain only a part of an CLOB that persists in the database, without having to rewrite all the CLOB data.
