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 database connection (same Oracle server, same database, different schema)

I have a python application working with an Oracle server. It opens a connection to the database (server / schema) A and selects the queries for the tables inside this database.

I 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 also tried matching the schema name with parentheses:

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

I get:

 ORA-00903: invalid table name 

Requests are made using the cx_Oracle python module from a Django application.

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

Answer 1

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

Most likely, you need your database administrator to give user A access to any tables in the PCT schema that you need. Something like

 GRANT SELECT ON pct.pi_int TO a; 

Once this is done, you should be able to reference the objects in the PCT schema using the pct.pi_int syntax, as you originally demonstrated in your question. The parenthesis syntax approach will not work.

Answer 2

In addition to grants, you can try to create synonyms. This avoids the need to specify a table owner schema each time.

From the interconnect;

CREATE SYNONYM pi_int FOR pct.pi_int;

Then you can query pi_int as:

SELECT * FROM pi_int;

Answer 3

Depending on the scheme / account that you use to connect to the database, I suspect that you are missing the grant of the account that you use to connect to the database.

Connect as a PCT account in the database, then specify the account for which you use access to access the table.

select selection on pi_int for Account_used_to_connect