Contains information about Teradata,BTEQ,FASTLOAD,FASTEXPORT, Oracle Pl/SQL,forms, JAVA open source related topics and discussions
Oracle forms and reports
  Oracle database 

05 May 2009

Reading and Writing CLOB objects

   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 .

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


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;


-- 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
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



  1. great info there kaushik......but the example wasnt that showcase the use of a CLOB (character large objects) you would have to actually use large data....
    why not read a doc on the fly and store that in a CLOB....I m not sure how its done in oracle

    but in mssql we could make use of ad hoc queries,
    probably convert the doc to binary format (BLOB)

    On a personal note,I prefer MS SQL to oracle .....handling of both BLOBs and CLOBs are a piece of cake in MSSQL..try it...

  2. Hi Linson,
    Thanks for your comment :-)
    It is just a simple example for reading a text from the user and writing it to a CLOB object.There are more advanced examples which is available in Web and PL/SQL is more powerful than T-SQL.

    I learnt T-SQL first in SQL SERVER 2000, the moved to Oracle PL/SQL

    I prefer MS SQL to oracle .....handling of both BLOBs and CLOBs - Linson In Oracle also we can do it using existing procedures available in JAVA. we can call a JAVA method in PL/SQL, just like local methods and do it and we have a lot of JARs for doing it.

    Kindly read my next post where I would post about , how to call JAVA methods directly in PL/SQl