Crop a CLOB with DBMS_LOB FRAGMENT_DELETE

December 14, 2017

in PL/SQL

DBMS_LOB.FRAGMENT_DELETE

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:

  1. Select the CLOB data FOR UPDATE
  2. Use dbms_lob.instr to find the start/end/length of the payload, based on the input keyname and delimiters
  3. Delete the pre- fragment: from character position 1 till the start of the payload
  4. Delete the post- fragment: from character position just after the length of the payload, till the end of the CLOB data
  5. 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.

Leave a Comment

Previous post:

Next post: