Monday, 31 May 2010

How to show BLOB type Column as image in APEX

1)Create table which stores the blob data
At first we had created a table with sequence and trigger. It is used for storing data.
CREATE TABLE PHOTOS
(
"PHOTO_ID" NUMBER NOT NULL ENABLE,
"PHOTO_BLOB" BLOB,
"MIME_TYPE" VARCHAR2(64 BYTE));

CREATE SEQUENCE PHOTO_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 1000000 MINVALUE 1;

create or replace
trigger photos_trg
before insert
on photos for each row
begin
select photo_seq.nextval into :new.photo_id from dual;
end;
commit;


2) Create pocedure which shows picture
We had created a procedure which can display our blobs data (as picture on a page) from our table.

create or replace
PROCEDURE my_image_display( p_image_id IN NUMBER)
AS

l_mime VARCHAR2 (255);
l_length NUMBER;
l_file_name VARCHAR2 (2000);
lob_loc BLOB;
BEGIN

SELECT mime_type, photo_blob, DBMS_LOB.getlength (photo_blob)
INTO l_mime, lob_loc, l_length
FROM photos
WHERE photo_id = p_image_id;


OWA_UTIL.mime_header (NVL (l_mime, 'application/octet'), FALSE);
HTP.p ('Content-length: ' || l_length);
OWA_UTIL.http_header_close;
WPG_DOCLOAD.download_file (lob_loc);

END my_image_display;


3) Create public synonym:
When we want to call my_image_display procedure then we have to be public it and also have to create synonym for it:

connect sys as sysdba

--grant all users the ability to execute this procedure
GRANT EXECUTE ON #user#.my_image_display TO PUBLIC;

--create synonym
CREATE PUBLIC SYNONYM my_image_display FOR #user#.my_image_display;

4) IN APEX:
When you want to display blob data as picture on apex page, you can do it on two way:
a) create new region on a page (Type: PL/SQL):

begin
for rec in (select *
from photos
)
loop

IF rec.mime_type is not null or rec.mime_type != '' THEN
htp.p( '<img src="my_image_display?p_image_id='||NVL(rec.photo_id,0)||'" height="'||100||'"/>' );
else
htp.p( 'No Image ');
END IF;
htp.p( '
');
end loop;
end;

b)or create new report on an apex page:

select photo_id, mime_type, '<img src="#OWNER#.my_image_display?p_image_id='|| NVL (photo_id, 0)|| '" height="'|| 100|| '" />' photo
from photos

5) how to upload images via apex
when you want to upload images into table, you can do the following:
A) create following items:
P10_MESSAGE (Type: display as text)
P10_PHOTO (Type: File Browse)
a button

B) Create process for button:

BEGIN

:P10_MESSAGE :=
save_data.upload_images ( in_name =>:P10_PHOTO);


END;


C) create package:

create or replace
PACKAGE SAVE_DATA AS
FUNCTION upload_images ( in_name IN VARCHAR2) RETURN VARCHAR2;
END SAVE_DATA;
/

create or replace
PACKAGE BODY SAVE_DATA AS
FUNCTION upload_images ( in_name IN VARCHAR2) RETURN VARCHAR2
IS
p_image_id VARCHAR2(256);
image_size number;
image_type varchar2(256);
out_message VARCHAR2(4000);
v_blob blob;
BEGIN

-- We want to keep the image in our own table.
-- Copy the image from WWV_FLOW_FILES to our own PHOTOS table.
--

-- check file size
select doc_size into image_size from wwv_flow_files where name = in_name;
select mime_type into image_type from wwv_flow_files where name = in_name;
if image_size <= 204800 AND upper(image_type) = upper('image/jpeg') then


select blob_content into v_blob from wwv_flow_files where name = in_name;

INSERT INTO photos (photo_blob, mime_type)
VALUES (v_blob,image_type);

else
out_message := 'Note: Max image size is 200k.<br />
And only .jpg files are allowed
<br />
<a href="http://www.shrinkpictures.com" target="_blank">Resize your images here </a>';
end if;

-- delete it from the WWV_FLOW_FILES table. That way we can keep
delete from wwv_flow_files where name = in_name;
commit;

RETURN out_message;

EXCEPTION
WHEN OTHERS THEN
RETURN 'Error';
END upload_images;


END SAVE_DATA;

11 comments:

Ergem Peker said...

thank you for preparing this attentive post. I searched lots of posts but this is the only one prepared such a care and worked for me as well.

Regards,
ergemp

Zsuzsi Vadasz said...

Your are welcome

Stirl said...

Yes helpful post thanks!

Theodoros Emmanuel said...

very useful post, thanks.

Just wondering, do the images show up with the same size or can this be set.

Teo

Theodoros Emmanuel said...

just noticed it is set from the source.

great, thanks!

Theodoros Emmanuel said...
This comment has been removed by the author.
rappi said...

Hi Sir,
Can you please tell me how to insert the image above 200k size?

Thanks & Regards
Ahamed Rafeeque Cherkala

Zsuzsi Vadasz said...

Hi rappi,

You should change the following code in SAVE_DATA package:
if image_size <= 204800 AND upper(image_type) = upper('image/jpeg') then

Regards,
Zsuzsi

Festus Christopher said...

Please i am new i need a step by step process for using this code in my program.

Festus Christopher said...

Please i need help on how to apply this code to my application i am a junior programer. please help.

Leo Nexx said...

It is not necessary to grant execute on procedure my_image_display to public, and create synonym, but enough grant EXECUTE on #OWNER#.my_img_display to apex_public_user