Re: Help: Inserting a file into a clob

In article <8h1j3k$3v3$1_at_nnrp1.deja.com>,   dwarakv_at_hotmail.com wrote:
> Hi,
>
> I have a table (lob_table) with two columns(id integer, lob_col
 clob).
> I need to insert files (text.htm, abc.txt) into this table. Can
 someone
> please help me with this?
>
> TIA,
> Dwarak
>
> P.S. My manager wants to store the files in the database. So, I guess,
> bfile is out of the question. Please cc to dwarakv_at_hotmail.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

replace blob with clob in the following and your ready to go. note: files must be on the database server for this to work. directory names are case sensitive -- when we pass them in a string -- make sure to uppercase them as i did.

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 );

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.