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
I'm having the same problem, i enabled this feature but i the error persists.
ReplyDeleteDo you have any idea what more can i do?
What do you mean exactly with:
ReplyDelete"Instead of the presentation hierarchy if I use high level column (Region), I am able to drill down and get correct numbers".
If you used only the column "Region", how did you the "drill-down" funcionality?
Hi Miguel,
DeleteIt's an old post and I don't clearly recall but instead of using presentation hierarchy you can always drill down by clicking on higher level to a lower level (provided the hierarchy/dimension has been defined in BMM layer.
Cheers!
Deepak
Hi deepak. I having same issue, I tried to change the DB feature as your suggested and also changed the DBFeatures.ini but it didn't work for me. I'm joing Tables from two different Schemas.
ReplyDeleteThanks,
Venkat
Make sure you have Select Priv on all tables for the user in your connection pool.
ReplyDelete