In this post you can see how to configure APEX to use BI Publisher
2) Create report query in APEX
Shared Components -> Report Queries -> Create
Report Query Name: first_query
Output Format: PDF
Source Queries:
select "PHOTOS"."PHOTO_ID" as "PHOTO_ID",
"PHOTOS"."MIME_TYPE" as "MIME_TYPE" ,
'http://YOUR_WEBSERVER/DAD/USER.my_image_display?p_image_id='||PHOTO_ID as FILEPATHVALUE
from "PHOTOS" "PHOTOS"
YOUR_WEBSERVER => the server's IP or canonical name
DAD => Database Access Descriptor
USER => schema name where my_images_display procedure is located
my_image_display => see the my_image_display procedure
At now we can see the xml data:
Shared Components -> Report Queries ->Click on first_query (which was created by us)
-> Source Queries -> click on Download button
Xml data which was generated by APEX:
<?xml version="1.0" encoding="UTF-8"?>
<DATA>
<ROWSET1>
<ROWSET1_ROW>
<PHOTO_ID>1</PHOTO_ID>
<MIME_TYPE>image/jpeg</MIME_TYPE>
<FILEPATHVALUE>http://YOUR_WEBSERVER/DAD/USER.my_image_display?p_image_id=1</FILEPATHVALUE>
</ROWSET1_ROW>
</ROWSET1>
<ROWSET2>
<ROWSET2_ROW>
<SYSDATE>01-JUN-10</SYSDATE>
</ROWSET2_ROW>
</ROWSET2>
</DATA>
3) Create custom report layout via Oracle BI Publisher Desktop and MS Word
At first you have to load sample xml data:
MS Mord -> Oracle BI Publisher -> Data -> Load Sample XML Data
Choose the sample xml data file which was generated by APEX
You can create table in MS Word similar the following picture. Fill the static text, for example page header or table column header. When you want to display PHOTO_ID element from the first query, then you the following: <?for-each: ROWSET1/ ROWSET1_ROW?> <?PHOTO_ID?> <?end for-each?>
When you want to see your layout in pdf, then Oracle BI Publisher -> Preview Template -> PDF
MS Word -> Insert -> Picture -> Clip Art -> choose a picture -> when you can see the choosed picture on page then double click on picture -> choose "Web" tab -> write the following: url:{FILEPATHVALUE}
When you see your layout in pdf, then you can see your blob data as image in pdf
4) Upload your custom report layout
Upload your layout to APEX
