Thomas Kyte wrote:
>
> 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
An exquisite code example! Bravo, Thomas. I am still a victim of habit
and I
haven't used 'return into' clause. I used a separate transaction to
insert
an empty_blob() and then select for update to retrieve it. Thanks for
reminding
me to program properly.
--
Mladen Gogala
Received on Mon May 31 1999 - 00:23:26 CDT
