Analytics Software

Hi guys

I'm an oracle apps dba new to OBIEE, users are getting this error on the one environment ORA-01843: not a valid month at OCI call OCIStmtExecute:.
This issue happens when they run a job through the OBIEE dashboard, the data does get returned but this error occurs.

Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtExecute: select distinct D7.c7 as c1, D7.c8 as c2, D7.c6 as c3, D7.c5 as c4, D7.c9 as c5, D7.c10 as c6, D7.c4 as c7, D7.c3 as c8, D7.c11 as c9, D7.c2 as c10, D7.c1 as c11, D7.c12 as c12, D7.c13 as c13, D7.c14 as c14 from ((select D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) - D0.c8 * case when TO_NUMBER(TO_CHAR(D0.c3, 'MM'), '99') = TO_NUMBER(TO_CHAR(TO_DATE('6/13/2012','YYYY/MM/DD'), 'MM'), '99') then nvl(D0.c2 , 0) - nvl(D0.c1 , 0) else 0 end as c1, D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) - case when D0.c3 = TO_DATE('6/13/2012','YYYY/MM/DD') then D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) else 0 end as c2, D0.c8 * case when TO_NUMBER(TO_CHAR(D0.c3, 'MM'), '99') = TO_NUMBER(TO_CHAR(TO_DATE('6/13/2012','YYYY/MM/DD'), 'MM'), '99') then nvl(D0.c2 , 0) - nvl(D0.c1 , 0) else 0 end as c3, case when D0.c3 = TO_DATE('6/13/2012','YYYY/MM/DD') then D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) else 0 end as c4, D0.c4 as c5, D0.c3 as c6, D0.c11 as c7, substr(cast(TO_DATE('6/13/2012','YYYY/MM/DD') as CHARACTER ( 30 ) ) , -(6)) as c8, D0.c10 as c9, D0.c5 as c10, D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) as c11, D0.c12 as c12, D0.c13 as c13, D0.c8 as c14 from (select /*+ index(T3160, xxrmb.xxxla_cashflow_n2) index(T2952, xxgl_instrument_dim_mv_u1) */ T3126.ACCOUNTED_CR as c1, T3126.ACCOUNTED_DR as c2, T3386.EFFECTIVE_DATE as c3, T3386.PERIOD_NAME as c4, T2952.SOURCE_INSTRUMENT_TYPE_CODE as c5, T3066.SIGN_CHANGE as c8, T3029.PORTFOLIO_KEY_NAME as c10, T2970.QUERY_DATE as c11, T3160.SOURCE_SYSTEM as c12, T3160.TYPE as c13 from XXXLA_TRANSACTION_OBJECTS_V T3160, XXXLA_AE_JNL_FCT_V T3126, XXGL_JE_LINE_DIM_V T2986, XXGL_TIME_DIM T2970, XXGL_PORTFOLIO_SEC_DIM_GT T3029, XXGL_ACCOUNT_DIM_MV T3066, XXGL_INSTRUMENT_DIM_V T2952, XXGL_JOURNALS_FCT_V T3386 where ( T2986.GL_SL_LINK_TABLE = T3126.GL_SL_LINK_TABLE and T2986.GL_SL_LINK_ID = T3126.GL_SL_LINK_ID and T2970.CALENDAR_DATE = T3386.EFFECTIVE_DATE and T3029.PORTFOLIO_KEY = T3386.PORTFOLIO and T3066.ACCOUNT = T3386.ACCOUNT and T2952.SEGMENT_VALUE = T3386.INSTRUMENT and T2986.JE_HEADER_ID = T3386.JE_HEADER_ID and T2986.JE_LINE_NUM = T3386.JE_LINE_NUM and T3029.PORTFOLIO_KEY_NAME = 'FI TRADING' and T3029.HIERARCHY_NAME = 'MANAGEMENT' and T3029.USER_NAME = upper('LPR') and T3066.ACCOUNT_TYPE = 'GL' and T3126.EVENT_ID = T3160.EVENT_ID and T3386.STATUS = 'P' and (T3066.LEVEL2 in ('500001', '600001')) and T2970.QUERY_DATE <= TO_DATE('2012-06-13' , 'YYYY-MM-DD') ) ) D0 union all select D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) - D0.c8 * case when TO_NUMBER(TO_CHAR(D0.c3, 'MM'), '99') = TO_NUMBER(TO_CHAR(TO_DATE('6/13/2012','YYYY/MM/DD'), 'MM'), '99') then nvl(D0.c2 , 0) - nvl(D0.c1 , 0) else 0 end as c1, D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) - case when D0.c3 = TO_DATE('6/13/2012','YYYY/MM/DD') then D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) else 0 end as c2, D0.c8 * case when TO_NUMBER(TO_CHAR(D0.c3, 'MM'), '99') = TO_NUMBER(TO_CHAR(TO_DATE('6/13/2012','YYYY/MM/DD'), 'MM'), '99') then nvl(D0.c2 , 0) - nvl(D0.c1 , 0) else 0 end as c3, case when D0.c3 = TO_DATE('6/13/2012','YYYY/MM/DD') then D0.c8 * (nvl(D0.c2 , 0) - nvl(D0.c1 , 0)) else 0 end as c4, (HY000)
SQL Issued: SELECT TIME_DIM.QUERY_DATE saw_0, RIGHT(cast(evaluate('TO_DATE(%1,%2)' as date,'6/13/2012', 'YYYY/MM/DD') as char),6) saw_1, GL_JOURNALS_FCT."Effective Date" saw_2, GL_JOURNALS_FCT."Period Name" saw_3, PORTFOLIO_DIM."Portfolio Name" saw_4, INSTRUMENT_DIM."Source Instrument Type Code" saw_5, case when GL_JOURNALS_FCT."Effective Date"= evaluate('TO_DATE(%1,%2)' as date,'6/13/2012', 'YYYY/MM/DD') then (IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_DR,0) - IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_CR,0)) * ACCOUNT_DIM.SIGN_CHANGE ELSE 0 end saw_6, case when MONTH(FSAH_JOURNAL_FCT."Effective Date") = MONTH(evaluate('TO_DATE(%1,%2)' as date,'6/13/2012', 'YYYY/MM/DD')) then IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_DR,0) - IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_CR,0) ELSE 0 end * ACCOUNT_DIM.SIGN_CHANGE saw_7, (IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_DR,0) - IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_CR,0))*ACCOUNT_DIM.SIGN_CHANGE saw_8, ((IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_DR,0) - IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_CR,0))*ACCOUNT_DIM.SIGN_CHANGE) - (case when GL_JOURNALS_FCT."Effective Date"= evaluate('TO_DATE(%1,%2)' as date,'6/13/2012', 'YYYY/MM/DD') then (IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_DR,0) - IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_CR,0)) * ACCOUNT_DIM.SIGN_CHANGE ELSE 0 end) saw_9, ((IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_DR,0) - IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_CR,0))*ACCOUNT_DIM.SIGN_CHANGE) - (case when MONTH(FSAH_JOURNAL_FCT."Effective Date") = MONTH(evaluate('TO_DATE(%1,%2)' as date,'6/13/2012', 'YYYY/MM/DD')) then IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_DR,0) - IFNULL(FSAH_JOURNAL_FCT.ACCOUNTED_CR,0) ELSE 0 end * ACCOUNT_DIM.SIGN_CHANGE) saw_10, TRANSACTION_OBJECTS_DIM."Source System" saw_11, TRANSACTION_OBJECTS_DIM."Transaction Type" saw_12, ACCOUNT_DIM.SIGN_CHANGE saw_13 FROM XXODS WHERE (PORTFOLIO_DIM.HIERARCHY_NAME = 'MANAGEMENT') AND (TIME_DIM.QUERY_DATE <= date '2012-06-13') AND (PORTFOLIO_DIM."Portfolio Name" = 'FI TRADING') AND (ACCOUNT_DIM."Account Type" = 'GL') AND (ACCOUNT_DIM.LEVEL2 IN ('500001', '600001')) ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_8, saw_9, saw_10, saw_11, saw_12, saw_13.

I dont know what the problem is the only different between the two database is .

Differences between the databases

Database where the testers are not having issues

SQL> set pagesize 9999
select property_name, substr(property_value, 1, 30) value
from database_properties
where property_name like 'DST_%' order by property_name;SQL> 2 3

PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE

*.compatible='11.2.0'

where the issue is happening

SQL> set pagesize 9999
select property_name, substr(property_value, 1, 30) value
from database_properties
where property_name like 'DST_%' order by property_name;SQL> 2 3

PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

*.compatible='11.2.0.3.0'

could this be an issue ?

regards

Sibusiso.