Re: Storing Images in Blobs

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 14 May 1998 18:11:08 GMT
Message-ID: <355d334b.15363521@192.86.155.100>

A copy of this was sent to markjo_at_cwcinc.com (if that email address didn't require changing) On Thu, 14 May 1998 16:25:51 GMT, you wrote:

>I need to store drawing files and images (.dxf, jpg, etc.) in a BLOB field in
>an Oracle 8 database. I am new to Oracle and have not had to deal with BLOBs
>before. Can anyone give me an example of loading a file into a BLOB field
>and then extracting it to a file using PLSQL? Is there a good book that
>would explain how to do this?
>
>Any help would be greatly appreciated.
>Thanks in advance.
>
>Mark
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading

Here is an example that loads a blob from a file and returns it over the web (pl/sql cannot do binary file WRITES currently so pl/sql cannot save a blob to a file)

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..

drop table blobs;

create table blobs

( id	varchar2(255),
  b_lob	blob

)
/

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

<<main>>
declare

	f_lob	bfile;
	b_lob	blob;
begin
	insert into blobs values ( 'MyGif', empty_blob() )
	return b_lob 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;
/

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 b_lob 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' );
	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;
/  

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 May 14 1998 - 13:11:08 CDT