I have some done a good deal of poking around on this forum in search of an explicit or implicit answer to my question, but have not been able to find a clear one. So I'll try this.
I have two schema in our production database; one which I will call PROD_STAR, and another I will call REPORT.
The PROD_STAR schema is open for updates to some users; we do ETLs from other data sources to populate our star schema.
The REPORT schema consists of synonyms referencing the PROD_STAR schema. Almost all of the synonyms have only 'select' grants, but there are a few tables which have CRUD grants as well.
This is the behavior I am seeing;
Ex:
Table X in PROD_STAR is very simple; some NUMBER and VARCHAR2 fields. There is a synonym X in REPORT which has been granted 'all' privileges on the table X in PROD_STAR.
I built a form with interactive report against table X in the schema PROD_STAR. Then I was informed that I should really be using the REPORT schema with appropriate grants from PROD_STAR for table X.
So, I did the appropriate grants, and switched the Parsing Schema to REPORT.
Now, the interactive report loads with the same query it had when the Parsing Schema was PROD_STAR.
select ... from #owner#.X
The report populates fine.
However, when I try to edit and pop up the form view, I get the following
Table or view X not found Contact your application administrator.
Technical Info (only visible for developers)
- is_internal_error: true
- apex_error_code: WWV_FLOW_DML.TABLE_NOT_FOUND
- component.type: APEX_APPLICATION_PAGE_PROCESS
- component.id: 2248405326917199
- component.name: Fetch Row from X
- error_backtrace: ----- PL/SQL Call Stack ----- object line object handle number name 3fb543b18 548 package body APEX_040200.WWV_FLOW_ERROR 3fb543b18 599 package body APEX_040200.WWV_FLOW_ERROR 3fb543b18 903 package body APEX_040200.WWV_FLOW_ERROR 418f0b940 160 package body APEX_040200.WWV_FLOW_DML 418f77270 332 package body APEX_040200.WWV_FLOW_PROCESS 418f77270 783 package body APEX_040200.WWV_FLOW_PROCESS 3fc9bb868 1909 package body APEX_040200.WWV_FLOW_PAGE 42e559818 6725 package body APEX_040200.WWV_FLOW 3fbd061a8 249 procedure APEX_040200.F 41814d6e0 2 anonymous block
I can create a new row for X, which uses the same form, and items are created in the PROD_STAR database. But I can not edit, so there is something about the row fetch that isn't happy when I'm bringing back an existing item.
When I try to create a new form with interactive report while REPORT is the Parsing Schema, it doesn't even see the synonyms, so am dead in the water there.
Any thoughts?
Thanks
-jg-