OBIEE , ORA-00942 Table or View does not exists, However it exists. (it works if change order)

Camilo Flores

Hello

I have the following problem , today some reports don't work  (I haven't worked with this envioremwent during the last week)

However with little changes in the query it works (which is not a solution since it is th equery generated by the OBI SERVER)

Some odd behaviors

1.- If I change the position of the tables in the Query , moving the last table (a fact table ) to the second last  position it works

2.- If I Remove  the SAWITH part, it works

Example

1.- This does not work, and is the generated query

WITH

SAWITH0 AS (select count(distinct T41570.DD_NUM_EVENTO) as c1

from

     View1 T41664,

     Dm1 T41412,

     View2 T41683,

     FTable 1 T41570

where  ( T41412.ID_TIPO_EVENTO = T41570.ID_TIP_EVENTO

and T41412.GLS_TIPO_EVENTO = 'DET'

and T41570.FLG_ACTIVO = 1

and T41570.ID_DEMOGRAFICA = T41664.ID_SEGMENTO_DEMOGRAFICO

and T41570.ID_FECHA_HECHO = T41683.ID_TIEMPO

and T41664.GLS_MAYOR_EDAD = 'MOE'

and T41683.COD_ANO = TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'yyyy'), '9999')

and T41683.FECHA between ( TO_DATE('2018-04-30' , 'YYYY-MM-DD') + TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'DDD'), '999') * -1 + 1 )

and TO_DATE('2018-04-30' , 'YYYY-MM-DD') ) )

select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,

     D1.c1 as c2

from

     SAWITH0 D1 ) D1 where rownum <= 66001

2.- This Works, Switch Positions of FTable and View2

WITH

SAWITH0 AS (select count(distinct T41570.DD_NUM_EVENTO) as c1

from

     View1 T41664,

     Dm1 T41412,

     FTable 1 T41570,

     View2 T41683,

where  ( T41412.ID_TIPO_EVENTO = T41570.ID_TIP_EVENTO

and T41412.GLS_TIPO_EVENTO = 'DET'

and T41570.FLG_ACTIVO = 1

and T41570.ID_DEMOGRAFICA = T41664.ID_SEGMENTO_DEMOGRAFICO

and T41570.ID_FECHA_HECHO = T41683.ID_TIEMPO

and T41664.GLS_MAYOR_EDAD = 'MOE'

and T41683.COD_ANO = TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'yyyy'), '9999')

and T41683.FECHA between ( TO_DATE('2018-04-30' , 'YYYY-MM-DD') + TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'DDD'), '999') * -1 + 1 )

and TO_DATE('2018-04-30' , 'YYYY-MM-DD') ) )

select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,

     D1.c1 as c2

from

     SAWITH0 D1 ) D1 where rownum <= 66001

3.- This Works , Remove the Sawith part and the End of the query, to prove the table does exist

select count(distinct T41570.DD_NUM_EVENTO) as c1

from

     View1 T41664,

     Dm1 T41412,

     View2 T41683,

     FTable 1 T41570

where  ( T41412.ID_TIPO_EVENTO = T41570.ID_TIP_EVENTO

and T41412.GLS_TIPO_EVENTO = 'DET'

and T41570.FLG_ACTIVO = 1

and T41570.ID_DEMOGRAFICA = T41664.ID_SEGMENTO_DEMOGRAFICO

and T41570.ID_FECHA_HECHO = T41683.ID_TIEMPO

and T41664.GLS_MAYOR_EDAD = 'MOE'

and T41683.COD_ANO = TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'yyyy'), '9999')

and T41683.FECHA between ( TO_DATE('2018-04-30' , 'YYYY-MM-DD') + TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'DDD'), '999') * -1 + 1 )

and TO_DATE('2018-04-30' , 'YYYY-MM-DD') )

Oracle Database

Has anyone faced this issue? Any suggestions?

Thanks

Camilo

user7959352

Camilo,

I doubt even the second query works there is a comma ( View2 T41683,)  View or tables shown in query are obiee views. May be that view deleted later

Camilo Flores

This was just an error posting the question

It should say

....

  FTable 1 T41570,

     View2 T41683

where  ( T41412.ID_TIPO_EVENTO = T41570.ID_TIP_EVENTO.....

Martin van Donselaar

That last example still won't work actually: FTable 1 T41570

A table does either exist for a schema user (Meaning it has rights to see the table) or it doesn't, therefore switching table order in the query is very unlikely to be the cause of this error.

Did you find out which table triggers the ora-942 error?

What schema user does obiee connect to in the connection pool?

Did you test all queries in SQLplus (or any query tool)?

With the same schema user in your connection pool?

Camilo Flores

Martin

I tested with several reports using the same Fact Table, all  of them fail , when the Fact Table is last in the From definition, I am not saying that the error is caused for moving the Table, but there is a BUG in the database probably, with some other conditions, and when moving the table, it worked for me.

However we have found a BUG

Bug 5130732 - Query using "WITH" clause can fail with ORA-942 (Doc ID 5130732.8)

I fixed it , unchecking WITH_CLAUSE_SUPPORTED, not sure if it will affect, but at least all the reports are running now

Camilo

Martin van Donselaar

Which Database version do you use? That bug has been fixed so you should not run into it?.

If you believe you hit this bug and you're database is at a higher version I would advise to raise an SR with Oracle.

Unchecking WITH_CLAUSE_SUPPORTED may impact performance so be very careful with this