It looks like Apex has built in the capability for different users to reference different database schemas at run time:-
1. Ability to add multiple schemas to a workspace
2. Ability to set the default schema for a user and restrict access for the user to one or more schemas
Doco is light-on in this area so I built a test app in Apex 4.2 to see if it would work for my needs....
1. created three schemas (schema1, schema2, schema3) and created the same table (testtab) in each schema but with different data so that I could tell which schema was being referenced.
2. created a test app as developer (schema1) with a home page and a simple report with sql: select sname from testtab
3. test app uses Application Express Authentication and has the parsing schema set to schema1
4. run the app as developer and I get the data I would expect from testtab in schema1
5. in Administration, added schema2 and schema3 to the workspace
6. created new workspace users user2 and user3 and gave them default schemas of schema2 and schema3 respectively. Also restricted their access to schema2 and schema3 respectively
Assumption here is that when I log on as user2, the sql "select sname from testtab" would be run against schema2 (the default schema for user2).
However, irrespective of whether I log in as developer, user2 or user3 I always get the data from schema1 (parsing schema) returned by the query.
Also tried:-
1. "select sname from #owner#.testtab" - same result (always get the data from the parsing schema - schema1)
2. "select sname from schema2.testtab" - this now always returns data from schema2 irrespective of user proving that schema2 exists and is accessible by apex
So the questions are:-
1. Where is the "default schema", that you can assign to a user, used in apex at runtime?
2. How to I get apex to change the schema that is being referenced at runtime to the logged in users "default schema"
Thanks in advance if you can help