I have seen variations of this question returning a couple of times in the forum, but no answer has come forward so far. So let's try again...
In APEX 4.1 (maybe also in earlier versions) there is a drop-down at the top-right of the query-builder tool to select a different schema.
The problem is, that it only shows the "parsing" schema. So the user the workspace is associated with.
I have taken all of the following steps, that i would expect to make the other schemas show up:
- associate my second scheme, SCHEMA_2, to the workspace SPACE_1
- grant the "parsing" schema, SCHEMA_1, select access to all objects in SCHEMA_2
And I have tested the following things:
- in "SQL Commands" run a query that selects objects from SCHEMA_2, using "select * from SCHEMA_2.TEST_TABLE;". This works
- in query builder, select the "SQL" option and insert the same query and click "run". Results in "Please enter a valid query to see results"
- create an application with a "SQL report" page, and insert the same query. Results in "Query cannot be parsed, please check the syntax of your query. (ORA-00942: table or view does not exist)"
My toughts on this issue:
- A different user is used in query builder and the application builder?
- the "SQL commands" uses the "parsing" schema, as expected