BI Publisher 11.1.1.9 Users Receive Error 'ORA-00936: missing expression' and 'Oracle.xdo.servlet.data.DataException' When Selecting Parameters For BI Publisher Reports (Doc ID 2177787.1)

Last updated on SEPTEMBER 16, 2016

Applies to:

BI Publisher (formerly XML Publisher) - Version 11.1.1.9.x and later
Information in this document applies to any platform.

Symptoms

Business Intelligence Publisher 11.1.1.9.x, Enterprise Administration issues

 



Users receive the following error when choosing parameters on BI Publisher Reports:

oracle.xdo.servlet.data.DataException

The oracle.xdo.servlet.data.DataException is received when choosing parameters on a BI Publisher Report after the 11.1.1.9.160119 upgrade.
This was working fine for many years on the previous version 11.1.1.7
Have attached screenshots showing the problem with 1 report .

The complete error message when choosing a parameter with name Transaction date is as follows

ERROR
-----------------------


Parameter name: TRAX_DATE oracle.xdo.servlet.data.DataException: ORA-00936: missing expression ; Data source: ICMRTRA; Query: select /* dmtraceid=lv_plan_Trax_dat_160509_104120 */ /*+ MONITOR */ case when is null then to_char(add_months (sysdate, -1), 'MON-YY') else end trax_date from dual

The exact sql query for the parameter TRAX_DATE (which is the parameter Transaction date )as defined in the data model was just

select case when :pAdhoc_month is null then to_char(add_months (sysdate, -1), 'MON-YY') else :pAdhoc_month end trx_date from dual

pAdhoc_month is an optional Text parameter which users can enter sometimes as MAY-16 etc

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
This seems to be impacting all reports which use similar logic for parameter derivation

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms