HI Team,
We are trying to build an APEX application with custom schema on Oracle Autonomous Database Serverless.
We have a schema ITM_WKSP, which is linked to APEX workspace ITM_WKSP.
We have another schema which is ITM_OWNER, which will hold all the DB objects required for the application on ITM_WKSP.
Now we are trying to implement the current_schema approach using a after logon trigger from ITM_WKSP to ITM_OWNER, so that objects can be accessed directly without using synonym or SCHEMA. prefix to ITM_OWNER.
But somehow either the trigger is not executing when logged in from APEX with ITM_WKSP workspace and Username or the changes are not reflecting. The necessary grants to the ITM_OWNER objects are there and they can be accessed using schema name prefix. But without schema name prefix they are not accessible from ITM_WKSP.
select * from ITM_USAGE_LOAD_STG_VIEW;
– Error at line 1/15: ORA-00942: table or view does not exist
select * from ITM_OWNER.ITM_USAGE_LOAD_STG_VIEW;
– No data found (Accessible because grants are there)
The POC_TRIGGER_LOG table (used for debug in the script) also does not containing any value while logged in from APEX.
When I try from SQL Developer Web using ITM_WKSP, it works after some time. Not immediately.
select * from ITM_USAGE_LOAD_STG_VIEW;
– No data found
ITM_WKSP has the ADMINISTER DATABASE TRIGGERS, and ALTER SESSION grants.
itm_after_logon_trg.sql