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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGYBaQFUKQbH9t-SbHXbN4bYK6C2ErPC2agEUdXzZon_3kZPdgqdceFil0MtJ_u-svs0-vgVRndffCLGC0Zu-cVy7i7Z9TFY7-JR202_8Vs5Y4lsL6s6H69pwoNtBxGhlofMKFY3dz5QeS/s200/01.png)
Choose the sample xml data file which was generated by APEX
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSjOwwZzdibKMn1I7sYjMS-oKNxyA6PNRyKWKZYSLO2g0mcNBFnaLAgmpskvoiuw8mwRGhC9nnTW0gglC_gTYH_6B6gKsNNZbuz5toEZoZxx-8FC44KhgfidwsKCm3i_47WXvl1kKg0cbE/s200/02.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge028MaK_ZTp0NQxO97jUWoOwwXdrIf6xieG2PdXvc755NKqknBEpVGqU43iTYGwqIGrVwdhNwj66s_QDcuu-YyeSDaHkP09vyaKRfJEJwYVLmpdVk-c29Z-2kR2Ya31I2nY3EWmBczM8f/s200/03.png)
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?>
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgByYaYGim2VMWaoPKY-IiY9prUz8f_h78AafLHTmP3BuzJac8sC6Ly2KxAHEjRLXvGrzaEsStmjXu4bcxDNNOhSTlXGgV3dTlmlFHZT2BCeMUuJ6GYGrmhci0yfvadxdVnoGEI-byW7azk/s200/04.png)
When you want to see your layout in pdf, then Oracle BI Publisher -> Preview Template -> PDF
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcaKupBcj0oGSzTepy9PFqVgm1poLqUELkI3Q2V5W201G8kaxY0pfxXZJK_rvujxpuc9KqMrVxlHsNPmax-0KhYBV-rdAA_wEqtd9Xk4iygWCfzMpyP4Ih4jTKz0SKTEpE0Lgn5b4kPb-Y/s200/05.png)
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}
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfshaXhlL-gk6gg8Q1iKA3Y3_pdvI4NwSwLdZVHGX6RSSZHw9bhqrCh9_iHQo9V_TR57lKI_9bLedBbk21AdWUCaTB21BV9K3thha3tpAhyphenhyphenI2uzI9tbzWA6vaqW9-dJkxIFpgJFLhHCAa_/s200/06.png)
When you see your layout in pdf, then you can see your blob data as image in pdf
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvSfQaWFguWq1ZP1oUuYYPLzQINcWzpmzKhJqxd0shC9czs_xNcUpffW1sqQ4PTR7zw1tBwYNMCRoganQ5c2YajIuaAOuDZhKCr2r4LLjIwqtmqAoW-nxqRsmPBx1ObH294pmX6tn9cMOj/s200/07.png)
4) Upload your custom report layout
Upload your layout to APEX