Hi everyone,
I'm working on Oracle APEX 24.2 with Oracle Database 21c.
I'm building a multi-tenant application, and I would like each tenant to have its own schema.
The idea is that all tenants use the same APEX application, but their data is isolated at the schema level.
For example, in my Interactive Grids I’d like to have a static SQL source like:
SELECT * FROM customers;
but when a tenant logs in, it should automatically read from that tenant’s schema
(e.g., TENANT_A.customers, TENANT_B.customers, etc.).
In other words, I want to dynamically change the *parsing schema* depending on the logged-in tenant.
If this were possible, I wouldn’t need to maintain separate apps per tenant — one shared app could simply
switch schema context after login.
I’m aware of the common approach using a single table with a `tenant_id` column (or Virtual Private Database policies),
but I want to avoid that model because it causes the shared tables (like CUSTOMERS) to grow excessively large over time.
Here’s what I’ve already tried:
- Executing `ALTER SESSION SET CURRENT_SCHEMA = tenant_schema` after login
- Using dynamic SQL via refcursor to fetch data from the tenant’s schema
- Storing the schema name in an application item (e.g., `APP_SCHEMA`) and referencing it dynamically
However, none of these approaches worked reliably for Interactive Grid regions — they seem to parse SQL at design time, not runtime.
Is there any supported way to dynamically change the parsing schema or session schema context per tenant in APEX?
Or is the only viable approach to create one APEX app per tenant?
I’ve been researching this for weeks but haven’t found a similar working example anywhere.
Any guidance or confirmation on the limitations would be greatly appreciated.
Thanks in advance!