Re: HOW TO - load images into new Oracle 8 datatype ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/17
Message-ID: <349b1ffa.5213346@inet16>#1/1

On 17 Dec 97 04:48:02 GMT, Stephen[dot]Meredith_at_jcu.edu.au (Stephen Meredith) wrote:

>Hi - has anyone tried to load JPG file(s) using the new
>BLOB supported in Oracle 8 ?
>
>I would imagine one uses SqlLoader but of course my (old) manual
>doesn't say anything about it. A simple example would be greatly
>appreciated.
>
>It would be even better if someone knows how to write a procedure
>of some kind to load 'all the JPGs in a given directory' (in Unix)
>as there are some thousands to be done.
>
>______________________________________________________________________
> Stephen Meredith Software Engineer
>TO REPLY CHANGE [DOT] TO '.' Computer Centre James Cook University
> _--_|\___Townsville Queensland 4811 AUSTRALIA
> / \ Telephone: +61 77 815913
>(A Canadian escaped \_.--._/ Fax: +61 77 815230
>from the snow and ice) v http://www.jcu.edu.au/~ccstm
>______________________________________________________________________

Here is one way to do it. I'll even show you how to display said blob in a web browser using the standard OWA toolkit if you are interested. That particular feature depends on a package, utl_raw, being installed. utl_raw is part of the standard distribution but typically isn't install if you don't have the procedural gateway. It install utl_raw, simply look for "*raw*" in your $ORACLE_HOME/rdbms/admin directory and run the package spec and body using svrmgrl when connected as internal or sys.

Below is the example:

drop table blobs;

create table blobs

( id	varchar2(255),
  b_lob	blob

)
/

REM A directory is a new database object in Oracle8. You can grant 'READ' REM on directories to people. You also need to grant 'create directory' if REM the user creating the directory is not a DBA. REM I will be loading files from my c:\temp directory....

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

REM this code snippet shows how to read a file from the file system REM into the database. We will use a BFILE (binary file type) and a blob. REM we must first create the blob in the database as an empty blob and then REM load that blob from a file. We will use the new 'returning COLUMN into REM HOST_VAR syntax to do this. This loads the gif file 'aria.gif' into the REM database....

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

REM now here is a routine that can be called from the webserver to REM retreive the image we just loaded. It will piecewise read the REM entire blob 4k at a time (I just picked 4k, no real reason, it REM could have been more, could have been less) and write it back REM to the browser. It uses utl_raw so the RAW data doesn't get REM turned into HEX. You will just want to make *sure* that the NLS REM setting in your OWA.CFG file (or wrb.app) is the SAME as the characterset REM in your database. for example, if your OWA.CFG has usascii7 as the REM character set for your DCD but the database is WE8ISO8859P1, then the REM NLS stuff will strip the high bits off, damaging the binary. If both REM are the same, NO conversion takes place.

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

As for how to load a directory full of existing files, I would create a script to write a sqlplus script. Lets say you take the anonymous block of code from above and made it a procedure that took

create procedure load_image( the_id in varchar2, the_filename in varchar2 ) as
...

You could (in unix anyway)

$ ls *.jpg | sed 's/^.*$/exec load_image('"'"'&'"'"','"'"'&'"'"')/' > load.sql

and then just run load.sql in sqlplus....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

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 Wed Dec 17 1997 - 00:00:00 CST