Re: How to load BLOB,CLOB in Oracle 8.0.5

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 01 Jul 1999 12:21:39 GMT
Message-ID: <37855d28.55550707@newshost.us.oracle.com>

A copy of this was sent to bdumke <bdumke_at_bol.com> (if that email address didn't require changing) On Thu, 01 Jul 1999 13:56:35 +0200, you wrote:

>Hi
>
>my problem is how can I load a picture in a BLOB and a long plain text
>in a CLOB?
>I am using Oracle 8.0.5.
>I tried it with the sql*loader but that doesn't work.
>Thanks
>Bernd

dbms_lob.loadfromfile is pretty handy at doing that:

drop table demo;

create table demo

( id            int primary key,
  theBlob       blob

)
/

create or replace directory my_files as '/export/home/tkyte/public_html';

declare

        l_blob  blob;
        l_bfile bfile;
begin
        insert into demo values ( 1, empty_blob() ) 
        returning theBlob into l_blob;

        l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
        dbms_lob.fileopen( l_bfile );

        dbms_lob.loadfromfile( l_blob, l_bfile, 
                                                   dbms_lob.getlength( l_bfile )

);

        dbms_lob.fileclose( l_bfile );
end;
/

NOTE: 'MY_FILES' is supposed to be in uppercase and the above example will NOT work without it being so.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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 Thu Jul 01 1999 - 07:21:39 CDT