BLOB Images in XML Publisher
Posted on: May 21, 2009
- In: Oracle
- 140 Comments
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:
140 Responses to "BLOB Images in XML Publisher"
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)
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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:
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
LikeLike
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 🙂
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
February 19, 2010 at 5:20 AM
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?
LikeLike
February 24, 2010 at 5:27 AM
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!
LikeLike
February 25, 2010 at 1:13 AM
@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
LikeLike
February 25, 2010 at 8:40 PM
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
LikeLike
March 1, 2010 at 1:10 AM
Hi Sarfraz,
You can send me your files at my gmail id edi.y4nto@gmail.com
Thanks,
Edi
LikeLike
March 11, 2010 at 4:17 AM
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
LikeLike
March 11, 2010 at 5:23 PM
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
LikeLike
March 12, 2010 at 6:24 AM
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.
LikeLike
March 12, 2010 at 6:27 AM
Missed to type the code:
This works in a field not in a footer
LikeLike
March 12, 2010 at 8:21 AM
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
LikeLike
March 14, 2010 at 11:30 PM
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
LikeLike
March 16, 2010 at 5:55 AM
Hey Edi,
Any idea on the issue I raised ??
If anyone has an idea please let me know
Regards,
Rekha
LikeLike
March 17, 2010 at 2:52 AM
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
LikeLike
March 16, 2010 at 9:45 PM
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
LikeLike
March 17, 2010 at 1:35 AM
Hello Edi,
I am currently using Oracle 10g, but couldn’t find the package procedure Wf_Mail_Util.EncodeBLOB.
Annie
LikeLike
March 17, 2010 at 3:57 AM
Edi,
I have sent the details and files to your gmail id.Kindly check and advise.
Thanks,
Rekha
LikeLike
March 18, 2010 at 11:07 PM
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
LikeLike
March 30, 2010 at 6:39 AM
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
LikeLike
March 31, 2010 at 11:35 PM
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
LikeLike
April 1, 2010 at 2:51 AM
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
LikeLike
April 1, 2010 at 5:40 AM
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
LikeLike
April 2, 2010 at 7:07 AM
Hey Edi,
I understood and it worked perfectly 🙂 Lods of thanks.
LikeLike
April 4, 2010 at 11:08 PM
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
LikeLike
April 13, 2010 at 6:32 AM
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)
LikeLike
April 14, 2010 at 6:45 PM
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
LikeLike
April 15, 2010 at 4:08 AM
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
LikeLike
April 15, 2010 at 4:47 AM
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
LikeLike
April 15, 2010 at 8:58 PM
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
LikeLike
April 16, 2010 at 3:39 AM
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
LikeLike
April 18, 2010 at 7:02 PM
Hi Rekha,
you can find the if elseif condition on my previous comment..
Thanks,
Edi
LikeLike
April 20, 2010 at 5:26 AM
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
LikeLike
April 20, 2010 at 6:38 AM
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
LikeLike
April 21, 2010 at 1:20 AM
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 ../../
LikeLike
April 21, 2010 at 1:33 AM
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
LikeLike
April 21, 2010 at 2:18 AM
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
LikeLike
April 21, 2010 at 3:52 AM
Hey Edi,
Awesome 🙂 You Rock.. !!! ancestor tag works perfectly for my scenario.
Thanks a lot.Keep Rocking!!
LikeLike
April 22, 2010 at 7:41 AM
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
LikeLike
April 22, 2010 at 5:35 PM
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
LikeLike
May 21, 2010 at 6:24 AM
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
LikeLike
May 21, 2010 at 6:26 AM
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
LikeLike
June 14, 2010 at 6:07 AM
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.
LikeLike
June 14, 2010 at 8:11 PM
@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
LikeLike
June 14, 2010 at 10:30 PM
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
LikeLike
June 15, 2010 at 1:02 AM
Hi Arun,
You can find them at e-business suite database, because they may have other dependency objects.
Thanks,
Edi
LikeLike
June 15, 2010 at 11:22 PM
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??
LikeLike
July 12, 2010 at 1:26 AM
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.
LikeLike
July 26, 2010 at 3:41 AM
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,
LikeLike
July 26, 2010 at 10:48 PM
Hi Rafi,
Have you encode the BLOB to CLOB for the images?
Thanks,
Edi
LikeLike
July 26, 2010 at 11:42 PM
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.
LikeLike
July 30, 2010 at 2:37 AM
Hi Mrafi,
You must encode the BLOB (images) to CLOB use this above function.
Regards,
Edi
LikeLike
July 31, 2010 at 10:30 PM
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,
LikeLike
August 9, 2010 at 11:11 PM
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
LikeLike
August 10, 2010 at 12:53 AM
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
LikeLike
August 31, 2010 at 5:07 AM
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
LikeLike
September 1, 2010 at 8:10 PM
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
LikeLike
September 3, 2010 at 6:17 AM
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
LikeLike
September 21, 2010 at 6:08 AM
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
LikeLike
September 22, 2010 at 6:34 PM
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
LikeLike
September 25, 2010 at 1:51 AM
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
LikeLike
October 18, 2010 at 11:46 AM
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
LikeLike
October 18, 2010 at 5:49 PM
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
LikeLike
October 19, 2010 at 5:41 AM
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
LikeLike
October 20, 2010 at 1:18 AM
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
LikeLike
October 20, 2010 at 5:19 AM
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
LikeLike
October 20, 2010 at 7:52 PM
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
LikeLike
October 21, 2010 at 10:32 AM
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
LikeLike
November 10, 2010 at 4:12 PM
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.
LikeLike
November 13, 2010 at 6:36 AM
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
LikeLike
November 15, 2010 at 6:04 AM
Of course, what a great site and informative posts, I will add backlink – bookmark this site? Regards,
Reader.
LikeLike
January 22, 2011 at 6:44 PM
I’m looking information about this from last friday, thanks!
LikeLike
March 10, 2011 at 6:33 PM
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
LikeLike
March 10, 2011 at 6:34 PM
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
LikeLike
March 10, 2011 at 8:47 PM
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
LikeLike
March 10, 2011 at 8:51 PM
Hi Rekha,
You can write the full tag like /Outboundpayment/Bankdetails/Bankamount. Is it solve your problem?
Thanks,
Edi
LikeLike
March 21, 2011 at 9:26 AM
Hi Yanto,
I created the XML as below in R12 by the function getbase64:
Qk02uAsAAAAAADYAAAAoAAAAgAIAAJABAAABABgAAAAAAAC4CwAAAAAAAAAAAAAA
AAAAAAAA////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
Could you help me?
LikeLike
June 1, 2011 at 3:07 AM
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
LikeLike
June 1, 2011 at 3:09 AM
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
LikeLike
June 1, 2011 at 3:12 AM
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
LikeLike
June 1, 2011 at 3:15 AM
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
LikeLike
June 1, 2011 at 3:20 AM
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
LikeLike
June 24, 2011 at 3:39 PM
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
LikeLike
June 27, 2011 at 10:46 AM
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
LikeLike
January 24, 2012 at 7:56 PM
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
LikeLike
January 25, 2012 at 12:53 PM
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
LikeLike
March 6, 2012 at 2:39 AM
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
LikeLike
March 6, 2012 at 12:14 PM
Hi Vijay,
You can try to resize your images size or try the steps on comment #93 until #97 .
Thanks,
Edi Yanto
LikeLike
April 11, 2012 at 8:38 AM
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
LikeLike
April 11, 2012 at 2:02 PM
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
LikeLike
May 17, 2012 at 8:58 PM
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
LikeLike
May 21, 2012 at 3:52 PM
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
LikeLike
June 13, 2012 at 6:00 PM
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
LikeLike
June 14, 2012 at 9:53 AM
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
LikeLike
June 16, 2012 at 2:28 PM
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
LikeLike
June 18, 2012 at 2:46 PM
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
LikeLike
July 26, 2012 at 1:18 PM
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
LikeLike
July 27, 2012 at 10:32 AM
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
LikeLike
October 10, 2012 at 7:40 PM
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
LikeLike
October 12, 2012 at 1:46 AM
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
LikeLike
October 16, 2012 at 1:23 PM
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
LikeLike
October 31, 2012 at 10:14 PM
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
LikeLike
November 19, 2012 at 3:39 PM
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
LikeLike
April 17, 2013 at 7:11 AM
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?
LikeLike
April 17, 2013 at 3:36 PM
Hi Runer,
Maybe the BLOB’s size is too large, try to resize the BLOB object and test it again.
Thanks,
Edi Yanto
LikeLike
May 20, 2013 at 7:03 AM
Hello Edi,
I’m having trouble viewing my image (BLOB) in my report.
Field image is large and there is no picture.
Thanks,
Igor
LikeLike
May 20, 2013 at 7:24 AM
Hi Edi,
I solved my problem.
The problem was the code into rtf field.
I used :”IMAGE”
The corret is: “.//IMAGE”
Excellent post !!!
Thanks
LikeLike
124 | PeopleSoft Trainer
January 17, 2014 at 4:18 PM
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
LikeLike
February 10, 2014 at 5:26 PM
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
LikeLike
February 11, 2014 at 10:16 AM
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
LikeLike
February 11, 2014 at 12:19 PM
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
LikeLike
February 11, 2014 at 12:22 PM
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
LikeLike
February 11, 2014 at 2:22 PM
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
LikeLike
July 8, 2014 at 5:50 PM
could plz send me rtf template for this ? i am getting error for converting rtf to pdf.
LikeLike
July 9, 2014 at 10:35 PM
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
LikeLike
July 17, 2014 at 2:50 AM
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
LikeLike
July 17, 2014 at 11:37 AM
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
LikeLike
July 17, 2014 at 2:38 PM
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
LikeLike
July 2, 2019 at 5:09 PM
ConfFile: C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config\xdoconfig.xml
Font Dir: C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts
Run XDO Start
Template: D:\Id.rtf
RTFProcessor setLocale: en-us
FOProcessor setData: C:\Users\hp\Desktop\MODULE_XML.xml
FOProcessor setLocale: en-us
java.lang.StringIndexOutOfBoundsException: String index out of range: 62487
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)
LikeLike
July 3, 2019 at 10:44 AM
Dear Jacobs,
You have an error on “java.lang.StringIndexOutOfBoundsException: String index out of range: 62487”. Can you send me your MODULE_XML.xml file?
Thanks,
Edi Yanto
LikeLike
July 17, 2019 at 2:36 AM
Hi Edi,
I am using below code to display the BLOB images for each line.
It is displaying images perfectly, but the issue is when there is null value, it is occupying whole page for each line of data. How can i solve this problem? Thanks in Advance.
LikeLike
July 23, 2019 at 9:48 AM
Hi Pinky,
I can’t see the code… please send me your rtf and xml to edi.y4nto@gmail.com
Thanks,
Edi Yanto
LikeLike
August 16, 2019 at 10:16 PM
The blob image quality for logo is getting reduced while calling from the database to rtf template so to keep the quality is there any way.
Also if we have table header with one color can we change to another color using any condition?
Thanks
John
LikeLike
March 24, 2022 at 4:01 PM
Dear all , i am facing the issue to display the BLOB image to XML Template , from the database image is properly displaying , but when i generate the XML file from RDF , and when i load the XML file to RTF template . then below error message is being displayed .
ConfFile: C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config\xdoconfig.xml
Font Dir: C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts
Run XDO Start
Template: C:\Users\Administrator\Documents\QRCODE\testreport.rtf
RTFProcessor setLocale: en-us
FOProcessor setData: C:\Users\Administrator\Desktop\xml\XXAR_SAMPLE_FINAL.xml
FOProcessor setLocale: en-us
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.charAt(Unknown Source)
Regards ,
Zulqarnain
LikeLike
Edi Yanto (何 萬 新) 
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
LikeLike