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 ...

8 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