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')