Reading and Writing CLOB objects

Hi,

   In this post , we can look into how to read and write CLOB objects.

Why we need to use CLOB objects?

 The only downside of using VARCHAR2 is that it is limited to 4000 characters, 

so if we are creating large queries or if we you store resume or text files in database, then

we can use this CLOb, where we can store upto 2GB .

Steps

1. Creating a table containing a CLOB column

    

SQL> create table lob_contain
2  (id number,
3   lob_ptr clob default empty_clob());
Table created.

    

2. Create a procedcure to write a text to the CLOB column

Declare

lob_locator CLOB;

text varchar2(200) := 'Resume NAme - G.Subramanian KAushik City - Chennai
Location - triplicane Occupation - Software engineer Hobbies - Reading astrology
books,nemerology, Playing video games';

amount number;

offset integer;

Begin

-- Setting the amount to be written
amount := length(text);

-- Lock the record in the table lob_contain to get a expicit lock over the record
Select lob_ptr into lob_locator from lob_contain where id = 1 FOR UPDATE;

--Setting the Offset
offset := DBMS_LOB.GETLENGTH(lob_locator)+2;

--Write the text to the CLOB table
DBMS_LOB.WRITE(lob_locator,amount,offset,text);
COMMIT;
END;    
 

3. Select the inserted value as a VARCHAR2 text.

    

   SQL>  select * from lob_contain;

   ID                                 LOB_PTR

-------      ---------------------------------------------------------

   1            Resume NAme - G.Subramanian KAushik City - Chennai 

                 Location - triplicane