FUNCTION mob_getbase64String( P_blob BLOB )
RETURN CLOB
IS
l_result CLOB;
BEGIN
DBMS_LOB.createtemporary(lob_loc => l_result, CACHE => FALSE, dur => 0);
Wf_Mail_Util.EncodeBLOB ( P_blob
, l_result
);
RETURN ( l_result );
END mob_getbase64String;
Sample Select:
from xxfnd_logo_images where file_id = :p_mlogo_id
Note: to_char has to be used for version of 5.6.2 of xml publisher, clobs are okay in 5.6.3
The syntax to add an image field to your format template is down below:
<fo:instream-foreign-object content-type="image/jpg">
<xsl:value-of select="BLOB_CONTENT"/>
</fo:instream-foreign-object>
20 comments:
Hi
I'm in very difficult with oracle BI Publisher!
I have create a report PDF whit Oracle BI Publisher.
I'm using blob2clobase64 function (to converto into base64) and I can see that this is doing the job when I test the query (both in SQL*Plus and from within APEX).
I download the XML and then imported it into Word using the BIP plugin - All fine.
My SQL is returning the blob2clobase64 result as column name THUMBNAIL (the blob column).
I have edited the properties of the THUMBNAIL field in my rtf document as follows...
fo:instream-foreign-object content-type="image/jpg"
xsl:value-of select="THUMBNAIL"
/fo:instream-foreign-object
When I try to generate a PDF, PDF will be produced but the thumbnail image does not display also if there is one image file of about 13Kb.
Can you help me?
Valeria
Valeria,
Please try changing the mime type to:
type="image/gif"
or try
type="image/tiff"
Also, verify that the your xml file has data in the THUMBNAIL field. If the THUMBNAIL field only exists once in the xml file change your syntax //THUMBNAIL.
Ike Wiggins
This is a good example though I found another example which served my purpose
http://blog.vmladenov.com/?p=310
I love this site! Thank you sooo much for all your posts!
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!
Try generating your xml in a data template. What version of BIP are you on.
Clobs are supported in reports6i. Now, with that said xml is supported output option but it doesn't mean it isn't "quirky." Furthermore, enhancement or bug requests for reports6i is not really supported. You could try and log an SR but good luck with that :-)
My advice, try it in a data template and see if you have the same problem. I don't think you will since a lot of people have been using blobs for sometime.
Thanks Ike for a prompt response,
Yes clobs are supported in 6i but as soon a try to call or use them in xml, trouble begins. i have tried every angle on this believe me...I am using BIP 5.6.3 desktop add-in to word. I have tried
1)register the rdf with clob column as a concurrent program with xml output....its errors out with message in my last post
2)i first converted the rdf to xml using the rwconverter utility. the xml generated from that is perfect...no errors it even shows the clob column and its properties etc....but when i run the report with xml ouput....same xsl error appears.....but as soon as i convert the clob to varchar2 it works fine! now varchar2 has its limitation of 4000 bytes so that too is not a viable solution.....any comments are welcome! i am using ebs 11i 11.5.10.2
Don't why my comment is not getting posted....you mean to say that i should develope the data template manually? Then register the data template definition and run the concurrent program with XDODTEXE as an executable?
can someone please tell me whats wrong with this template and why is application erroring out when i run this as a concurrent request.I have made a concurrent program with XML as the output and XDODTEXE as the executable....please help
and the error generated is
+---------------------------------------------------------------------------+
Human Resources: Version : 11.5.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
TDP_BLOB_CLOB module: TDP BLOB to CLOB XML
+---------------------------------------------------------------------------+
Current system time is 26-FEB-2010 16:03:39
+---------------------------------------------------------------------------+
XDO Data Engine Version No: 5.6.3
Resp: 53838
Org ID : 1041
Request ID: 38224060
All Parameters: review_id=
Data Template Code: TDP_BLOB_CLOB
Data Template Application Short Name: PER
Debug Flag: N
{review_id=}
Calling XDO Data Engine...
--SQLException
java.sql.SQLException: ORA-00911: invalid character
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584)
at oracle.apps.xdo.dataengine.XMLPGEN.processSQLDataSource(XMLPGEN.java:515)
at oracle.apps.xdo.dataengine.XMLPGEN.writeData(XMLPGEN.java:429)
at oracle.apps.xdo.dataengine.XMLPGEN.writeGroupStructure(XMLPGEN.java:300)
at oracle.apps.xdo.dataengine.XMLPGEN.processData(XMLPGEN.java:266)
at oracle.apps.xdo.dataengine.XMLPGEN.processXML(XMLPGEN.java:205)
at oracle.apps.xdo.dataengine.XMLPGEN.writeXML(XMLPGEN.java:237)
at oracle.apps.xdo.dataengine.DataProcessor.processData(DataProcessor.java:364)
at oracle.apps.xdo.oa.util.DataTemplate.processData(DataTemplate.java:236)
at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:293)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Executing request completion options...
+------------- 1) PUBLISH -------------+
Beginning post-processing of request 38224060 on node TLNT09 at 26-FEB-2010 16:03:45.
Post-processing of request 38224060 failed at 26-FEB-2010 16:03:45 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 26-FEB-2010 16:03:45
+---------------------------------------------------------------------------+
i am working on
Database Server
----------------------------------------
RDBMS : 11.1.0.7.0
Oracle Applications : 11.5.10.2
At last i did and still not working....i am not even to get a simple template to work
can someone please tell me whats wrong with this template and why is application erroring out when i run this as a concurrent request.I have made a concurrent program with XML as the output and XDODTEXE as the executable....please help
and the error generated is
+---------------------------------------------------------------------------+
Human Resources: Version : 11.5.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
TDP_BLOB_CLOB module: TDP BLOB to CLOB XML
+---------------------------------------------------------------------------+
Current system time is 26-FEB-2010 16:03:39
+---------------------------------------------------------------------------+
XDO Data Engine Version No: 5.6.3
Resp: 53838
Org ID : 1041
Request ID: 38224060
All Parameters: review_id=
Data Template Code: TDP_BLOB_CLOB
Data Template Application Short Name: PER
Debug Flag: N
{review_id=}
Calling XDO Data Engine...
--SQLException
java.sql.SQLException: ORA-00911: invalid character
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584)
at oracle.apps.xdo.dataengine.XMLPGEN.processSQLDataSource(XMLPGEN.java:515)
at oracle.apps.xdo.dataengine.XMLPGEN.writeData(XMLPGEN.java:429)
at oracle.apps.xdo.dataengine.XMLPGEN.writeGroupStructure(XMLPGEN.java:300)
at oracle.apps.xdo.dataengine.XMLPGEN.processData(XMLPGEN.java:266)
at oracle.apps.xdo.dataengine.XMLPGEN.processXML(XMLPGEN.java:205)
at oracle.apps.xdo.dataengine.XMLPGEN.writeXML(XMLPGEN.java:237)
at oracle.apps.xdo.dataengine.DataProcessor.processData(DataProcessor.java:364)
at oracle.apps.xdo.oa.util.DataTemplate.processData(DataTemplate.java:236)
at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:293)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Executing request completion options...
+------------- 1) PUBLISH -------------+
Beginning post-processing of request 38224060 on node TLNT09 at 26-FEB-2010 16:03:45.
Post-processing of request 38224060 failed at 26-FEB-2010 16:03:45 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 26-FEB-2010 16:03:45
+---------------------------------------------------------------------------+
i am working on
Database Server
----------------------------------------
RDBMS : 11.1.0.7.0
Oracle Applications : 11.5.10.2
For the field that is pulling the clob what do you have that defined as in the data template?
What version of BIP are you running?
What is the data type of field in oracle: Clob, blob, lob, long?
Turn on the debugging parameter in your data template to see what query is getting executed and to get more details about the error.
<property name="debug_mode" value="on"/>
What did you define the field data type as?
Please tell me your not appending the clob data....
Hi Ike,
I am uploading the url to screen shot of my data template...can you please take a look at it and tell me what is wrong with it....i have made it manually after taking the guide lines from developers guide and some suggestions from the SR i raised....i can't even get this simple data template to run from apps with XDODTEXE as the executable.....can you help and i posted it last time but your comment box won't show it :)
http://www.freepicsupload.com/pics/phpqufcZO-DataTemplateScreenShot.jpg
Lets start from the very begining and make it simple....it is the same data template as the one in my last post so when i run this data template it produces the same error as described in my previous post
Hi, I even tried with property=debug on, still the same error! what do you suggest?
sarfraz
Hey Ike,
Good news i did it :) Thanks mate for the debug parameter help....it finally gave me the clue to what was wrong.....if you must know...you would notice a semi-colon at the end of the query in my data template....that was the invalid character :D anyways i still have the complex report at my hand and CLOB to handle so will bother you for your expertise :D thanks once again....
Hi there Ike,
regarding the FUNCTION mob_getbase64String( P_blob BLOB )
i have some suggestions if you want to update this post its upto to you but i came across this scenario so i thought i will give people the heads up. I have having trouble using this funciton when BLOB column was null so a slight change i would suggest.
CREATE OR REPLACE FUNCTION mob_getbase64String( P_blob BLOB )
RETURN CLOB
IS
l_result CLOB;
BEGIN
if P_blob is not null then
DBMS_LOB.createtemporary(lob_loc => l_result, CACHE => FALSE, dur => 0);
Wf_Mail_Util.EncodeBLOB ( P_blob,l_result);
RETURN ( l_result );
else
return(null);
end if;
END mob_getbase64String;
Secondly,
The code for rendering the image in rtf template also needs to be changed slightly i.e
fo:instream-foreign-object content-type="image/jpg">
Depending on the image format you may have to change it. For your example you had to do that, for my example it was a gif....
fo:instream-foreign-object content-type="image/jpg
oh sorry i inserted a tag the full text didn't show i meant use
notice the './/' in the select="" clause....that is what i was trying to point out...thanks
Hi Wiggins,
I am trying to display a blob in rtf in a similar way you suggested. I am able to view the image in pdf, but gettting a blank page before and after the image file.
My rtf just has only one field with tag similar as follows,
i tried other image types(gif, png) also,image is getting displayed but with blank page.
Please assist me to resolve this issue.
Also Please let me know options to resize the image as required.
Thanks
Iyeswariya
Hi,
I am unable to access the signature for check printing.
Signature file (image) is not in Oracle apps server. It would be stored in some secured server.
I tried to access the image file through the command
url:{'${OA_MEDIA}/signature.gif'} it is working fine as the file is located in Apps server.
But this syntax is not working when the file is another server.
My file is located in the path: \\xxxx\zzzz\abc\signature.gif.
I am trying to access like url:{'\\xxxx\zzzz\abc\signature.gif'}.
But, it's not working. Please suggest me how to access the image.
Best Regards,
Ramesh.
Post a Comment