Report example: displaying photographs

You can create a report that displays BLOB data (binary large objects). In this example, we wish to show employee photographs. The photographs are stored in the database as BLOBs.

The basic technique is to have the report call a form, and have the form display a table that contains the BLOB data (consisting in this case of gifs).

Step 1

Create a table:

create table imagetable(A number(3) , B BLOB);

Step 2

Create a simple form based on this table for uploading the data. Oracle 9iAS Portal will create a form with a field for uploading the data in this table.

Insert some records and some gif files. The gif files are the employee photographs.

Step 3.

Create a stored procedure under the SCOTT schema, as follows:

CREATE OR REPLACE procedure retrive_image(no number) as size1 integer;

blob1 BLOB;

tmpblob BLOB;

mimetype varchar2(200) := 'image/gif';

begin

select B into blob1 from imagetable where A = no ;

size1 := dbms_lob.getlength(blob1);

dbms_lob.createtemporary(tmpblob,true);

dbms_lob.copy(tmpblob,blob1,size1,1,1);

OWA_UTIL.MIME_HEADER('image/gif');

htp.p('Content-length: '|| size1);

owa_util.http_header_close;

wpg_docload.download_file(tmpblob);

dbms_lob.freetemporary(tmpblob);

end;

Step 4.

Grant permissions for the PORTAL_PUBLIC user.

Step 5.

Connect as scott/tiger user.

grant execute on retrive_image to PORTAL_PUBLIC ;

Step 6.

Create a report. Use the following query:

select A , ('<IMG SRC="' || owa_util.get_owa_service_path

|| 'SCOTT.retrive_image?no=' || A || '"

Align=Middle >') IMG1 from imagetable

Test Your Report

Run the report. You should see the Employee ID's along with their photographs.