An Oracle blog about BI Publisher

  • RTF
    May 5, 2006

Inserting BLOBs into your report

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"/>

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.

Join the discussion

Comments ( 26 )
  • Michael Friedman Friday, May 5, 2006
    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.
  • Tim Dexter Friday, May 5, 2006
    Hi Michael
    The XMLP data extraction engine does not have such a limitation. You are going to generate biiiiig XML thou :o)
  • Michael Friedman Sunday, May 7, 2006
    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.
  • sheena Saturday, June 20, 2009
    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?
  • Carlos Hernandez Wednesday, September 30, 2009
    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
    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
  • Anisha Tuesday, December 29, 2009
    Hello Everybody,
    I am trying to retrieve the blob image from DB and using peoplecode to pass the data to the xml template:
    Local XmlNode &nodeCurr = &nodeRoot.AddElement("AD_COMPOSITE");
    &nodeCurr.NodeValue = &Empl_Photo;
    In the rtf template, i am using the same sample template:

    But couldn't get the report, the following error is obtained:
    Eoor occurred during the process of generating the output file from template file, xml data file, and translation XLIFF file>
  • Night Sweat Tuesday, February 9, 2010
    That’s Too nice, when it comes in india hope it can make a Rocking place for youngster.. hope which come true.
  • Sarfraz R Ramay Thursday, February 18, 2010
    hey nice article i must say.....my question is that how did u get to the xml that you uploaded in the sample. I am working on rdf reports oracle reports builder and i have converted the rdf to xml using the rwconverter utility. my rdf also had a BLOB column and it has given me one tough time. without the blob column the report runs just fine.i will be obliged if you can help me on urgent basis as tom is my deadline and i have just started and this is my first assignment and to tell you the truth i have already wasted a lot of time on it.....if you can give me your email id Mr. Carlos Hernand i can send you the rdf and xml and rtf files......
    Please help!
  • Sarfraz Thursday, February 18, 2010
    Sorry the article was by Tim Dexter, :).
  • Sarfraz R Ramay Thursday, February 18, 2010
    can someone please help......how to 'get the blob out into your XML datasource.'......please help since its urgent
  • Sarfraz R Ramay Friday, February 19, 2010
    does anyone read these articles anymore or am i just too late to get into this technology! i have extracted the blob into as base64, now problem is that i have to convert it into char to load it into the template and varchar2 is limited to 4000 bytes and i have tried dbms_load.substr() function so please anything useful would be very helpful! :)
  • Rekha Tuesday, March 9, 2010
    Plz Help,
    Hi I am developing one template where I have to print a BLOB on every page at the end(say as a footer).I had 2 ways to approach
    1)Printing the BLOB as footer.
    Issue:- blob prints normally as a field but when I put the same code

    in footer it did not work, is it sensible to put BLOB in footer
    2)I can print it at end of every page in a table along with some other text too.
    Issue:- plz tell me how to print the table at end of each page without disturbing the other table that has been created to print a data of many rows.
    Kindly mail me or reply on this post.
    email: rekha.pujeri@hcl.in
  • Krishna Wednesday, June 16, 2010
    Hi Tim,
    I am on 5.6.3. and developing a report that shows a list of defects and its pictures. We could able to show pictures in this report using:

    This works fine when I am running the report with 3 - 4 pictures.
    When I am running the same report with more pictures, for ex: 10 pictures, its giving below error message.
    Can you please help about this.?
    Thank You.
    java.lang.StringIndexOutOfBoundsException: String index out of range: -1

    at java.lang.String.charAt(String.java:687)

    at oracle.apps.xdo.common.encoding.Base64Util.decode(Base64Util.java:54)

    at oracle.apps.xdo.template.fo.elements.FOInstreamForeignObject.end(FOInstreamForeignObject.java:71)

    at oracle.apps.xdo.template.fo.FOHandler.endElement(FOHandler.java:455)

    at oracle.apps.xdo.common.xml.XSLTHandler$EEEntry.sendEvent(XSLTHandler.java:594)

    at oracle.apps.xdo.common.xml.XSLTMerger.startElement(XSLTMerger.java:51)

    at oracle.xml.parser.v2.XMLContentHandler.startElement(XMLContentHandler.java:181)

    at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1288)

    at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:336)

    at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:303)

    at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:206)

    at oracle.apps.xdo.template.fo.FOProcessingEngine.process(FOProcessingEngine.java:320)

    at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:1051)

    at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5936)

    at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3459)

    at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3548)

    at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:296)

    at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:173)
  • Emilee Getman Monday, August 30, 2010
    What a write!! Very informative and easy to understand. Looking for more such posts!! Do you have a myspace or a facebook?
    I recommended it on digg. The only thing that it's missing is a bit of speed, the pictures are appearing slowly. Anyway thank you for this information.
  • Will Sanderson Saturday, October 23, 2010
    Great blog, it's keeping me from studying I'll be keeping an eye out for new posts.
  • Leonardo Macedo Candido Tuesday, July 26, 2011

    You have another link to the samples files?

  • Scott Wesley Thursday, July 28, 2011

    This may be crossing borders of expertise, but I was using this functionality quite happily during development, but found issues when my images went over a certain size.

    It seems when generating BI Publisher reports from Oracle Apex we encounter a 32k limit on the xml data file - is this something you're aware of, or could lead me in the right direction?


  • guest Tuesday, October 4, 2011


    I need to display a BLOB signature filed(stored in Oracle DB) on an existing data template report. The select statement with BLOB field gives me error:

    <sqlStatement name="Q1">

    <![CDATA[SELECT id ID, last_name LASTNAME, first_name FIRSTNAME, signature SIGN

    FROM ap_admin_info

    WHERE id=:Batch_ID

    GROUP BY id]]>


    ORA-00932: inconsistent datatypes: expected - got BLOB

    I'm current using stand alone version of BI Publisher

    Please guide.


  • guest Tuesday, October 4, 2011


    I need to display a BLOB signature filed(stored in Oracle DB) on an existing data template report. The select statement with BLOB field gives me error:

    ORA-00932: inconsistent datatypes: expected - got BLOB

    I'm current using stand alone version of BI Publisher

    Please guide.


  • guest Tuesday, October 4, 2011

    @Scott - its a limitation on the APEX side. They are looking to increase it in a future release. Probably worth posting your question to their forum on oracle.com.


  • Tim Tuesday, October 4, 2011

    @Guest - Can you run the query outside of BIP and it run OK? If not, this might help




  • guest Tuesday, October 4, 2011


    Your blog helped me work my BIP report.


  • abhishek dabas Saturday, May 16, 2015


    This become night mare for me, as I don't found any post where I can show blob data that holds html content into my report.

    I am having problem showing BLOB data into my report. My blob data contains Html data. sample :





    <p style="color:rgb(0, 0, 0);font-family:Times New Roman;font-size:medium;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;">

    Public Meeting at Northerly Island





    This is a notebook topic entry in my ADMUSER.WBSMEMO table. How to show this data as a normal text in the report template.

    I am using RTF template.

    BI Publisher Version : Oracle BI Publisher

    appreciate your help on this.


    Abhishek Dabas

  • Tim Monday, May 18, 2015


    Check this


    If you can move to 11g, there is native support for HTML formatted strings.


  • Vijay Wednesday, March 1, 2017

    Hi Tim,

    Is it possible to embed the image as an object itself(attachment) instead of inline content?


  • guest Wednesday, March 15, 2017

    How to add a condition that if image is there then show the image else any text coming from xml file and it need s to work in HTML preview. The following code works in pdf but not in HTML preview.


    <fo:instream-foreign-object content-type="image/png" xdofo:alt="An Image" >

    <xsl:value-of select="/DATA_DS/REQUISITION_DETAILS/TEAM_MEMBER_HIRING_MANAGER/HIRINGMANAGER_IMAGE"/></fo:instream-foreign-object>





Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.