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

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>