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

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 on May 04, 2006 at 10:14 PM MDT #

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 on May 05, 2006 at 12:20 PM MDT #

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 on May 06, 2006 at 10:00 PM MDT #

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 on June 20, 2009 at 11:32 AM MDT #

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 on September 30, 2009 at 01:29 AM MDT #

Hello Everybody, I am trying to retrieve the blob image from DB and using peoplecode to pass the data to the xml template: SQLExec("SELECT PHOTO FROM PS_PHOTO WHERE EMPLID='0030'", &Empl_Photo); 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:

Posted by Anisha on December 28, 2009 at 10:34 PM MST #

That’s Too nice, when it comes in india hope it can make a Rocking place for youngster.. hope which come true.

Posted by Night Sweat on February 08, 2010 at 09:40 PM MST #

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! regards, Sarfraz

Posted by Sarfraz R Ramay on February 17, 2010 at 11:15 PM MST #

Sorry the article was by Tim Dexter, :).

Posted by Sarfraz on February 17, 2010 at 11:43 PM MST #

can someone please help......how to 'get the blob out into your XML datasource.'......please help since its urgent

Posted by Sarfraz R Ramay on February 18, 2010 at 02:15 PM MST #

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! :)

Posted by Sarfraz R Ramay on February 19, 2010 at 12:20 AM MST #

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 OR 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

Posted by Rekha on March 08, 2010 at 05:41 PM MST #

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)

Posted by Krishna on June 15, 2010 at 10:26 PM MDT #

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.

Posted by Emilee Getman on August 30, 2010 at 03:00 PM MDT #

Great blog, it's keeping me from studying I'll be keeping an eye out for new posts.

Posted by Will Sanderson on October 23, 2010 at 03:51 AM MDT #

You have another link to the samples files?

Posted by Leonardo Macedo Candido on July 26, 2011 at 07:07 AM MDT #

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?

Cheers

Posted by Scott Wesley on July 27, 2011 at 06:46 PM MDT #

Tim,

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]]>
</sqlStatement>

ORA-00932: inconsistent datatypes: expected - got BLOB

I'm current using stand alone version of BI Publisher 10.1.3.4.0.

Please guide.

Thanks.

Posted by guest on October 04, 2011 at 04:11 AM MDT #

Tim,

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 10.1.3.4.0.

Please guide.

Thanks.

Posted by guest on October 04, 2011 at 04:26 AM MDT #

@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

Posted by guest on October 04, 2011 at 05:53 AM MDT #

@Guest - Can you run the query outside of BIP and it run OK? If not, this might help
http://devsac.blogspot.com/2009/07/ora-00932-inconsistent-datatypes.html

Regards

Tim

Posted by Tim on October 04, 2011 at 05:58 AM MDT #

Tim,

Your blog helped me work my BIP report.

Thanks.

Posted by guest on October 04, 2011 at 09:29 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today