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

User · Answer

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

User · 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

User · Answer

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

[sql] SELECT data from another schema in oracle

Examples related to sql

Examples related to oracle

Examples related to cx-oracle