Oracle PL/SQL - String operations on CLOBs

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Large objects (CLOB, BLOB)

Introduction

You can use regular string operations on CLOBs:

  • search for the patterns,
  • get length,
  • get part of the code,
  • and so on

The following code implements a search or indexing routine for all large text files loaded in the database. 

declare
     v_manual_cl   CLOB;
     v_nr          NUMBER;
     v_tx          VARCHAR2 (2000);
     v_add_tx      VARCHAR2 (2000):='Loaded: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi');
begin
     select manual_cl             
        into v_manual_cl
        from t_product
     where id = 1
     for update;      
                 
     DBMS_LOB.writeappend (v_manual_cl,LENGTH (v_add_tx), v_add_tx);                                
     v_nr := INSTR (v_manual_cl, 'Loaded:', -1);                       
     v_tx := SUBSTR (v_manual_cl, v_nr);
     DBMS_OUTPUT.put_line (v_tx);
end;

Related Topic