OBIEE 11g Date Format Issues With Date Presentation Variable Error ORA-01861 Literal Does Not Match Format String At OCI Call OCIStmtFetch (Doc ID 1474844.1)

Last updated on JULY 05, 2023

Applies to:

Symptoms

Have a Date prompt that sets a presentation variable (say varDueDate). When this presentation variable is used in a report, here is how it behaves:

1. @{varDueDate} ===> 01/01/2011
2. '@{varDueDate}' ===> 2011-01-01

We are using on connect command to set NLS_DATE_FORMAT to 'DD/MM/YYYY' . Date calendar recognizes this. Even the variable is recognizing it correctly. But when we try to use this presentation variable in a report, we are casting it as date as this can be compared against actual date column (Eg., "Fact Sales"."Due Date" < cast('@{varDueDate}{1/1/2012}' as date)

This was working fine in 10g. Now, in 11g its giving the below error, as the presentation variable is automatically converted to 'YYYY-MM-YY' format when given inside quotes. We tried removing the quote, but the expression manager would not allow it. Please advice a solution.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 1861, message: ORA-01861: literal does not match format string at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.