Re: Should I use LOBs here? HOW???

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 05 Aug 1998 13:29:49 GMT
Message-ID: <35ca5bfd.1857360@192.86.155.100>

A copy of this was sent to "a" <a_at_x.y>
(if that email address didn't require changing) On Wed, 5 Aug 1998 18:25:25 +1000, you wrote:

>Hi,
>
>We have a reasonably simple requirement - to store a couple of pages of text
>per row in a table. Up til now we used VARCHAR2(4000) columns, but this is
>no longer sufficient.
>
>To store more, it seems I would have to use LONG or CLOB. Oracle doco says
>that, in V8, LONGs are provided purely for backward compatibility, and we
>should use LOBs instead. However, when I tried using CLOBs in our webserver
>app, I'm having all sorts of troubles with using the datatype in webserver
>procedures, and normal functions (eg UPPER).
>

What problems are you having? Here is an example that shows how flexible dealing with LOBs can be in pl/sql. First We create a table:

SQL> create table clobs

  2  ( id            varchar2(255),
  3    the_lob       clob

  4 )
  5 /

Table created.

Then, to get some big data in there, we create a directory:

SQL> create or replace directory MY_FILES as '/export/home/tkyte/public_html';

Directory created.

Next, we put a file into the CLOB:

SQL> declare

  2      l_bfile   bfile;
  3      l_clob    clob;
  4  begin
  5      insert into clobs values ( 'MyDoc', empty_clob() )
  6      return the_lob into l_clob;
  7  
  7      l_bfile := bfilename( 'MY_FILES', 'test.html' );
  8      dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength(l_bfile) );
  9      commit;
 10      dbms_output.put_line( dbms_lob.getlength(l_bfile) ||
 11                           ' Bytes of text loaded' );

 12 end;
 13 /
67956 Bytes of text loaded

So, thats a little over 64k of text (at this point, if we had this in a LONG, dealing with it in pl/sql would be very hard. We could not insert or update this LONG using pl/sql we could only READ it using DBMS_SQL and piecewise fetches -- no static sql)....

Lastly, we read it back out and work with it:

SQL> create or replace procedure get_text( p_id in varchar2 )   2 as

  3      l_lob   clob;
  4      l_amt   number default 250;
  5      l_off   number default 1;
  6      l_txt   varchar2(255);
  7  begin
  8      select the_lob into l_lob
  9          from clobs
 10       where id  = p_id;
 11  
 11      begin
 12          loop
 13              dbms_lob.read( l_lob, l_amt, l_off, l_txt );
 14              dbms_output.put_line( upper(l_txt) );
 15              l_off := l_off+l_amt;
 16              l_amt := 250;
 17          end loop;
 18      exception
 19          when no_data_found then
 20              NULL;
 21      end;

 22 end;
 23 /

That procedure, run in sqplus with SET SERVEROUTPUT ON SIZE 1000000, will read the entire file out of the lob, uppercase it and dump it to the screen. We can search on it in the database using dbms_lob.instr, dbms_lob.substr, dbms_lob.compare and so on...

>So, should I persevere with LOBs? Am I merely doing it wrong? Or should I
>use LONGs anyway? Or, should I pursue a different method - eg splitting the
>text into multiple VARCHAR chunks for storage and gluing them back together
>for retrieval?
>
>Any advice will be appreciated.
>
>Thanx
>
>-ak
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Aug 05 1998 - 08:29:49 CDT