Re: LOB help (8i)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 22 May 1999 15:51:36 GMT
Message-ID: <3749d24e.10032626@newshost.us.oracle.com>

A copy of this was sent to shmulik_at_ix.netcom.com (Shmuel Cohen) (if that email address didn't require changing) On Sat, 22 May 1999 12:42:00 GMT, you wrote:

>Can someone show me a sample bit of Sql and pl/sql code to INSERT NEW
>LOB records into a database? All the samples show how to get stuff
>out, but not put it in (records > 32K).
>

You have to deal with 32k chunks of data but since dbms_lob supports piecewise read/write this is pretty easy. Here is an example that stuffs 1meg of data into the lob using dbms_lob.write to append 10,000 bytes at the end of a lob:

SQL> create table lobtbl ( x CLOB );

Table created.

SQL>
SQL> declare

  2      l_lob    clob;
  3      l_data    varchar2(10000);
  4  begin
  5      insert into lobtbl values ( empty_clob() )
  6      return x into l_lob;
  7  
  7      l_data := rpad( 'a', 10000, 'a' );
  8      for i in 1 .. 100 loop
  9          dbms_lob.write( l_lob, length( l_data ),
 10                          dbms_lob.getlength(l_lob)+1, l_data );
 11      end loop;
 12  
 12      commit;

 13 end;
 14 /

PL/SQL procedure successfully completed.

SQL>
SQL> select dbms_lob.getlength(x) from lobtbl;

DBMS_LOB.GETLENGTH(X)

---

              1000000

>For example, if I have a database table defined as:
>
> create table test (
> MyLOB CLOB
> );
>
>Let's say I want to insert a piece of text that is 50,000 chars in
>length, how do I insert this? How do you initialize (or do you need
>to) the lob instance and insert large files? Everything works great
>as varchar2s of less than 4k bytes, but I need to add large
>(megabyte/gigabit) size text.
>
>create or replace procedure add_text (
> ???
>)
>
>Thanks

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 22 1999 - 10:51:36 CDT