Attach image logo in XML Puslisher Report with BLOB

There was a requirement to build a report having photograph on it.Instead of storing image in OA media and reading the image is there any other way we can achieve the requirement.

It is a report used by HR department to keep along with the personal/academic/experience records for each employee of the organization. This report was named as ‘Employee Photograph report’.

This requirement was actually to find out the way of converting BLOB (photographs are stored as BLOB in HR table) to CLOB and use that in a XML PUBLISHER report template.


This is how it was achieved step by step:
1. Write a BLOB TO CLOB converter db function as below and compile in the database.
CREATE OR REPLACE FUNCTION APPS getbase64( p_source BLOB )
RETURN CLOB
IS
v_result CLOB;
BEGIN
--dbms_lob.freetemporary(v_result);
DBMS_LOB.createtemporary(lob_loc = > v_result, CACHE =>False, dur => 0);
Wf_Mail_Util.EncodeBLOB ( p_source, v_result);
RETURN ( v_result );
END getbase64;

2.Build the report ( it can be a rdf report or XMl data template) and call the above BLOB to CLOB converter function from the report query as shown below:
In this case employee photographs were stored in PER_IMAGES table in IMAGE column.
SAMPLE REPORT QUERY
——————————–
SELECT pa.person_id
,pa.employee_number
,pa.full_name
,getbase64(PerImageEO.IMAGE) IMAGE1
FROM per_all_people_f pa
,PER_IMAGES PerImageEO
WHERE PerImageEO.PARENT_ID=pa.employee_number


For the front end we can prepare the template( .rtf file ) and incorporate the photograph tag as well and below is the illustration for the same:

THE TEMPLATE

Now the IMAGE field show above is the place where the Photo of the employee should come:
Below is the code need to put in the tag of the image field shown above:
<?if:IMAGE1!=''?>
<fo:<span class=”hiddenSpellError”>instream-foreign-object</span>content-type=”image/jpg”>IMAGE1</fo:instream-foreign-object>
<?end if?>
The ‘ IF’ shown above is to handle the employee records without photograph in the table. Otherwise the report will end up with error if there is an employee without photo in the database.
Place the rdf and rtf and register as usual like any other XML publisher report.
IMAGE