SELECT data from another schema in oracle

Question

I want to execute a query that selects data from a different schema than the one specified in the DB connection (same Oracle server, same database, different schema)

I have an python app talking to an Oracle server. It opens a connection to database (server/schema) A, and executes select queries to tables inside that database.

I've tried the following :

select .... 
from pct.pi_int, pct.pi_ma, pct.pi_es
where ...

But I get:

ORA-00942: table or view does not exist

I've also tried surrounding the schema name with brackets:

from [PCT].pi_int, [PCT].pi_ma, [PCAT].pi_es

I get:

ORA-00903: invalid table name

The queries are executed using the cx_Oracle python module from inside a Django app.

Can this be done or should I make a new db connection?

Answer 1

Does the user that you are using to connect to the database (user A in this example) have SELECT access on the objects in the PCT schema? Assuming that A does not have this access, you would get the "table or view does not exist" error.

Most likely, you need your DBA to grant user A access to whatever tables in the PCT schema that you need. Something like

GRANT SELECT ON pct.pi_int
   TO a;

Once that is done, you should be able to refer to the objects in the PCT schema using the syntax pct.pi_int as you demonstrated initially in your question. The bracket syntax approach will not work.

Answer 2

In addition to grants, you can try creating synonyms. It will avoid the need for specifying the table owner schema every time.

From the connecting schema:

CREATE SYNONYM pi_int FOR pct.pi_int;

Then you can query pi_int as:

SELECT * FROM pi_int;