Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

error displaying a jpg file loaded into a table with blob field

623970Jul 24 2009 — edited Aug 3 2009
This may not be the correct forum for this question, but if it isn't could someone direct me to the correct one.

I have created a table with a blob field in which I have loaded a jpg image. This appeared to work correctly, but when I try to display the image in internet explorer it comes back and tells me that it doesn't recognize the file type. Enclosed is the table create, load, and display pl/sql code. Can anyone tell me what I am doing wrong. Thanks. For the mime/header I used owa_util.mime_header('images/jpg') because my image is a jpg file.

The database is 10g

-- Create table

create table PHOTOS
(
IMAGEID NUMBER(10),
IMAGE BLOB,
IMAGE_NAME VARCHAR2(50)
)

load image

CREATE OR REPLACE PROCEDURE load_file ( p_id number, p_photo_name in varchar2) IS

src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('SNAPUNCH', p_photo_name);
-- insert a NULL record to lock
INSERT INTO photos (imageid, image_name, image)
VALUES (p_id , p_photo_name, EMPTY_BLOB())
RETURNING image INTO dst_file;
-- lock record
SELECT image
INTO dst_file
FROM photos
WHERE imageid = p_id AND image_name = p_photo_name
FOR UPDATE;
-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
-- update the blob field
UPDATE photos
SET image = dst_file
WHERE imageid = p_id
AND image_name = p_photo_name;
-- close file
dbms_lob.fileclose(src_file);
END load_file;

display image

PROCEDURE display_image(p_id NUMBER) IS
Photo BLOB;
v_amt NUMBER DEFAULT 4096;
v_off NUMBER DEFAULT 1;
v_raw RAW(4096);
BEGIN

-- Get the blob image
SELECT image
INTO Photo
FROM PHOTOS
WHERE IMAGEID = p_id;

owa_util.mime_header('images/jpg');
BEGIN
LOOP
-- Read the BLOB
dbms_lob.READ(Photo, v_amt, v_off, v_raw);
-- Display image
htp.prn(utl_raw.cast_to_varchar2(v_raw));
v_off := v_off + v_amt;
v_amt := 4096;
END LOOP;
dbms_lob.CLOSE(Photo);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

END;

The url I enter is: http://webdev:7777/tisinfo/tis.tiss0011.Display_Image?p_id=1
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 31 2009
Added on Jul 24 2009
7 comments
1,237 views