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

My webserver application takes input from a textarea and stores it in that column. My problems were in compiling the package that does this.

This package (P1) calls another package (P2) when a button is pressed. P2 has another button that returns to P1, restoring all of P1's screen values. To do this, when P1's button is pressed, all of P1's screen values are stored in a PL/SQL table of clob and that table is passed to P2. P2 then stores each element of the table as a hidden field so the data is not lost. Eventually, when P2's button is pressed, P1 is called, passed all the stored values, and P1 redisplays the data.

I get compilation errors when trying to store the array elements as formHiddens. LONGs appeared to work fine here. However, I will have a close look at your code and try to educate myself on how to use them effectively! (Any more tips would be greatly appreciated!)

Thanks for the reply.

-ak

Thomas Kyte wrote in message <35ca5bfd.1857360_at_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 - 17:15:00 CDT