OBIEE 11g: ORA-00942: table or view does not exist at OCI call OCIStmtExecute Error
I am working on OBIEE 11g - 11.1.1.3. Database is Oracle 10g- 10.2.0.3
Set up: I have a presentation hierarchy Region>Country>State>City build from a single Geography Dimension. I have three fact columns coming from three materialized views.
Now, when this Presentation Hierarchy and three fact columns are used to create a request, and I try to drill down, BI query is throwing a "table does not exist" error. I get the following error.
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
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: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
Instead of the presentation hierarchy if I use high level column (Region), I am able to drill down and get correct numbers. I am able to create a report with Region, Country, State, City, Fact1, Fact2, and Fact3 without getting an error.
With the error, I also saw the query log with too long query and many SAWITHn, SACOMMONnnnn clauses.
I enabled PERF_PREFER_INTERNAL_STITCH_JOIN in DB Features in repository and now the hierarchy and reports work fine. You will get a warning that DB Features do not match default values during consistency check, you can ignore that.
Hope it helps someone.
Cheers!
Deepak
