My Oracle Support Banner

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 AUGUST 09, 2018

Applies to:

Business Intelligence Suite Enterprise Edition - Version and later
Information in this document applies to any platform.
Checked for relevance on 24-Mar-2014


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.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.