I've an APEX interactive grid with a complex VIEW on OEBS data. Instead of using ALL-Tables I switch to MOAC tables, as this proveded to be much more performant by means of report selection time. The new view is ready to use, but as it uses MOAC-security it delivers no data without setting the MOAC security policy.
My APEX application is executed in schema EAO.. Thus I had to grant execution
grant execute on apps.MO_GLOBAL to eao;
further I provided a synonym
create or replace synonym EAO.MO_GLOBAL for apps.MO_GLOBAL;
Wenn I do this in SQL Developer in schema EAO
exec MO_GLOBAL.SET_POLICY_CONTEXT('S', 81);
SELECT * FROM EAO_AR_CUSTOMS_RECONCILE_RPT_V;
I get the data from underlying MOAC-protected, where 81 is my organization_ID I need to work on.
Anyhow when I execute in APEX
MO_GLOBAL.SET_POLICY_CONTEXT('S', 81);I get an error
ORA-00942 table or view does not exist:
is_internal_error: false
ora_sqlcode: -942
ora_sqlerrm: ORA-00942: Tabelle oder View nicht vorhanden
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 27222255176423344
component.name: Get Username Cookie
error_backtrace:
ORA-06512: in "APPS.FND_CORE_LOG", Zeile 45
ORA-06512: in "APPS.FND_GLOBAL", Zeile 144
ORA-06512: in "APPS.FND_GLOBAL", Zeile 1982
ORA-06512: in "APPS.FND_GLOBAL", Zeile 2710
ORA-06512: in "APPS.MO_GLOBAL", Zeile 1044 ORA-06512: in Zeile 6
ORA-06512: in "SYS.DBMS_SYS_SQL", Zeile 1926 ORA-06512: in "SYS.WWV_DBMS_SQL_APEX_190100", Zeile 599
ORA-06512: in "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", Zeile 2486
ORA-06512: in "APEX_190100.WWV_FLOW_PROCESS_NATIVE", Zeile 93
ORA-06512: in "APEX_190100.WWV_FLOW_PROCESS_NATIVE", Zeile 1153
ORA-06512: in "APEX_190100.WWV_FLOW_PLUGIN", Zeile 2457
ORA-06512: in "APEX_190100.WWV_FLOW_PROCESS", Zeile 203
error_statement:
begin
begin
apex_debug.enable(p_level => apex_debug.c_log_level_info);
apex_debug.info(p_message => 'Report XYZ started at ' || to_char(sysdate,'DD.MM.YYYY hh24.mm.ss'));
apex_debug.info(p_message => 'Current Schema: ' || sys_context( 'userenv', 'current_schema' ));
APPS.MO_GLOBAL.SET_POLICY_CONTEXT('S', 81);
end;
end;
Can you help? Any idea what goes wrong?