SELECT data from another schema in oracle

13
votes

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?

3
sqloraclecx-oracle
Out of curiosity did you try this statement in Toad or SQL Developer before trying it in python? – Conrad Frix
In Oracle terminology, a database is a collection of data files. It sounds like you want to select data from a different schema in the same database which is what I edited your question to reflect. What Oracle calls a "schema" is similar to what many other RDBMS products refer to as a "database". If you really mean that there are two databases on the server and you want to connect to database A and query tables on database B, that is possible but then you need to add a database link to the solution. – Justin Cave
Thats correct, I mean two schemas. – marianov
I tried this and other queries using the python shell, and all the other queries work. – marianov
[ and ] are invalid characters for (ANSI) SQL identifiers. – a_horse_with_no_name

3 Answers

26
votes

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.

5
votes

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;
0
votes

Depending on the schema/account you are using to connect to the database, I would suspect you are missing a grant to the account you are using to connect to the database.

Connect as PCT account in the database, then grant the account you are using select access for the table.

grant select on pi_int to Account_used_to_connect