oracle.xdo.XDOException: java.sql.SQLDataException: ORA-01843: not a valid month in BI Publisher

Whenever we use TO_DATE in BIP Data Model (Consider the sample below query) – You will receive an exception.

SELECT 

GLD.FROM_CURRENCY ,
GLD.TO_CURRENCY,
GLD.CONVERSION_DATE,
GLD.CONVERSION_TYPE,
GLD.CONVERSION_RATE

FROM GL_DAILY_RATES GLD  
WHERE 1=1
and GLD.FROM_CURRENCY  ='USD'
AND GLD.TO_CURRENCY ='INR'
AND CONVERSION_TYPE ='Corporate'
AND CONVERSION_DATE = TO_DATE ( '18-APR-2021','DD-MON-YYYY')

Solution :

TO_DATE ( ’18-APR-2021′,’DD-MON-YYYY’ ,’NLS_DATE_LANGUAGE = American’)

SELECT 

GLD.FROM_CURRENCY ,
GLD.TO_CURRENCY,
GLD.CONVERSION_DATE,
GLD.CONVERSION_TYPE,
GLD.CONVERSION_RATE

FROM GL_DAILY_RATES GLD  
WHERE 1=1
and GLD.FROM_CURRENCY  ='USD'
AND GLD.TO_CURRENCY ='INR'
AND CONVERSION_TYPE ='Corporate'
AND CONVERSION_DATE = TO_DATE ( '18-APR-2021','DD-MON-YYYY'  ,'NLS_DATE_LANGUAGE = American')

Comments |0|

Legend *) Required fields are marked
**) You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
Category: OracleSaaS