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?

Accepted Answer

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.

SELECT data from another schema in oracle

Solution

Problem