1 Reply Latest reply: Apr 6, 2012 3:20 AM by Wouter_ RSS

    Multiple schemas in query builder

    Wouter_
      Hi,

      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

      So, what's wrong?