clob hello world

write to a clob

SQL> var x clob
SQL> begin
2 dbms_lob.createtemporary(:x,true);
3 dbms_lob.writeappend(:x,12,'Hello World!');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> print x

Hello World!

read from a clob

SQL> var c varchar2(10)
SQL> var n number
SQL> exec :n := 5 /* read 5 characters, if possible */

PL/SQL procedure successfully completed.

SQL> begin
3,:n,7 /*position*/,:c);
4 dbms_lob.close(:x);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> print c


dbms_lob.createtemporary(:x,true); caches the lob. If the lob is large (a large object is often large), it may use a lot of memory, use cache=false for slower performance but more economic memory consumption

By Laurent Schneider

Oracle Certified Master


  1. In Oracle 10.2, you can write to a clob just like a varchar2…

    You can also use CLOBs in your SQL statments (e.g. WHERE MY_CLOB LIKE ‘SILLY ME%’)….

    Much easier!

  2. yes, for sure, but you cannot have clob litterals.

    If you have characters columns with less than 4000 characters, you do not need clob. If you have more than 4000 characters, you will probably need plsql to update your columns

Leave a comment

Your email address will not be published.