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;
