Hi all!
In my workspace DMS_KPI_DEV I have two schema SC1 and SC2. We have created a role and have granted select access to SC2 tables. When building a query, LOV, etc. we are good and works wonderfully. I am now needing to create a view in DMS_KPI_DEV that has a table from SC2 and a lot of tables from SC1.
When I run a query using both schemas in SQL Commands it runs like a champ and I get data from both schemas.
When I tried to take this query (copy/paste) and put it in the Create View Wizard I get “Unable to Create View Error at line 18/12: ORA-01031: insufficient privileges ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 847 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 833 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 1903”
Line 18 is the table from SC2.
What permission did we miss granting against SC2 so that we can create a view using tables from SC2?
We did like on the SC2 Plant table:
- Grant SELECT on SC2.PLANT to DMS_KPI_PROD_DMS_KPI_READONLY; (the role)
- Grant MMS_QA_DMS_KPI_READONLY to DMS_KPI_DEV;
Diane
[Note: Edited by moderator to remove a typo that caused the Spam filter to activate]