BI Publisher 126.96.36.199 Users Receive Error 'ORA-00936: missing expression' and 'Oracle.xdo.servlet.data.DataException' When Selecting Parameters For BI Publisher Reports
Last updated on MAY 11, 2018
Applies to:BI Publisher (formerly XML Publisher) - Version 188.8.131.52.x and later
Information in this document applies to any platform.
Business Intelligence Publisher 184.108.40.206.x, Enterprise Administration issues
Users receive the following error when choosing parameters on BI Publisher Reports:
The oracle.xdo.servlet.data.DataException is received when choosing parameters on a BI Publisher Report after the 220.127.116.11.160119 upgrade.
This was working fine for many years on the previous version 18.104.22.168.
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
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