SQL & PL/SQL

I have been attempting to read an image file from the OS and store the contents in a BLOB column. I also need to do the reverse, ie. save an image file from a stored BLOB to the OS.

I have 2 procedures in place to read and write BLOBs. Here is the first to retrieve the BLOB back from the database to the operating system.

-- Write BLOB from DB to file
create or replace procedure write_blob is

l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;

BEGIN

-- Get LOB locator
select i_blob
into l_blob
from test
where id = 999;

-- Establish length of BLOB
l_blob_len := DBMS_LOB.getlength(l_blob);

-- Open the destination file.
l_file := UTL_FILE.fopen('NEW_DIR','image.jpg','w', 32767);

-- Read chunks of the BLOB and write them to the file until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;

-- Close the file.
UTL_FILE.fclose(l_file);

EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;

end write_blob;


And here is the procedure to read a BLOB from the operating system to the database.


create or replace procedure read_blob(filename in varchar2) as

l_blob Blob;
l_bfile Bfile;

Begin

insert into test (id, i_blob) values (999, empty_blob())
returning i_blob into l_blob;

l_bfile := bfilename ('NEW_DIR', filename);
dbms_lob.fileopen (l_bfile);
dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);

end read_blob;

When I run the procedures and load the returned image (.jpg) file back to a picture viewer application then it is unrecognisable.

Question1> Can anybody see why my either or both procedures are failing?

Question 2> Is there no easy way in Oracle 9i (rather than Oracle 10g) to allow the user to import a JPEG file into a BLOB and then export back out again? ie. binary in equals binary out?

Many thanks in advance... it's driving me mad.