Oracle BI Publisher Blog

Good question on the forum asking how to insert a db stored blob image in the report output? It can be done relatively easily the first task is to get the blob out into your XML datasource.

Extract your BLOB

The XMLP data engine now(5.6.2) supports the extraction of blob images directly into the XML. Its pulled out and stored in a base64 format there is no limit on the size of the BLOB ie you can get more than 64K of data out in the encoded format. There are other methods to get the data out but it must be base64 encoded.

Reference your BLOB

In the template you now need to reference the image data. We can use an XSLFO expression to reference the image:

<fo:instream-foreign-object content-type="image/jpg">
 <xsl:value-of select="IMAGE_ELEMENT"/>
</fo:instream-foreign-object>

Notice the template needs to know the mime type of the image, in this case "image/jpg" The IMAGE_ELEMENT contains the base64 encoded image data. Template and sample data here.
So at runtime the image is brought into the output, et voila!

Please note: In this release the BLOB support is limited to Oracle databases.

Those of you that have used the data extraction engine will know that it can generate structured XML when executing a data template, with all the bells and whistles, event triggers, group filters, summary columns, etc. The engine can also accept a simple sql statement too including parameters. Passing say:

select JOB,
         ENAME,
         DEPTNO 
 from EMP

Will render the following XML.
<?xml version="1.0" encoding="UTF-8" ?>
<ROWSET>
 <ROW>
  <JOB>CLERK</JOB>
  <ENAME>SMITH</ENAME>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <JOB>SALESMAN</JOB>
  <ENAME>ALLEN</ENAME>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <JOB>SALESMAN</JOB>
  <ENAME>WARD</ENAME>
  <DEPTNO>30</DEPTNO>
 </ROW>
</ROWSET>
Thats all very nice but what if you wanted to extract the data in a structured format with out having to develop a data template. Well now you can, the latest release of the engine supports SQL XML. This sql format allows you to extract hierarchical XML from the Oracle database, the XMLP data engine can now support this too.
So the following statement:

SELECT XMLELEMENT("Department", XMLAttributes(deptno AS "deptno"),
                  XMLAgg(XMLElement("Employee", e.job||' '||e.ename))).getClobVal()
   AS "Dept_list"
   FROM emp e
   GROUP BY e.deptno

will generate the following XML:
<?xml version="1.0" encoding="UTF-8" ?>
<ROWSET>
 <ROW>
  <Dept_list>
   <Department deptno="10">
    <Employee>MANAGER CLARK</Employee>
    <Employee>PRESIDENT KING</Employee>
    <Employee>CLERK MILLER</Employee>
   </Department>
  </Dept_list>
  </ROW>
  <ROW>
   <Dept_list>
    <Department deptno="20">
     <Employee>CLERK SMITH</Employee>
     <Employee>ANALYST FORD</Employee>
     <Employee>CLERK ADAMS</Employee>
     <Employee>ANALYST SCOTT</Employee>
     <Employee>MANAGER JONES</Employee>
    </Department>
   </Dept_list>
  </ROW>
...
</ROWSET>

Notice, still rowset/row but there is now heirarchy in the sub elements ... no more regrouping for me in my template :o)
For the XMLP engine to sucessfully extract the data using the SQL XML format we need to append the .getClobVal() function to the end of the statement.
So, the world is your oyster when it comes to using SQL with the XMLP data engine .. so start shucking:o)