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

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
2 dbms_lob.open(:x,dbms_lob.lob_readonly);
3 dbms_lob.read(:x,:n,7 /*position*/,:c);
4 dbms_lob.close(:x);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------
World

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

3 thoughts on “clob hello world”

  1. nyarrow

    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. Laurent Schneider Post author

    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

Comments are closed.