Error ORA-00904 while having Faceted Search With multiple facets search Items

user_1983030 0
Viewed 280

I am using APEX 21.1 and I am facing the below error while running a page having two regions (Faceted Search and Classic Report) :

Error in Page:

Ajax call returned server error ORA-00904: "APPS"."HR_API": invalid identifier for .

Error in Debug:

APEX - Column Gender, referenced by facet P24_G, is not available or unauthorized. - Contact your application administrator. ORA-06512: at "APEX_210100.WWV_FLOW_ERROR", line 1370 ORA-06512: at "APEX_210100.WWV_FLOW_ERROR", line 1405

The error is happening only when I am adding more than one facet item in the Faceted Search.

Can anyone let met know is if it is a bug or not?

Note that I am using a DB view from a different Schema in the Classic Report.

oracle-apex oracle
1 Answers

The solution for this error since I am using a DB view in a different schema in my classic report

Oracle Answer:

with views or objects in other schemas, it often helps to rewrite the report query to use the MATERIALIZE hint, e.g. as follows:

with myquery as (
    select /*+ MATERIALIZE */ {your query}
)
select * from myquery
user_19830300
Related