Re: Insertion and Retrieval of Pictures in Oracle 8

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 04 Jun 1998 13:57:15 GMT
Message-ID: <3577a66b.1023631@192.86.155.100>

A copy of this was sent to gordonch_at_Hawaii.Edu (Gordon K Chan) (if that email address didn't require changing) On 4 Jun 1998 01:54:00 GMT, you wrote:

>Hello,
>
>I have created a table that includes a RAW field so that I can insert some pictuers that need to be retrieved using SQL. I want to be able to use update to load these images into the table but am unsure of how I would do this. I would appreciate any help I could get. I have looked in some of the books we have but none seem to be able to show the steps that we would take to do this. Are there any sources I should look at or could someone explain to me how I would do this.
>
>Gordon

DON'T use a long raw, use a blob....

For example:

drop table blobs;

create table blobs

( id		varchar2(255),
  theBlob	blob

)
/

create or replace directory MY_FILES as 'c:\temp';

declare

	f_lob	bfile;
	b_lob	blob;
begin
	insert into blobs values ( 'MyGif', empty_blob() )
	return theBlob into main.b_lob;

	f_lob := bfilename( 'MY_FILES', 'aria.gif' );
	dbms_lob.loadfromfile( b_lob, f_lob, dbms_lob.getlength(f_lob) );
	commit;

end;
/

There, the file c:\temp\aria.gif is now loaded in the database. Assuming you want to retrieve it via the web for example, you could:

create or replace procedure get_blob( id in varchar2 ) is

	l_lob	blob;
	l_amt	number default 4096;
	l_off   number default 1;
	l_raw   raw(4096);
begin
	begin
		select theBlob into get_blob.l_lob 
	  	  from blobs 
	     where id = get_blob.id;
	exception
		when no_data_found then
			owa_util.status_line( 404, 'Not Found' );
			return;
	end;

	begin
		owa_util.mime_header( 'image/gif' );
		loop
			dbms_lob.read( l_lob, l_amt, l_off, l_raw );
			htp.prn( utl_raw.cast_to_varchar2(l_raw) );
			l_off := l_off+l_amt;
			l_amt := 4096;
		end loop;
	exception
		when no_data_found then NULL;
	end;

end;
/

That would return it to a browser....

Bear in mind that the character set for the PL/SQL cartridge must be the same as the character set of the database for this to work (we are dealing with RAWs here, can't let any NLS conversions happen).

also, this uses utl_raw which is shipped with the database since 7.1.6 (replication and gateways use it). If utl_raw is not installed in your database, simply goto your $ORACLE_HOME/rdbms/admin directory and look for the 2 files named "*raw*". There will be a spec and body. Use svrmgr to install them when connected as SYS or INTERNAL..  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jun 04 1998 - 08:57:15 CDT