Thursday, December 13, 2007

Null XML elements and no NVL function BI Publisher RTF? Try xdofx decode

Where did that nvl() function go when creating BI Publisher Templates?

Lets say you've got this data:

<?xml version="1.0" encoding="UTF-8"?>
<rowset>
<row>
<g1>1</g1>
<g2></g2>
<g3>3</g3>
<g4></g4>
</row>
</rowset>

And in your template you put:

Sum: <?G1+G2+G3+G4?>

Uh-oh, you get the lovely number NaN or a nice long java error including:

Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert  to number.

What to do? Well, you could rightly put nvl(G1,0) functions around whatever SQL query is generating your XML data, but if not then where's the nvl in BI Publisher lingo? Oh here it is:

Sum: <?xdofx:decode(G1,'',0,G1) + decode(G2,'',0,G2) + decode(G3,'',0,G3) + decode(G4,'',0,G4)?>

Well, not that lovely after all, but if it works ...

10 comments:

Tim Dexter said...

Hey Gareth
You also have the more compact XPATH expression.Now 'blogger' thinkks Im writing some scripting attack on the server if I put the complete expression in so hopefully you get the idea
< ? G1 [ . ! = '' ] + ...? >
Saves some space in those precious formfields.
Tim

Gareth said...

Hi Tim,
I can't get the xpath syntax working for me ... still getting NaN, my thinking is even with the xpath null check null + 1 is NaN. Drop me an email ggr888 [AT] gmail [D0T] com

Sem said...

It becomes my main resource for Oracle E-Business Suite.

thank you for your INFO

Sem

vpr said...

Gave like

Not working...it tr=hrows error...

plz send mail to vrajesh_apps@yahoo.com if got any clue

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: E:\Venkatesh\Shared_Venkatesh\Rajesh\17May10_Abstract_Report\Lease Abstract Report.rtf
RTFProcessor setLocale: en-us
FOProcessor setData: E:\Venkatesh\Shared_Venkatesh\Rajesh\17May10_Abstract_Report\Lease Abstract Report Torrance.xml
FOProcessor setLocale: en-us
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at oracle.apps.xdo.common.xml.XSLT10gR1.invokeProcessXSL(Unknown Source)
at oracle.apps.xdo.common.xml.XSLT10gR1.transform(Unknown Source)
at oracle.apps.xdo.common.xml.XSLT10gR1.transform(Unknown Source)
at oracle.apps.xdo.common.xml.XSLTWrapper.transform(Unknown Source)
at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(Unknown Source)
at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(Unknown Source)
at oracle.apps.xdo.template.FOProcessor.createFO(Unknown Source)
at oracle.apps.xdo.template.FOProcessor.generate(Unknown Source)
at RTF2PDF.runRTFto(RTF2PDF.java:708)
at RTF2PDF.runXDO(RTF2PDF.java:517)
at RTF2PDF.main(RTF2PDF.java:305)
Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number.
at oracle.xdo.parser.v2.XSLStylesheet.flushErrors(XSLStylesheet.java:1534)
at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:521)
at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:489)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:271)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:155)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:192)
... 15 more

Gareth said...

Hi VPR,

You're still trying to convert something to a number that won't work:
Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number.

Regards,
Gareth

Cindy Dy said...

Couldn't be written any better. Reading this post reminds me of my old room mate! He always kept talking about this. I will forward this article to him. Pretty sure he will have a good read. Thanks for sharing!


Macy
www.gofastek.com

Silvia Jacinto said...


I really love your blog there's a lot to share. Keep it up.Visit my site too.

n8fan.net

www.n8fan.net

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b

Shrikant Bhoir said...

Hi,

I have similar error. First I thought it is due to a null value so, I corrected it and verified from the XML output that all null values are taken care of. Having done this still the error persists. I am calculating running total using a variable like so:


I have 3 4 similar variables doing almost the same job. When I take out all these variable and run the concurrent program the output report is generated successfully.

Also, using the xml data output the report is able to run locally; the problem occurs when run thru concurrent program.

Shrikant Bhoir said...
This comment has been removed by the author.