Clobs

Not many tables demand the use of CLOBs (Character Large Objects). CLOB data type columns can store up to 4 GB of text. When designing a new application, developers face a dilemma – I need a column where I need to store a large block of text. Should I use multiple VARCHAR2 columns or CLOB?

The decision to use VARCHAR2 instead of CLOB is usually driven by the fear of the unknown. One should always use CLOB when it comes to deciding about multiple VARCHAR2 columns vs. CLOB to store a single logical piece of textual data.

Example use string in Oracle CLOB column:

create table aTable(
      id number,
      aClobColumn  clob
    );

insert into aTable values (1,'value' );
insert into aTable values (1,'values are like this' );

select * from aTable where dbms_lob.substr( aClobColumn , 4000, 1 )='value';

Source, try it online: http://sqlfiddle.com/#!4/1878f6/164

Accessing a CLOB as a VARCHAR2 in PL/SQL

The following example illustrates the way CLOB manage.

Length of CLOB

declare myStoryBuf VARCHAR2(4001); 
BEGIN SELECT 
  ad_sourcetext INTO myStoryBuf FROM print_media WHERE ad_id = 12001; 
  -- Display Story by printing myStoryBuf directly 
END; /

Assigning a CLOB to a VARCHAR2 in PL/SQL

declare myLOB CLOB; 
BEGIN 
  SELECT 'ABCDE' INTO myLOB FROM print_media WHERE ad_id = 11001; 
  -- myLOB is a temporary LOB. 
  -- Use myLOB as a lob locator DBMS_OUTPUT.PUT_LINE('Is temp? '||DBMS_LOB.ISTEMPORARY(myLOB)); 
END; 
/

Find in CLOB string

select * from my_table where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1;

Other Functions and Operators on LOBs are here.

select dbms_lob.getlength(ad_sourcetext) 
from Print_media 
where product_id=3106 and ad_id = 13001; 
/
declare 
clob1 clob; 
amt number:=10; 
BEGIN 
 -- select a clob column into a clob, no implicit convesion 
 SELECT ad_sourcetext INTO clob1 
 FROM Print_media WHERE product_id=3106 and ad_id=13001 FOR UPDATE; 
 dbms_lob.trim(clob1, amt); -- Trim the selected lob to 10 bytes 
END; 
/

Read from CLOB column

PL/SQL (DBMS_LOB Package): Reading Data from a LOB. This file is installed in the following path when you install the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lread.sql. Procedure readLOB_proc is not part of the DBMS_LOB package: reading data from lob.

CREATE OR REPLACE PROCEDURE readLOB_proc (Lob_loc IN OUT BLOB) IS
    /* Note: Lob_loc can be a persistent or a temporary LOB */
    Buffer            RAW(32767);
    Amount            BINARY_INTEGER := 32767;
    Position          INTEGER := 2;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB READ EXAMPLE ------------');
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
   /* Read data from the LOB: */
   DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);

   DBMS_OUTPUT.PUT_LINE(RAWTOHEX(substr(Buffer, 1, 200)));
END;
/

SUBSTR

SELECT a.*, dbms_lob.substr( sql_text, 4000, 1) sql_text_vc FROM dba_hist_sqltext a;

Convert string to CLOB

declare   
   c1 clob := to_clob('abc');
   c2 clob;
 begin
      case c1
          when to_clob('abc') then dbms_output.put_line('abc');
          when to_clob('def') then dbms_output.put_line('def');
      end case;

      c2 := case c1
        when to_clob('abc') then 'abc'
         when to_clob('def') then 'def'
     end;
      dbms_output.put_line(c2);
  end;
/
select dbms_lob.substr( x, 4000, 1 ) from T;
/

Update CLOB columns

declare
 v_lob clob;
begin
  for r_lob in (select cfrom foo for update)
  loop
    dbms_lob.write(r_lob.c, 6, 16, 'phrase'); -- ie write at offset 16, 6 bytes
  end loop;
end;

Replace in CLOB column

select to_clob(replace(' and ','and', 'or')) from dual;
TO_CLOB(REPLACE('AND','AND','OR'))

SELECT regexp_replace('I need to replace this Everything can be here ', '.*','') FROM dual;

REGEXP_REPLACE

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )

Parameters or Arguments

The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The regular expression matching information.

Parameters can be a combination of the :