Wednesday, April 28, 2010

Solution to Oracle BI Publisher java.lang.NullPointerException


Hey guys. I have been using Oracle BI Publisher for some days now. It is a very powerful tool if you know how to use it. For some days or should I should say months, I have been getting a very irritating error when ever I bursted a report.

The Report would run very nicely, and would give the outputs whenever I click on the view button, but when we go ahead and use the bursting option I used to get the following error,

oracle.apps.xdo.servlet.scheduler.ProcessingException: java.lang.NullPointerException
        at oracle.apps.xdo.servlet.scheduler.XDOJob.runBurstingReport(XDOJob.java:2116)
        at oracle.apps.xdo.servlet.scheduler.XDOJob.execute(XDOJob.java:358)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: java.lang.NullPointerException
        at oracle.apps.xdo.servlet.scheduler.XDOJob.runBurstingReport(XDOJob.java:1905)
        ... 3 more
This is caused because of the incorrect use of the ?for-each-group? in the RTF template.
Lets say, we have the following things,
  1. A report with, Data Model named Billing. This data model can be of any Type i.e. SQL Query or Data Template etc.
  2. An RTF template named sampletemp.rtf.
  3. BI Publisher plugin for MS. Word.
  4. A little Patience.
What I want to do is write a text "Hello " followed by a field viz. SBF_DP_ID

Create the Data Model in BI Publisher and click on save.

Start MS Word and login to the BI Publisher Server, using the appropriate credentials. Go to the BI Publisher menu and go to Insert> Field . Insert some fields, say for eg. SBF_DP_ID.
Select this inserted field and go to the BI Publisher Menu >Insert>Repeating Group

Here you would see three tabs viz. Properties, Sorting and Advanced.
Inside the Properties tab, you have two fields For Each and group By, both of them are drop downs. Also there are two check boxes provided.
Here comes the important part. When you expand the Dropdown against the For Each field, you will see two options, viz. Billing and BILLING_ROW. You have to select the BILLING_ROW as the repeating For-Each option and not Billing.

Note : The names Billing and BILLING_ROW will appear only if you have named the Data Model as Billing. Incase you have entered another name, the name of the options will be according to the name of your Data model. You have to select the _ROW option and not the option.

Next, select the Group By field that you would want to group by. In this example SBF_DP_ID since I want my bills to be generated for each DP_ID.
Use the Sorting tab to use sorting on your data (not required if you have taken care of it in the Data model)
And finally click on OK.

Thus you have made a repeating group. You will get a field named F and another field named E. The F stands for the Beginning of the For-Group and E stands for the End of the For Group, i.e. the for each group will be applicable only to the elements between the F and E fields respectively.

Note: The field names 'F' and 'E' can be changed to whatever you want by going in the Advanced tab.

Next, again select the field SBF_DP_ID and go to the BI Publisher Menu>Insert>Repeating group.
This time you will see that the For Each Drop Down is disabled and has a default value Group Item. Here set the Group By option again to SBF_DP_ID (or any other repeating group of your choice). Again set the Sorting options via the Sorting tab.

So Finally you will get the following in the template

F  F  Hello SBF_DP_ID  E  E  

Save and Upload the Template and enter the Bursting Query, and select the Split by and Deliver by elements.

Note : Make sure that the column, that you have selected as the KEY doesnt have any null values. Also the Deliver By Element and KEY should be the same
And cross your fingers!!

Hope this came in handy, for someone. I struggled a lot, and had to do a lot of trial and error in order to find out what was going wrong.

Feel free to contact me if you are still getting the dreaded java.lang.NullPointerException in Oracle BI Publisher Report Bursting .

5 comments:

WorkForYou said...

Hi,
I am getting nullpointerexception only when using SFTP. I applied a patch for this but still get the error. Do you know what may be causing this?

Harry Paddy said...

I dont think OBI Publisher supports SFTP. If it does, i have never used it.

Unknown said...

Hi Harry,

im still getting same error java.lang.NullPointerException
for a bip report which is scheduled.
the problem here is it is getting to trriger a data model.can u plz suggest me.

thanks
kumar

keisy said...

Hi! Did you resolve the issue on bip report scheduled?

Hitesh said...

Hi Harry,

I have a report which is pulling the data with the help of a DB package. This DB package has to join two table which has 250+ Mn rows each so taking about an hour to prepare the data. But the report start publishing below error within 30 minutes of executing the report:

[2017-11-15T10:00:01.722+00:00] [AdminServer] [WARNING] [] [oracle.xdo] [tid: 16] [userId: BISystemUser] [ecid: 0000LyixsDF7q2lMwuedMG1Q1ypd000002,0] [APP: bipublisher#11.1.1] java.lang.NullPointerException[[
at oracle.xdo.dataengine.datasource.PlsqlFunctionCall.getRemoteValue(PlsqlFunctionCall.java:161)
at oracle.xdo.dataengine.DataHandler.getRemoteValue(DataHandler.java:976)
at oracle.xdo.dataengine.DataHandler.getBindValue(DataHandler.java:371)

Can you please help me out here.

Thanks,
Hitesh