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.
Comments (5)
Got anything for those of us generating XML straight out of the DB?
Oracle restricts nodes to 64KB so large images need to be broken up into separate segments.
Posted by Michael Friedman | May 5, 2006 4:14 AM
Posted on May 5, 2006 04:14
Hi Michael
The XMLP data extraction engine does not have such a limitation. You are going to generate biiiiig XML thou :o)
Tim
Posted by Tim Dexter | May 5, 2006 6:20 PM
Posted on May 5, 2006 18:20
Tim,
We generate XML for multiple consumers.
We therefore strongly prefer to generate our XML in the DB and make it available in XML_TYPE views.
We use a pipeline function to break up our blobs into segments - works like a charm.
For some purposes (ie. EDI between systems in different locations) we also use Oracle's compression functions for data transmission efficiency.
I don't think there is anything wrong with XMLP (although I admit I haven't looked at it) but I doubt I can use it as a source for an XML_TYPE view, for example. Or make its output easily available to HTMLDB.
It's getting a bit frustrating the number of different groups at Oracle who seem to be reinventing slightly incompatible wheels.
Posted by Michael Friedman | May 7, 2006 4:00 AM
Posted on May 7, 2006 04:00
Tim,
I am on xml pub 5.6.3. i am trying to use blobs in my report we well, but hitting against:
Calling XDO Data Engine...
[062109_082536910][][EXCEPTION] java.sql.SQLException: Stream has already been closed
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBDataSetImpl.getStreamItem(DBDataSetImpl.java:1598)
at oracle.jdbc.driver.OracleStatement.getBytesInternal(OracleStatement.java:3555)
at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.java:3808)
at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:460)
any ideas on what i am missing?
Posted by sheena | June 20, 2009 5:32 PM
Posted on June 20, 2009 17:32
Hello everybody,
I had the same problem, this blog was my solution, but i have some additional issues:
1. I´m using BI Publisher of BI standard one product, version 10.1.3.2.1.
2. It`s true, the xml data engine supports the blob extraction, just verify your xml to see if the blob field has information in it (I say this because in some other blogs people said they had to build a PL/SQL function to transform the blob information, this was not true for my case).
3. It´s true, to reference the blob field in your report (in the rtf document) you have to edit the blob field and use the sintaxis mentioned in "Reference your blob", but for my case worked this way:
Where image_element is your blob field´s name. Note the additional ".//" before the field´s name.
4. I had a index out of range error, because some records had null for the blob field in some records, I had to populate all records with a default image (my field is null enable in the table).
5. I tried with different images types, finally it worked with jpg. Note that if you work with different image types you have to modiffy the sentence "image/jpg" in the point 3 sintaxis.
6. I experienced some problems with the image size (the error said it couldn´t render the image in the space of the report or something like that), you have to ensure enogh space to display the image in your report (specially if you are displaying the image in a cell of a microsoft word´s table, I suggest you to try before with "Free Form" for your report format in the table wizard and small images).
I hope this could be helpful,
Carlos Hernandez,
OCP DBA 10g, OCA Developer 10g and Fussion Middleware Presales Champion
Posted by Carlos Hernandez | September 30, 2009 7:29 AM
Posted on September 30, 2009 07:29