Re: Oracle8 CLOB with remote webserver question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 10 Mar 1999 18:51:13 GMT
Message-ID: <36edbd80.21451455@192.86.155.100>

A copy of this was sent to jamesf <uzada_at_my-dejanews.com> (if that email address didn't require changing) On Tue, 09 Mar 1999 22:39:11 GMT, you wrote:

>All --
>
>The scenario is that I have a storyserver install on machine A and an
>oracle database on machine B. The data passed around is components of
>the web pages, and the biggest headache is the body of the web page.
>The length of these pages is highly variant, a couple of K up to (so
>far a max of 31000 chars... although that is the exception more than
>the rule).3000 chars is the norm. Oracle suggests using the CLOB vs.
>the LONG because it is more accessible.
>
>The problem is the web-based editing tool which takes in the data from
>the database (no problem), but placing the modified data back into
>machine B from machine A seems problematic. I'm wondering if I'm
>missing something with the whole DBMS_LOB.LOADFROMFILE() as the only
>way of getting the data into the CLOB in the database.
>

No, dbms_lob.loadfromfile is not the only way to get it in. Its is probably the easiest though.

You can nfs mount or ftp the files to the server and use dbms_lob.loadfromfile as you described in the below snippet. that'll work.

Since the files are 31k or less, you can also do the following:

SQL> create table t1 ( x clob );

Table created.

SQL> 
SQL> 
SQL> create or replace procedure add_Text( p_string in long )
  2  as
  3          l_clob  clob;
  4  begin
  5          insert into t1 values ( empty_clob() ) returning x into l_clob;
  6  
  6          dbms_lob.write( l_clob, length(p_string), 1, p_string );

7 end;
8 /

Procedure created.

SQL> 
SQL> 
SQL> exec add_text( 'Hello World ' || rpad( '*', 31000, '*' ) );

PL/SQL procedure successfully completed.

SQL> 
SQL> column just_a_piece format a30
SQL> select dbms_lob.substr(x,20,1) just_a_piece, dbms_lob.getlength(x) from t1;

JUST_A_PIECE                   DBMS_LOB.GETLENGTH(X)
------------------------------ ---------------------
Hello World ********                           31012

that procedure, add_text, can be called by a client on the machine with the files to be loaded. You could write a small program to invoke that procedure, passing it the text. Alternatively, if the strings get to be >32k, you could write a small loader on the machine that loads the data in any fashion you want...

>i.e.
>
>

[snip]

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Mar 10 1999 - 12:51:13 CST