My Oracle Support Banner

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 MAY 11, 2018

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.

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


Parameter name: TRAX_DATE oracle.xdo.servlet.data.DataException: ORA-00936: missing expression ; Data source: ICMTEST; Query: select /* dmtraceid=lv_plan_Trax_dat */ /*+ 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


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

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.