Edi Yanto (何 萬 新)

BLOB Images in XML Publisher

Posted on: May 21, 2009


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 (remove the extension .odt  )

Update: 22-Sep-2010
Call BLOB image on footer:

134 Responses to "BLOB Images in XML Publisher"

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

Like

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)

Like

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

Like

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

Like

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.

Like

@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:

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

Like

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🙂

Like

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

Like

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

Like

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

Like

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

Like

Hi Miki,

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

Thanks,

Edi

Like

Edi,

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

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

Thanks,

Miki

Like

Edi,

I have created a text form field PHOTO by including the following fro the field.
in my RTF template.

In my peoplecode i am selecting the image using sqlExec
&nodePhoto= &nodeRoot.AddElement(“PHOTO”);
select employee_photo from ps_empl_photo

&nodePhoto.NodeValue = &photoobtained;

When generating the report , an error is obtained:
“Error generating report output (235, 2309)”

Can you please help me and check where i am wrong?

Like

Hello there, i have spent a lot of time on the problem u have stated and have been successful on ocassions too….but still hanging.

wht report builder are u using. m using reports 6i and when i include a clob column and select xml ouput from the application ebs 11i i get the following error. no xml output is generated! do you have any solution?

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

——————————————————————————–

The following tags were not closed: MODULE1, LIST_G_IMAGEID. Error processing resource ‘http://tlnt03.fi.gi.telenor.com:800…

next i was able to get two segments by converting the blob to varchar2 using dbms_lob.substr(clob,4000,1) function and xml was perfect but when i go beyond two sengments the report starts to error out! can you please help, i am unable to solve this!

can you please direct me to what is the limitation of reports 6i so that i can give reference to my boss!

Like

@Annie,
Have you encode BLOB to CLOB (base64 function)?

@Sarfraz,
I was test this task by report builder 10g, database 10g, and oracle apps R12. How about your database version, I notice the size on the clob column is 4000 by default (Oracle 9i) and you must find out how to retrieve more than 4KB.
How about the output result (PDF/RTF, etc) after you use the dbms_lob.substr function?
Can you send your report file (.rdf) to me? I’ll help you for checking it.

Thanks,

Edi

Like

yes i have encoded it to base 64…..
here is my query
select
image_id ImageID
, imageEO.image_name ImageName
,mob_getbase64String(imageEO.image) IMAGE_CLOB
,imageEO.image image_BLOB
from per_empdir_images ImageEO,per_all_people_f papf
where
papf.person_id = imageEo.orig_system_id and
trunc(sysdate) between effective_start_Date and effective_end_date
and imageEO.image_id = 19003;

now as soon as i bring either CLOB or BLOB and take XML as the output from the apps…….i start getting errors……the workaround i found for this for dbms_lob function…..here is the query for it
select
image_id ImageID
, imageEO.image_name ImageName
–,imageEo.image picture
,dbms_lob.getlength(dbms_lob.substr(mob_getbase64String(imageEO.image),4000,1)) length1
,dbms_lob.getlength(dbms_lob.substr(mob_getbase64String(imageEO.image),4000,4001)) length2
,dbms_lob.getlength(dbms_lob.substr(mob_getbase64String(imageEO.image),4000,8001)) length3
,dbms_lob.getlength(dbms_lob.substr(mob_getbase64String(imageEO.image),4000,12001)) length4
,dbms_lob.getlength(dbms_lob.substr(mob_getbase64String(imageEO.image),4000,16001)) length5
,dbms_lob.getlength(dbms_lob.substr(mob_getbase64String(imageEO.image),4000,20001)) length6
–,dbms_lob.getlength(imageEO.image) Image_Length_before
–,mob_getbase64String(imageEO.image) Image_Length_after_encoding
,dbms_lob.substr(mob_getbase64String(imageEO.image),4000,1) IMAGE1
,dbms_lob.substr(mob_getbase64String(imageEO.image),4000,4001) IMAGE2
,dbms_lob.substr(mob_getbase64String(imageEO.image),4000,8001) IMAGE3
,dbms_lob.substr(mob_getbase64String(imageEO.image),4000,12001) IMAGE4
,dbms_lob.substr(mob_getbase64String(imageEO.image),4000,16001) IMAGE5
,dbms_lob.substr(mob_getbase64String(imageEO.image),4000,20001) IMAGE6
from per_empdir_images ImageEO,per_all_people_f papf
where
papf.person_id = imageEo.orig_system_id and
trunc(sysdate) between effective_start_Date and effective_end_date
and imageEO.image_id = 19003;
in order to concatenate these segments i used a character formula column…..concatenation of two segments is working fine but above that i am dooomed…..please help and you didnt leave your email id, please mention it so that i can send you the files

Like

Hi Sarfraz,

You can send me your files at my gmail id edi.y4nto@gmail.com

Thanks,

Edi

Like

Hi buoz, it works 100%! Thank you very much ya😉

Like

Hello Edi,

I trying to create the following function getbase64 in my DBase:

CREATE OR REPLACE 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;
/

The following error message is obtained while creating:
“Warning: compiled but with compilation errors”

I think this one Wf_Mail_Util.EncodeBLOB ( p_source, v_result) is causing the error.

Can you please guide me in creating the function getbase64 so that i can convert my image blob stored as long raw in DB to clob in my peoplecode?

Thank you
Annie

Like

Hi Annie,

you can show your compilation errors message with this syntax “show error” in your sqlplus to see what cause this errors.
You can also check whether the package procedure Wf_Mail_Util.EncodeBLOB is exist or not in your DB.

Thanks,

Edi

Like

Hi Edi,
Greetings!!I am trying to get in touch with you second time😦
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.

Like

Missed to type the code:
This works in a field not in a footer

Like

Dear Rekha,

Have you try this below code:

at body of your word template:

your BLOB code

at footer of word template:
write this

Thanks,

Edi

Like

Hey Edi,
This also did not work!! when I called the in footer it re printed my code instead of a picture.Also tried creating a table and calling the same in footer but had a same issue in printing a blob.
Let me know if there is any alternative.
OR
This can also be achieved by printing a table at end of the each page without declaring in footer or without being the part of the footer as footer is not supporting a blob.
Please let me know how this can be achieved.

Thanks and Regards,
Rekha

Like

Hey Edi,
Any idea on the issue I raised ??
If anyone has an idea please let me know

Regards,
Rekha

Like

Hi Annie,

I think you don’t use the oracle applications E-Business right? If you have oracle e-business you can find this package at APPS schema. I attach this package here

Thanks,

Edi

Like

Hi Rekha,

can you send me (edi.y4nto@gmail.com) your XML dan template (RTF) files? I’ll try to find the solution.. send your both template declare in footer and end of each page..

Thanks,

Edi

Like

Hello Edi,

I am currently using Oracle 10g, but couldn’t find the package procedure Wf_Mail_Util.EncodeBLOB.

Annie

Like

Edi,
I have sent the details and files to your gmail id.Kindly check and advise.

Thanks,
Rekha

Like

Hello Edi,

Thank you for the package you sent.
Meanwhile i have used the following function to convert from blob to clob:
CREATE OR REPLACE FUNCTION BATCH.F1 (lv_blob BLOB)
RETURN CLOB
IS
RESULT CLOB;
resultstring VARCHAR2 (8192);
resultstring1 CLOB;
l_amt NUMBER DEFAULT 2048;
l_raw RAW (8192);
l_offset NUMBER DEFAULT 1;
l_clob CLOB;

BEGIN
DBMS_OUTPUT.put_line (‘length blob: ‘ || DBMS_LOB.getlength (lv_blob));
BEGIN
DBMS_LOB.createtemporary (resultstring1, FALSE, DBMS_LOB.CALL);
DBMS_LOB.createtemporary (RESULT, FALSE, DBMS_LOB.CALL);
LOOP
DBMS_LOB.READ (lv_blob, l_amt, l_offset, l_raw);
l_offset := l_offset + l_amt;
— dbms_output.put_line(‘ voor resultstring’);
resultstring :=
UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (l_raw));
— dbms_output.put_line(‘ na resultsstring’);
resultstring1 := TO_CLOB (resultstring);
DBMS_LOB.append (RESULT, resultstring1);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
— dbms_output.put_line(‘length:’||dbms_lob.getlength(result));
RETURN (RESULT);
END f1;
/

I have succeeded in displaying a jpeg image in my RTF template for a size of less than 2 kB.
But when displaying a jpeg image of size more than 2 KB, for e.g 2,42KB, the image has not been displayed completed, it has been cut at the bottom. Even my table, borders surrounding the image has been disappeared.
Note: Need to open the pdf in Acrobat Reader more than version 7.0 as facing the error “a drawing error occur”

I have sent you my rtf template, xml file data and the image in your mail edi.y4nto@gmail.com. Can you please check for a jpeg image of more than 2 KB, why i am facing this problem?

Thank you,
Annie

Like

Hi Edi,(Please reply whoever can as its urgent)
Greetings!!
I embedded certain chinese letters in my rtf and pdf diplays accurately at my desktop.So, I registered the same template as Language:-Chinese and Territory:-US in E-business suite but the chinese script is displayed as ????. Kindly tell me where I went wrong and how can that be rectified, what set ups are missing.

2) Does RTF to PDF output takes common Font.Is there any facility to display Book Antiqua FONT in pdf while displaying the out put as the font set in rtf is getting output in some uniform Font.

Regards,
Rekha

Like

Hi Rekha,

If you want embedding a font, you can do the following step:
Assume you want to use a font in your template called ‘MyFont’, this is not available as a regular font on your server so XML Publisher requires that you tell it where the font is at runtime. If this location is known and fixed for wherever the template might be deployed, lets assume ‘/tmp/fonts’ then you can set the font and its location into the template and XML Publisher will use it at runtime. In the properties dialog of your template, you would enter:
Name :xdo-font.MyFont.normal.normal
Type :Text
Value :truetype./tmp/fonts/MyFont.ttf

at your template font name, type as equal to your font name file, “MyFont”

When the template is applied to the XML data on the server XML Publisher will look for the font in the /tmp/fonts directory.

Thanks,

Edi

Like

Hi Rekha,

I have sent an email about BLOB on footer to your email address (solve it with my xml publisher tag template and call), please check it..

Thanks,

Edi

Like

Hey Edi,
Thanks!! Assuming you are busy person some how I broke my head and had achieved the same one fresh morning and your tip did work.
Regarding using the Font,can you give me a exact scenario or screen shot about where the below settings are to be done?
Name :xdo-font.MyFont.normal.normal
Type :Text
Value :truetype./tmp/fonts/MyFont.ttf

I want that font settings to be applied when the template is executed from Oracle E-business suite ,Is it possible??Please
Reply.

I registered the Chinese hard coded template with
Language:-Chinese
Teritory:-China still this did not work out.Will be waiting for your reply.

Regards,

Rekha

Like

Hey Edi,
I understood and it worked perfectly🙂 Lods of thanks.

Like

Hi Edi,
I am done with all my doubts and here is the last one probably🙂.I have modified my template in such a way to print the table with multiple rows and columns repeated.
***Table structure contains as below(its a single table)****
row1 (5 columns)
Row2(columns merged)
Row3(5 columns)
Row4(columns merged)

I want these rows to be printed repeatedly till my data ends.
I have given as intial field of my first row but issue is that row 3,row4 are not getting repeated and getting printed at the end.

HELP ME!!! where do I need to end exactly.Any other solution.

Regards,
Rekha

Like

Hello Edi,

I have succeeded in displaying a blob image in my XML publisher report. However when the blob image in database is Null, i am getting an error message :
“Error generating report output…. (235,2309)”

Can you please help me in resolving this issue? What to do when the image to be displayed is null in the database?
Its Urgent.

Complete error msg:
ConfFile: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config\xdoconfig.xml
Font Dir: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts
Run XDO Start
Template: C:\HRMS \C0047495 – Fiche Individuelle\Photo_test\EYBLOB.rtf
RTFProcessor setLocale: en-us
FOProcessor setData: C:\HRMS \C0047495 – Fiche Individuelle\Photo_test\EYBLOB_in_XMLP_210509.xml
FOProcessor setLocale: en-us
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.charAt(Unknown Source)
at oracle.apps.xdo.common.encoding.Base64Util.decode(Unknown Source)
at oracle.apps.xdo.template.fo.elements.FOInstreamForeignObject.end(Unknown Source)
at oracle.apps.xdo.template.fo.FOHandler.endElement(Unknown Source)
at oracle.apps.xdo.common.xml.XSLTHandler$EEEntry.sendEvent(Unknown Source)
at oracle.apps.xdo.common.xml.XSLTMerger.startElement(Unknown Source)
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(Unknown Source)
at oracle.apps.xdo.template.FOProcessor.generate(Unknown Source)
at RTF2PDF.runRTFto(RTF2PDF.java:629)
at RTF2PDF.runXDO(RTF2PDF.java:439)
at RTF2PDF.main(RTF2PDF.java:289)

Like

Hi Annie,

Error in generate to XML Output or to XMLP Layout? If error in generate to XMLP Output, you can add check condition to this field is NULL or not, use the condition IF, do the action to display image if this field is not null

Thanks,

Edi

Like

Hi Edi,
Kindly let me know how we check OR condition in XML publisher?? For executing AND condition we give
Print Data

how do I check the OR condition?? which returns the data on any one validation. Plz give me tips.

Regards,
Rekha

Like

Actually I tried to write the code above and some data did not get pasted.
For And validation we give:-
IF condition
IF condition
print data
end if
end if

For OR
give me If-else if execution syntax.
I tried with if then else if but not working

Like

Hi Rekha,

In XMLP you can use the IF condition like this: (Replace * with ?)
1000 then ‘Higher’
else
if AMOUNT

choose condition:

Btw, you can check this condition in your report builder for generate this field in XML for using in your xmlp layout later.

Thanks,

Edi

Like

Hi Edi,
I agree, but in report builder the conditions that we check in format trigger for displaying and if I am using same conditions for displaying in XMLP then I have to use IF conditions before displaying the data same as my format trigger in reports.
If there are multiple comditions handled in format trigger of the report and if I am using the same for XML to pdf out put,then I need to check the same at XMLP right? SO, am stuck with handling multiple conditions some times and OR conditions.

Give me remedy for this.
Also give me if elseif condition with XMLP syntax.I doubt I am going wrong some where because of which data is not getting displayed as per validation😦

Rekha

Like

Hi Rekha,
you can find the if elseif condition on my previous comment..

Thanks,

Edi

Like

Hey Edi,
Choose statemt will not replace OR condition as if two conditions are true then data will get printed.
Selecting the if elseif condition and replacing the question mark with *, id below query is correct as validation is not right for worst conditions.

Let me know plz…
Regards,
Rekha

Like

I am not able to paste again😦 Check ur email plz

Like

2)One more question:-
Considering the below sample code:
Xml code:-

HEAD1….
HEAD2….

LINE1…
LINE2…

SHIP1…
SHIP2…

LINE3…
LINE4….

In xml PUBLISHER:-
For loop:G_head
For loop:G_Line
For loop:G_Ship
How can I fetch HEAD1.. here??
End for-each
End for-each
End for-each

Like

Hi Rekha,

Note: Replace * with Question Mark (?)

you can try the choose when condition combine with the AND condition like this: “”

Q.1) Is this correct, please correct if it is wrong.

–Answer:
you use use the if condition like this :

Q.2)One more question:-
Considering the below sample code:
Xml code:-

HEAD1….
HEAD2….

LINE1…
LINE2…

SHIP1…
SHIP2…

LINE3…
LINE4….

In xml PUBLISHER:-
For loop:G_head
For loop:G_Line
For loop:G_Ship
How can I fetch HEAD1.. here**
End for-each
End for-each
End for-each

–Answer:
you can fetch HEAD1 with the following syntax:

OR
–up 2 level with ../../

Like

Hey Edi,
Thanks!! for your reply I will test properly.
Will the node concept works irrespective of nested For loop ?
In xml PUBLISHER:-

For loop:G_head

For loop:G_Line

For loop:G_Ship

How can I fetch HEAD1.. here??

End for-each

End for-each

End for-each

Like

Hi Rekha,

if you use the “”, don’t need to pay attention of nested for loop, but if you use the “”, must pay attention of the nested loop, how much level above that you want to display the data.

Thanks,

Edi

Like

Hey Edi,
Awesome🙂 You Rock.. !!! ancestor tag works perfectly for my scenario.

Thanks a lot.Keep Rocking!!

Like

Hi,

can you please send me the rdf & rtf file for the same. I need to call changes imger on my report dynamically.

Thanks
Deepak

Like

Hi Deepak,

You can download these files at the link I provide in the end of this article (Template and sample data here), https://ediyanto83.wordpress.com/wp-content/uploads/2009/05/eyblob.zip

Thanks,

Edi

Like

Hey Edi,
Am back with one more query!!!

We have a huge text as a note that is printing properly as text out put using rdf.But when same rdf is mapped to print the XML code the text is getting spaces in the text nullified.

Ex:- If rdf is printing a test as below
1) this is first line.
2) this is second line

then the xml code is taking the data from rdf as below
1)this is first line.
2)this is second line.

We are fetching the data using formula column as character data type as only that is available.
Let me know your solution please.

Regards,
Rekha

Like

Hey Edi,
Am back with one more query!!!
Am re typing the text again🙂 as this also did not count the space.

We have a huge text as a note that is printing properly as text out put using rdf.But when same rdf is mapped to print the XML code the text is getting spaces in the text nullified.

Ex:- If rdf is printing a test as below
1) (lots of space)this is first line.
2)(lots of space) this is second line

then the xml code is taking the data from rdf as below
1)this is first line.
2)this is second line.

We are fetching the data using formula column as character data type as only that is available.
Let me know your solution please.

Regards,
Rekha

Like

Hi Edi,
We use oracle 10g for implementing peoplesoft.
I downloaded the package wf_mail_util.zip.We do not have Ebusiness suite so running this package was a failure.I tried to use peoplecode decoding into base64 using crypt classes. The problem i face is that even on querying for the image field the variables are not long enough to hold values queried so they are returning 5 or 6 length words.

Error encountered while installing package header
PLS-00113: END identifier ‘APPS’ must match ‘WF_MAIL_UTIL’ at line 1, column 9
PLS-00103: Encountered the symbol “.” when expecting one of the following:

Errors encountered while installing package body
105 Create package body, executed in 0.156 sec.
141 33 PLS-00201: identifier ‘WF_CORE.NEWLINE’ must be declared
141 2 PL/SQL: Statement ignored
145 2 PLS-00201: identifier ‘WF_CORE.CONTEXT’ must be declared
145 2 PL/SQL: Statement ignored
187 7 PLS-00201: identifier ‘WF_CORE.CONTEXT’ must be declared
187 7 PL/SQL: Statement ignored
203 8 PLS-00201: identifier ‘FND_TIMEZONES.TIMEZONES_ENABLED’ must be declared
202 4 PL/SQL: Statement ignored
254 11 PL/SQL: ORA-00942: table or view does not exist
252 4 PL/SQL: SQL Statement ignored
396 14 PLS-00201: identifier ‘WF_CORE.TRANSLATE’ must be declared
396 1 PL/SQL: Statement ignored
399 17 PLS-00201: identifier ‘WF_NOTIFICATION.DOC_TEXT’ must be declared
399 1 PL/SQL: Statement ignored

Kindly help me out.

Like

@Rekha,
“then the xml code is taking the data from rdf as below
1)this is first line.
2)this is second line.”

The above example output on template RTF or XML data? Have lots of space or not in XML Data?

@Arun,
If you want to use the wf_mail_util package, you must copy all the dependecies objects to your database like wf_core, etc., and make sure you don’t use the schema APPS because it’s not available at your database.

Thanks,

Edi

Like

Hi Edi
I have surfed the net for these packages WF_CORE,WF_NOTIFICATION,‘FND_TIMEZONES but could not find it.Can you tell me from where I can download them. Or is there any workaround for this ??
Thanx in advance

Like

Hi Arun,

You can find them at e-business suite database, because they may have other dependency objects.

Thanks,

Edi

Like

Hi Edi,
I edited the package body wf_mail_util as follows i included only the encodeblob function.
CREATE OR REPLACE PACKAGE BODY wf_mail_util as
/* $Header: wfmlutb.pls 120.5 2006/04/17 07:06:06 sstomar ship $ */

— EncodeBLOB
— Receives a BLOB input and encodes it to Base64 CLOB
— IN
— BLOB data
— OUT
— CLOB data

procedure EncodeBLOB(pIDoc in blob,
pODoc in out nocopy clob)
is
rawData raw(32767);
chunksize integer;
amount binary_integer := 32767;
position integer := 1;
base64raw RAW(32767);
chunkcount binary_integer := 0;
cBuffer varchar2(32000);
begin
chunksize := 12288;
amount := dbms_lob.getLength(pIDoc);
if(chunksize < amount) then
chunkcount := round((amount / chunksize)+0.5);
else
chunkCount := 1;
end if;

for i in 1..chunkcount loop
dbms_lob.read(pIDoc, chunksize, position, rawData);
base64raw := utl_encode.base64_encode(rawData);
cBuffer := utl_raw.cast_to_varchar2(base64Raw);
dbms_lob.writeAppend(pODoc, length(cBuffer), cBuffer);
position := position + chunksize;
end loop;
dbms_lob.WriteAppend(pODoc, 1, chr(13));

exception
when others then
–wf_core.context(‘WF_MAIL_UTIL’, ‘EncodeBLOB’);
raise;
end EncodeBLOB;
end WF_MAIL_UTIL;
/

I edited the line dbms_lob.WriteAppend(pODoc, 1,wf_core.newline ); as dbms_lob.WriteAppend(pODoc, 1, chr(13)); and I remmed out the exception line wf_core.context(‘WF_MAIL_UTIL’, ‘EncodeBLOB’);
Now the getbase64 function works fine and I get a picture displayed while previewing the RTF but there is an empty page or a page break before the photo appears. Is there any solution for this??

Like

How to show PER_IMAGES Table Image field (LONG RAW, Picture Data) to a XML report, (rtf Templates). If anybody Know this solution, Kindly to help me. I tried, but it shows null XML Tag, or warning Error.

Thanx & Regards,
M.RAFI.

Like

Hi Edi,

When I used to run xml image print report, I am getting a warning error.

Environment will now switch to UTF-8 code-set.
Parts of this log file may not display correctly
as a result. This is an expected behavior.

XML_REPORTS_XENVIRONMENT is :
/ora/dbSite/siteora/8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb

XENVIRONMENT is set to /ora/dbSite/siteora/8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb

Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
AMERICAN_AMERICA.UTF8

‘.,’

would you please xplain me why I am getting this warning.

If I used to print from Text Format, its work file, this error shows only XML format.

Also I tried from outside the application, the system never generate XML data file.

Thanks,

Mohammed Rafi,

Like

Hi Rafi,

Have you encode the BLOB to CLOB for the images?

Thanks,

Edi

Like

Hi Edi,

But I used getbase64 function, Is this function will convert BLOB to CLOB? Or else how can I encode this BLOB to CLOB? If I use this direct TO_LOB Conversion the system shows an error 10:36:34 ORA-00932: inconsistent datatypes: expected NUMBER got BLOB.

Did you found any mistake from my code?

Thanks & Regards,
MRAFI.

Like

Hi Mrafi,

You must encode the BLOB (images) to CLOB use this above function.

Regards,
Edi

Like

Hi Edi,

Yes I already use that conversion through getbase64 function, but till I couldn’t get an output. Still I am getting warning error, which I mentioned above. If you know kindly update me, How shall I resolve that issue.

Thanks & Regards,

Mohammed Rafi,

Like

Hi Edi,

I am repeating the question which mrafi has asked. How to show PER_IMAGES Table Image field (LONG RAW, Picture Data) to a XML report, (rtf Templates). I have the same requirement and I couldn’t find a solution. Can you please advise the steps in detail
Regards
Anish

Like

Hi Anish,

First you must encode the BLOB images to the CLOB, after that you can use the above code on my article at point to show the BLOB images in your rtf tempate to show the images.

Thanks,
Edi

Like

Hi

I am writing a pl/sql procedure to generate and excel report using xml
in my pl/sql

I need to show a logo into the excel output for each report in a cell

how can i do it ?
please help me

thanks
Mandar

Like

Hi Mandar,

What tool do you use for show the logo in your excel output? BI Publisher or not? If BIP, you can do it by follow the above steps (Note: for oracle apps, excel can’t show the images)

Thanks,
Edi

Like

Hi EDI

Thanks For ur response

I do not use BI
I have a PL/SQL Code in which i use XML syntax and then run the PL?SQL code using a concurrent request and generate and email
and show the output in the email

Thanks
Mandar

Like

Hi Edi

This information is great, thank you! Is it possible to display the image in the header or footer section of the rtf?

Thanks
Norah

Like

Hi Norah,

You can’t display the image in the header/footer use form fields or full placeholder text. But you can use the xml publisher tag template in body and call function in header/footer.
Please find the way at https://ediyanto83.wordpress.com/wp-content/uploads/2010/09/call_blob_eof.png or the above article, I just put it on this article.

Thanks,
Edi

Like

Hi annie ,
i am also using oralce 10g in backend and frontend using Peoplesoft . i also face the same problem because i did no find the wf_mail_util package in oracle 10g . i used ur fuction batch.F1 but did not get the picture fro database i my tamplate.
please send me ur template or any suggestion to over come this problem . i will be grate full . my email id is
mailtosohail@gmail.com

Like

Hello Edi,

Thank you for posting this article. This is very useful for my project where I am trying to save the signature as a jpg file and try to get it back in xml output for printing AP checks from E Business Suite using BI Publisher (xml publisher).

I created a report and when I run the report to generate xml output, the report runs fine. However when I register the same report in EBS 11.5.10.2 ATG RUP7 ( executable and concurrent program) and run the report through concurrent request screen, I am getting the following error

stat_low = 8B
stat_high = 0
emsg:was terminated by signal 11

Any idea how to fix this? I have logged an SR and yet to get any positive response on it.

Thanks

Like

Hi ppatel,

the error message terminated by signal 11 can be caused by various condition, may be the length of the parameter, unhandle condition divided by zero, etc.. May be you can check by running this report in your report builder. It’ll tell you what the error condition.

Thanks,
Edi

Like

Hi Edi,

When I run the report in the builder, it runs fine.
If I remove the image, it runs fine both in builder as well as in EBS. So I know for sure, the signal 11 error occurs only when I have BLOB to CLOB image in the report. Report does not have any parameter, division by zero etc.

Thanks
ppatel

Like

hi ppatel,

Do you use the getbase64 function to encode the blob image to clob (datatype in rdf is clob for the image)? the datatype of field image on table is BLOB?

Thanks,
Edi

Like

Hey Edi,
Yes, I am converting the blob to clob using base64 function. The image in table is in column of type BLOB and the data type for the converted image in report is of type ‘Character LOB’.

Here is the query in the report:

select image_name , mime_type
, ru_getbase64(ri.content) RU_SIGNATURE
from rucust.ru_images ri

And here is the table definition:

CREATE TABLE RU_IMAGES
(
IMAGE_NAME VARCHAR2(30 BYTE),
MIME_TYPE VARCHAR2(30 BYTE),
CONTENT BLOB
)

As I said earlier, report runs fine from outside of EBS.

Thanks
ppatel

Like

Hi pptal,

How about you run this report (without template rtf) to generate the xml output? it’s successful? If not, eliminate the ru_getbase64(ri.content) RU_SIGNATURE on your report query and run in your instance, to see whether the error is caused by it or not.

Thanks,
Edi

Like

Hey Edi,
I ran the report without template – same error. If I eliminate ru_signature, report runs fine.
Note that we are on EBS 11.5.10.2 which uses reports 6. Reports 6 does not support LOB. Not sure if this is the issue.
I am not able to create the report with ru_signature in 6i. Maybe this is the issue.
Thanks
ppatel

Like

Hi Edi,
How do you get the clob in the output file of a 11.5.10 concurrent program that is created using pl/sql.

I use fnd_file.put_line to send the data to the output file.

Is this going to work or is there a different method to send a clob file to the Oracle output file.

Dan J.

Like

Dear Dan J.

Oracle output file only can show 255 char, CMIIW. In this case, I use report builder to generate the XML file..

Thanks,
Edi

Like

Of course, what a great site and informative posts, I will add backlink – bookmark this site? Regards,
Reader.

Like

I’m looking information about this from last friday, thanks!

Like

Hi Edi,

I have gone through your Blog on XML Publisher one. I like the way you have given answers to all the questions in Blog. I appreciate your help to other people.

Right now am working in PeopleSoft, as you know that even PS is also using the XML BI Publisher to generate the Reports through source of PS Query, XML etc.,.

There is already an existing report and now they are asking small change in the report that we need to pull some image on to the report but that image is stored in one of the database field i.e., CLOB data base field. Please let me know how to pull the image on to the report. They already prepared the RTF file.

I am working on PeopleSoft PeopleTools V8.50 and XML Bi Publisher is latest version. I need to know where we can write the code like changing the BLOB to CLOB in the RTF File.

Please help me in this.

Thanks,
Suren

Like

Hi Suren,

You can follow the steps on this article to pull the BLOB images (encode BLOB to CLOB first) on to the RTF to show the images using the “(fo:instream-foreign-object content-type=”image/jpg”)(xsl:value-of select=”.//IMAGE_”/)(/fo:instream-foreign-object)”

Note: replace the ( with

Thanks,
Edi

Like

Hi Edi,
I am Rekha, if you remember you had helped me one year back in xml publisher issue. This time its regarding etext template. Urgent requirement please help me as am struggling with one issue.
XML Code as below
————————————
(Outboundpayment)
— (Payee)
— (Bankdetails)
— (Bankamount)
— (Documentpayable)
—— (Invoicedetailsetc)
— (Documentpayable)
—— (Invoicedetailsetc)
(/Outboundpayment)

Etext template
———————————————-
table1:- details abount payee and bank amount etc

table2:- should have details of invoice and bank details as per my requirement.
I could print all invoice details but not the bank details etc as the node is in higher hierachy😦

In XML publisher I used ancestor-or-self::?/C_ONE_REQUESTOR to get the details from any node nut this does not work in Etext template.Please HELP!!!!

Regards,
Rekha Pujeri

Like

Hi Rekha,

You can write the full tag like /Outboundpayment/Bankdetails/Bankamount. Is it solve your problem?

Thanks,
Edi

Like

Hi Edi,
Thankk that worked for me:-)

Like

Hi Yanto,

I created the XML as below in R12 by the function getbase64:

Qk02uAsAAAAAADYAAAAoAAAAgAIAAJABAAABABgAAAAAAAC4CwAAAAAAAAAAAAAA
AAAAAAAA////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////

Could you help me?

Like

Hi Tommy,

What can I help you?

Thanks,
Edi

Like

Hi Edi,

I saw your blog and was able to use the procedure that you gave. To convert blob to clob base64

My problem is the same as Sarfraz R Ramay.

He was having a problem if the file size is big. Oracle Apps will throw an error
REP-271504897: Unable to retrieve a string from the Report Builder message file.

I use the dbms_lob.substr and was able to substr my clob and when I run this report in Apps everything seems alright and I’m able to view the XML Data.

Now I come to the part where I have to mapped the XML data to rtf file.
How do I append those columns that I have in XML data to create one whole image.
I did copy (fo:instream-foreign-object content-type=”image/jpg”)(xsl:value-of select=”.//IMAGE_”/)(/fo:instream-foreign-object)
from your website.
Now I have 6 columns that makes up an image. How do I do this.

Thanks and regards,
Ayu Ross

Like

Hi Ayu,

In this current step, you just change the element name IMAGE_ to the element name of the 6 columns on the below script:

(fo:instream-foreign-object content-type=”image/jpg”)(xsl:value-of select=”.//IMAGE_”/)(/fo:instream-foreign-object)

Note: Change the ‘(‘ to ”

Thanks,
Edi

Like

But how do I append these 6 column I generate from Oracle Report in Apps.
I have 6 column in my XML Data … Col1, col2, col3, col4, col5, col6
How can I replace this in the statement.

Thanks
Ayu Ross

Like

Sorry to bother you.

I did what you told me but still I got error when I try to preview rtf to PDF.

This is my select statement: I have to substr the blob since it is too big. This is to avoid getting error when I run this in Oracle e-BUSINESS SUITE.

select fad.ENTITY_NAME, fad.PK1_VALUE ORG_ID, FAD.PK2_VALUE INV_ITEM_ID,
fadf.ATTACHED_DOCUMENT_ID, fadf.FILE_NAME, fl.FILE_CONTENT_TYPE,
dbms_lob.getlength(fl.FILE_DATA) Image_Length_before
,xxx_mob_getbase64String(fl.FILE_DATA) Image_Length_after_encoding
,dbms_lob.substr(xxx_mob_getbase64String(fl.FILE_DATA),4000,1) coL1
,dbms_lob.substr(xxx_mob_getbase64String(fl.FILE_DATA),4000,4001) col2
,dbms_lob.substr(xxx_mob_getbase64String(fl.FILE_DATA),4000,8001) col3
,dbms_lob.substr(xxx_mob_getbase64String(fl.FILE_DATA),4000,12001) col4
,dbms_lob.substr(xxx_mob_getbase64String(fl.FILE_DATA),4000,16001) COL5
,dbms_lob.substr(xxx_mob_getbase64String(fl.FILE_DATA),4000,20001) col6
from
FND_ATTACHED_DOCUMENTS fad,
FND_LOBS fl
(select distinct attached_document_id ,file_name, media_id from FND_ATTACHED_DOCS_FORM_VL) fadf,
where fad.ATTACHED_DOCUMENT_ID = fadf.ATTACHED_DOCUMENT_ID
and fl.FILE_NAME = fadf.FILE_NAME
AND FAD.ENTITY_NAME = ‘MTL_SYSTEM_ITEMS’
AND FAD.PK1_VALUE = ‘204’
AND FAD.PK2_VALUE = ‘230963’
and fadf.media_id = fl.FILE_ID;

I attached the xml data, rtf for your info… just you can check if I’m doing the right thing. COL1-COL6 makes up one image.

thanks,
Ayu Ross

Like

Hi Ayu,

You must concatenate these 6 column to 1 column first at the report builder, ex: the name of this concatenate field is PHOTO.
And then you can use the below script to display the images..

(fo:instream-foreign-object content-type=”image/jpg”)(xsl:value-of select=”.//PHOTO”/)(/fo:instream-foreign-object)

Note: Change the ‘(’ to ‘‘

I’ve sent u back the XML file that I’ve been modified to concatenate the image column and has successfully generated the output to PDF and also the RTF file to display the image. Please check on your email.

Thanks,
Edi

Like

Hi Edi,

I have uploaded a small jpg picture to a sales order line in Oracle applications. I have also created the function getbase64.

Report rdf query is :

select file_id ,getbase64(file_data) photo
from apps.fnd_lobs where file_id = 31574846

result :
31574846 (HUGECLOB)

I have not uploaded the template yet but XML is failing to generate output. Following is th error :

“The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

——————————————————————————–

The following tags were not closed: XXHT_BLOB_TEST, LIST_G_FILE_ID. Error processing resource ‘http://appsc05t01.actuant.pr…

Orace ERP version : 11i
database version : 10.1.0.4.2
reports version : 6.0.8.28.0
XML Pub Version : 5.6.2

Please advise and thanks in advance.

Gopi

Like

Thanks Edi for you response.

Yes the report is showing clob when I run it from desktop.
But the issue is when I register the report as cocurrent preogram(XML), it is not generating XML tags.

Thanks in advance.
Gopi

Like

Dear Edi

Thanks for this great valuable thing.

I have created function in apps schema (I am Using R12)

CREATE OR REPLACE 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;

and SQL is
select
parent_id, getbase64(image) IMAGE
from per_images

I have created rtf
but when I run it
below error shows
ConfFile: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config\xdoconfig.xml
Font Dir: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts
Run XDO Start
Template: C:\Documents and Settings\muhammad.rafique\Desktop\pdf\eyblob\per_images.rtf
RTFProcessor setLocale: en-us
FOProcessor setData: C:\Documents and Settings\muhammad.rafique\Desktop\pdf\eyblob\per_image.xml
FOProcessor setLocale: en-us
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.charAt(Unknown Source)
at oracle.apps.xdo.common.encoding.Base64Util.decode(Unknown Source)
at oracle.apps.xdo.template.fo.elements.FOInstreamForeignObject.end(Unknown Source)
at oracle.apps.xdo.template.fo.FOHandler.endElement(Unknown Source)
at oracle.apps.xdo.common.xml.XSLTHandler$EEEntry.sendEvent(Unknown Source)
at oracle.apps.xdo.common.xml.XSLTMerger.startElement(Unknown Source)
at oracle.xml.parser.v2.XMLContentHandler.startElement(XMLContentHandler.java:167)
at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1182)
at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:301)
at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:268)
at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:149)
at oracle.apps.xdo.template.fo.FOProcessingEngine.process(Unknown Source)
at oracle.apps.xdo.template.FOProcessor.generate(Unknown Source)
at RTF2PDF.runRTFto(RTF2PDF.java:708)
at RTF2PDF.runXDO(RTF2PDF.java:520)
at RTF2PDF.main(RTF2PDF.java:305)

Kindly guide me in this regards,

Regards,
Muzafar

Like

Dear Muzafar,

You have error “java.lang.StringIndexOutOfBoundsException: String index out of range: -1” but I must look deeply, please send me your files to edi.y4nto@gmail.com.

Thanks,
Edi Yanto

Like

Edi, If the clob is greater that 40 KB, the function getbase64 is failed to convert those to blob. Any idea to shrink the size or any alternative to display clob which are more than 40kb?
Thanks in advance,
Vijay
vijay.k.nalam@sherwin.com

Like

Hi Vijay,

You can try to resize your images size or try the steps on comment #93 until #97 .

Thanks,
Edi Yanto

Like

Hi Edi,

I am trying to have an excel output for my xml containing images. The template used is RTF. The pdf output is fine for both base64 data as well as URL but in the excel output the images are not embedded in cells. The sizing does not scale itself to the excel cell size. Any suggestions..

Regards,
Siva k

Like

Hi Siva,

Images on excel output are not scale itself, so you must change the image size according to what you want.
More info about excel limitations you can find them at https://blogs.oracle.com/xmlpublisher/entry/excel_limitations.

Thanks,
Edi Yanto

Like

Hi Edi,
I am trying to incorporate signature and logo for check printing template in R12. I have converted the blob to clob using getbase64 function and also created extended xml element for signature and logo from database tables. I am getting the xml where the signature and logo have the encoded image feched, but not able to display them on rtf. giving lot of xdo.parser error. It is very urgent, and I am not finding any way. Can you please help me…
Mandi

Like

Hi Mandi,

Have you tried the steps on my blog? Please send me your RTF, XML and RDF files to my gmail (edi.y4nto@gmail.com), I’ll take a look when I’m free.

Thanks,
Edi Yanto

Like

Hi Edi,

In the RDF I have written a query to fetch data from fnd_lobs table and converting the BLOB column using the function conv_2base64 .My query is something like this “select conv_2base64 (fl.file_data) file_data from fnd_lobs where fl.file_id =22990626” . In the RTF in the form field i have used the syntax

But when I am executing the report in Oracle 11i EBS its erroring out with the message “signal 11”.The XML version is 5.6.3 .
Any suggestion for the issue.

Thanks
Jaydeep

Like

Hi Jaydeep,

The function conv_2base64 is to encode BLOB to CLOB? please run the report on your local report builder, anything wrong?

Thanks,
Edi Yanto

Like

Hi Edi,
Yes the function conv_2base64 is to encode BLOB to CLOB.It does the same as mentioned above.
dbms_lob.createtemporary(L_clob,false,0)
wf_mail_util.encode(p_blob,l_clob).
.In the report builder if i execute it gives some Image processor exception.

Please advise.

Thanks and Regards
Jaydeep

Like

Hi Jaydeep,

You already have the image processor exception, please correct this problem first.
If the report run well it will generate the XML file sucessfully when u choose the report builder to generate to this format.
Please test the the below scenario:
1. create table with BLOB data type column
2. Insert images to this BLOB column
3. Create report builder and enclode the BLOB to CLOB use the function above.

Is it run successfully?

Thanks,
Edi Yanto

Like

Hi Edi.

I too am getting SIGNAL 11 (when running RDF in 11.5.10.2). I am using your getbase64 function and it all compiles ok in Report Builder (6.0.8.25.0). When I view report output it tells me that there are some tags not closed (last one being the group containning the image field I have converted).

Can you shed any light as to why this may be? The report executed fine before I added a new query and group to include the image.

many thanks!
Allison

Like

Hi Allison,

can you provide the report files before and after you added the image? also the XML output files to edi.y4nto@gmail.com

Thanks,
Edi Yanto

Like

Hi Edi,

I want to display student photos on a report in PeopleSoft Campus Solutions, using PeopleSoft Query and XML Publisher. The back end is an SQL Server 2008 database. Is there an easy way to achieve this?

Thanks,
ST

Like

Hi Edi,

Kindly let me know the detailed process for retrieving images stored as BLOB in an SQL Server 2008 database. I want to display the image on an XML Publisher report for PeopleSoft Campus Solutions. Your help will be very much appreciated.

Thanks,
ST

Like

Hi ST,

The main point here is you should encode the BLOB (Binary Large Object) which is a collection of binary data stored as a single entity in DBMS to CLOB (Character Large Object), collection of character data in DBMS using the encode blob procedure. I’m not sure this package procedure Wf_Mail_Util.EncodeBLOB available on your SQL Server.

Thanks,
Edi Yanto

Like

HI PLS DS IS VERY URGENT…CAN U PLS STATE D STEPS INVOLVED IN INCLUDING IMAGES ON DATA DEFINITION TEMPLATE AND THE RTF TEMPLATE TOO FOR XML PUBLISHER. I HAVE MY PICTURES IN THE DATABASE BUT I DON’T KNOW HOW TO WRITE THE TAG IN MY DATA STRUCTURE OF THE DATA DEFINITION AND HOW DO I PUT IT ON THE RTF TEMPLATE. TANKS

Like

Hi Busola,

You can follow the steps on this video links (http://www.youtube.com/watch?v=G3wfbXnyQcc&feature=related and http://www.youtube.com/watch?v=cpS2DK_4p1I&feature=relmfu) to create the template and how to register it.

Thanks,
Edi Yanto

Like

This is a great site, thanks Edi. I followed the directions, created encoding function, Oracle Reports code and rtf template but stuck with Oracle Reports truncating the CBOB in the XML output. If I manually edit the XML file and correct the CLOB, everything works fine. I run Oracle Reports in R12 EBS environment. Any idea?

Like

Hi Runer,

Maybe the BLOB’s size is too large, try to resize the BLOB object and test it again.

Thanks,
Edi Yanto

Like

Hello Edi,

I’m having trouble viewing my image (BLOB) in my report.

Field image is large and there is no picture.

Thanks,
Igor

Like

Hi Edi,
I solved my problem.
The problem was the code into rtf field.
I used :”IMAGE”
The corret is: “.//IMAGE”

Excellent post !!!

Thanks

Like

Hi Igor,

U’re welcome..

Thanks,
Edi Yanto

Like

Hi Edi,
Thanks a lot for the informative article.I followed the steps suggested by you and achieved printing the image on pdf report but the report is working fine when I am running the report for one student.While running the report for several students (There are around 6000 students),the report is not getting published at all and page is getting redirected to the log in page.Can you please help me out..

Thanks,
Amit

Like

Hello Edi,

I am able to display signature on PDF using XML publisher. Now the issue I am facing is that signature Font is too small. To display image on RTF Template I used the following code:

Can you please assist how to increase font size of the signature so that it should appear big on PDF.

Thanks,
Ekta Midha

Like

Hi Ekta,

I can’t see your code here, may be u can create new signature image with bigger font size and attach it to the template.

Thanks,
Edi Yanto

Like

Thanks for the quick response Edi!!

Oh!! sorry My mistake,
Actually, I am using Mobile Apps where in one of the screen I am capturing the Customer Signature and storing it as BLOB in Oracle table column. Now, in XML Data Definition file, I am just using select query to retrieve that signature column and on RTF file, I am using below Code to show signature.

Now the issue is that when I view this signature on PDF, it size is too small. My query is how too increase font on RTF template file.

Note: I am mailing you the code which I have implemented along with the signature snapshot as might be it would help you in understanding what I am trying to explain above.

Thanks,
Ekta Midha

Like

Opps, its not saving the code. No problem please check you mail as I am sending my patch of code.

BTW, I have picked your email Id from above chats i.e. edi.y4nto@gmail.com.

Thanks,
Ekta Midha

Like

Hi Ekta,

You can try to resize the PNG image bigger and storing to your BLOB table column, or maybe you can try this below code:
(fo:instream-foreign-object content type=”image/png” height=”1 in” width=”2 in”)
(xsl:value-of select=”.//CUST_SIGNATURE”/)
(/fo:instream-foreign-object)

Thanks,
Edi Yanto

Like

could plz send me rtf template for this ? i am getting error for converting rtf to pdf.

Like

Hi Arka,

you can get the template and sample data from this link https://ediyanto83.files.wordpress.com/2011/07/eyblob-zip.odt (remove the extension .odt)

Thanks,
Edi Yanto

Like

Hi Edi

Thanks Edi for your help.I resolved this.
Now i am facing another issue. In case of my image size is more than 5 kb my xml publisher could not generate the xml file through plsql code .
I am sending you a mail in your gmail account which contains the details.

Regards,
Arka

Like

Hi Arka,

May I know the real image size that more than 5KB?
Try to resize the image and test it again.

Thanks,
Edi Yanto

Like

Hi Edi,

I checked it multiple times. yes,the real image size is more than 5 kb , it faces problem. 5 kb image is smaller in dimension than your test image.

Thanks

Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

May 2009
M T W T F S S
« Apr   Jun »
 123
45678910
11121314151617
18192021222324
25262728293031

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: