Believe in yourself, and remember that anything is possible. Believe in what makes you feel good. Believe in what makes you happy. Believe in the dreams you have always wanted to come true.

BLOB Images in XML Publisher

Posted by: Edi Yanto on: May 21, 2009

In: Oracle
Comment!

Easy AdSenser by Unreal

BLOB is “a collection of binary data stored as a single entity in a database management system. Blobs are typically images, audio or other multimedia objects, though sometimes binary code is stored as a blob. Database support for blobs is not universal.” (wikipedia)

Support for extracting BLOB images to XML is coming, the XMLP extraction engine will extract the images and encode to base64 in the XML, you will then be able to pull this into the output via the RTF template.

Following are something that you should know:

Can’t generate xml data for CLOB more than 40 kb in report builder.
BLOB Images are supported in XML Publisher 5.6.2 or later.
Encode BLOB to CLOB use this following function:
CREATE FUNCTION getbase64( p_source BLOB )
RETURN CLOB
IS
v_result CLOB;
BEGIN
DBMS_LOB.createtemporary(lob_loc => v_result, CACHE => FALSE, dur => 0);
Wf_Mail_Util.EncodeBLOB ( p_source, v_result);
RETURN ( v_result );
END getbase64;

To show BLOB Images use the following code:
<fo:instream-foreign-object content-type=”image/jpg”><xsl:value-of select=”.//IMAGE_”/></fo:instream-foreign-object>
XML Publisher supports in general right now only JPG, GIF and PNG.

Notice the template needs to know the mime type of the image, in this case “image/jpg” The IMAGE_ contains the base64 encoded image data. Template and sample data here

Share on Facebook

Easy AdSenser by Unreal

Tags: ebiz, reports, XML/BI Publisher

13 Responses to "BLOB Images in XML Publisher"

1 | sheena
June 20, 2009 at 4:46 pm

hi Edi,
i have xml pub ver 5.6.3 on my end. trying to get a dynamic image to show in the rdf and used the xml code you had given above in the field in the RDF document. I am using a data template and have done the template and data defninitions and connected all those pieces together. if i remove the image field from the RDF, it works fine. but if i add it in i get this error:
java.sql.SQLException: No corresponding LOB data found :
in the log of the concurrent process. what could be wrong?
please help!
Sheena

2 | sheena
June 20, 2009 at 6:13 pm

Edi, the concurrent process ends in a warning now:

Calling XDO Data Engine…
[062109_015329488][][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)

3 | Kavitha
July 15, 2009 at 9:56 am

Hi,

My template is Acrobat(PDF) and not RTF. How do I code in Acrobat so that it recognises the image. I understand that if its RTF then the code is

Is there something for Acrobat? Please let me know.

Thanks
Kavitha

4 | Edi Yanto
October 1, 2009 at 11:14 pm

Hi sheena,

sorry for my late reply, u don’t need to add the image field to the RDF because xmlp can’t generate xml from BLOB. You must encode to CLOB with the function getbase64 above and the query in RDF will like this example :
SELECT tech_id, getbase64(photo) photo
from technicals

note: in the table already have images.

Thanks
Edi

5 | Mark
October 30, 2009 at 2:09 am

Hi, I’m using Oracle EBS R12. We have documents uploaded from users and stored in the database as BLOBS. These documents will be MS Word documents or PDFs.

You mentioned previously that only jpg, gif and png are supported?

Is it possible to display a pdf or ms word document in a BI P RTF template report?

I’ve tried and set the following:

It doesn’t work. I also tried changing the content-type=”image/jpg” and it also doens’t work.

When I run the template in MS Word I just get a blank field where the blob should appear.

Any help much appreciated.

Thanks,
Mark.

6 | Edi Yanto
October 31, 2009 at 4:24 am

@Kavitha, sorry I’ve never tried it, if one day I know the answer, I’ll post it… maybe you can use it in RTF first.

@Mark, I don’t know the XMLP/BIP data engine now can supports the extraction of blob documents Word/PDF/XLS directly into the XML or no… but it can support the blob images.

I just know, we can use sub template in our main template with the following code in our main template:
< ?import:file:///d:/temp/templates.rtf?>
Once deployed we can then use either a full URL or load the sub template to the Template Manager in Oracle EBS and use the xdo: URL format.

Thanks,

Edi

7 | Miki
November 11, 2009 at 8:21 am

Hi Edi,

First I want to thank you for this guidance. I was looking solution for this problem over 20 days….. Thank you for cut my pains

Now, I have another problem!
I have over 10,000 blob pictures in table. Thay all visible and ok. Most of them I show in output document(rtf template) with success, but some of them show as blank (no image) and some blob(clob) do not execute at all (request execute with warnings, and can not show document).

All images are same size (900px*1200px) and format(jpg).

What is happening? Why for some picture this work and for another don’t?

Thanks,

Miroslav (Miki)

P.S. Sorry if my English is bad

8 | Edi Yanto
November 11, 2009 at 10:36 pm

Hi Miki,

Have you check the content of XML file for the some blob that can’t show in RTF? Are they have a full clob code that have been generate from the report for the image that can’t show? How about the size of the image file?

Thanks,

Edi

9 | Miki
November 12, 2009 at 3:17 am

You are right, there is difference in size of those images (same resolution but diferent size). Problematic image are over 3MB.

UHHHHH…… It’s long story….. Small number of images was load from form (HRMS Menager). Those images have normal size (about 100K) and i can view them in rtf template.
9000 images I was migrate in table direct with my own code. This was start of HRMS project when we had to migrate all data. All images in this table are LONG RAW. I was convert LONG RAW in BLOB, BLOB in CLOB, CLOB in image document with your help.
All input images are JPG, same resolution and size about 100K.

This “my own code” is writen in Oracle Forms Bilder 10.
I used Base Data Block to insert new record with code

READ_IMAGE_FILE(path, ‘JPG’, ‘PER_IMAGES.IMAGE’);

What to do with this big images?
Have you any suggestion?

Thanks,

Miki

10 | Edi Yanto
November 12, 2009 at 8:59 pm

Hi Miki,

Yes, I was suspicious the size of those images are the main problem.. over 3MB? it’s about 2048 x 1536, so high resolution…
You must convert/resize those images size first, before insert to table. Try not to exceed 100 KB.

Thanks,

Edi

11 | Miki
November 13, 2009 at 12:49 am

Edi,

All images are 900 x 1200 and have size about 100K.
When I insert those images in table with
READ_IMAGE_FILE(path, ‘JPG’, ‘PER_IMAGES.IMAGE’);
take 3MB or more….

For example: image1.jpg, 900 x 1200, 120 KB

when I insert this image i table (LONG RAW)
it’s 900×1200 and have 3,5 MB

THAT IS MY PROBLEM. I suppose that missing something in code
READ_IMAGE_FILE(path, ‘JPG’, ‘PER_IMAGES.IMAGE’); and I don’t know what….

Thanks Edi

I’m sorry if I bother you

12 | Edi Yanto
November 14, 2009 at 12:11 am

Hi Miki,

How about the result if you change the data type of the image column to BLOB data type?

Thanks,

Edi

13 | Miki
November 16, 2009 at 4:56 am

Edi,

Same thing happened when I changed the data type in table!

Is there any chance to ‘resize’ (convert) existing blob images?

Thanks,

Miki

Leave a Reply

Latest Comments
Tags

Edi Yanto: have you download the fndconc.pll at the above link (http://www.ediyanto.co.cc/wp-content/uploads/2009/07/fndconc.pll) ? Are u compile at the right
Rahul: Hi, Very good article. Thanks for providing such great tip.
Edi Yanto: Hi Herlambang, Untuk Internal Manager, sillahkan lakukan step dibawah ini: 1. login sebagai user oracle 2. masuk ke $APPLCSF/scripts/[SID]_[host