How to query tables and pull data into an Oracle APEX application from another schema?

Question

I am working in an Oracle APEX application and am trying to query tables in another schema (that another Oracle APEX application sits on) to pull in data to my application.

The applications are hosted within the same APEX workspace and on the same Oracle 11g instance. Our application have tables that are structurally the same as the tables we're trying to query in the other schema.

Using the schema prefix (SELECT * FROM "SCHEMA2".TABLE1;) when querying is throwing an error that the tables do not exist (ORA-00942: table or view does not exist)

The second thing I tried is creating a database link between the two schemas, but when trying to establish the connection I'm getting the following error: ORA-01031: insufficient privileges

Can someone identify where I'm going wrong here / help me figure out how to query the other schema?

Answer 1

Database link is used when there are different databases involved; your schemas reside in the same database which means that the first attempt was correct: prefixing table name with its owner, e.g.

SELECT * FROM SCHEMA2.TABLE1

However, first connect as schema2 user and issue

grant select on table1 to schema1;

Otherwise, schema1 can't see the table. If schema1 is supposed to do something else with that table, you'll have to grant additional privileges, such as insert, update, delete, ....