Accessing tables from another owner using cx_Oracle + Python

Question

I'm working in a company where they organize their projects in different OWNERs in the Oracle database. Using Python + cx_Oracle, it can list all the tables in the root database, as shown on the exemple below:

I couldn't even find any instruction in the official cx_Oracle documentation on how to access tables from other OWNERs. In Oracle SQL Developer I can run the query, but not when I try to run the same query in Python, as showm below:

PS: ORA-00942: a tabela ou view não existe -> ORA-00942: table or view does not exist

The original code:

import cx_Oracle

try:
    connection = cx_Oracle.connect(
        user='SYSTEM',
        password='123456',
        dsn='localhost:1521/XEPDB1',
        encoding='UTF-8'
    )
    print(connection.version)

    c = connection.cursor()
    rows = c.execute("select con_id,name,open_mode,restricted,open_time from v$pdbs").fetchall()
    for row in rows:
        print(row)

except Exception as ex:
    print(ex)

Connection details:

I will be very grateful if someone can explain to me how to solve this problem.

Answer 1

Let's try to verify your example with my own.

• Oracle 19c
• Python 3.8.1
• cx_Oracle 8.0

Tip: DNS name for database is ODCGRC1R.XXXXX.DEV.CORP which differs from the hostname of the server which is scglvdoracd0006.xxxxx.dev.corp

1.Create an user with select privilege over other schemas

SQL> select instance_name , host_name from v$instance ;

INSTANCE_NAME         HOST_NAME
----------------------------------------------------
odcgrc1r              scglvdoracd0006.xxxxx.dev.corp

SQL> create user testpython identified by "Oracle_1234" ;

User created.

SQL> grant connect to testpython ;

Grant succeeded.

SQL> grant select any table to testpython ;

Grant succeeded.

2.From my laptop, connect to check a table in another schema ( sqlplus )

C:\python>tnsping ODCGRC1R.XXXXX.DEV.CORP:60995/odcgrc1r

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-SEP-2021 12:00:25

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Parameterdateien benutzt:
C:\Programme\Oracle\11.2.0\client\network\admin\sqlnet.ora

Adapter HOSTNAME zur Aufl÷sung des Alias benutzt
Verbindungsversuch mit (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=odcgrc1r))(ADDRESS=(PROTOCOL=TCP)(HOST=180.22.128.47)(PORT=60995)))
OK (130 ms)

C:\python>sqlplus testpython/"Oracle_1234"@//ODCGRC1R.XXXXX.DEV.CORP:60995/odcgrc1r

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 11 12:00:46 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select count(*) from cpl_rep.frm_c001_status_cat ;

  COUNT(*)
----------
         4

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Now, let's use cx_Oracle to do the same. Below a simple test program

import cx_Oracle
import os 

host="ODCGRC1R.XXXXX.DEV.CORP"
port=60995
sid='odcgrc1r'
user='testpython'
password='Oracle_1234'
sid = cx_Oracle.makedsn(host, port, service_name=sid)

connection = cx_Oracle.connect(user, password, sid, encoding="UTF-8")
cursor = connection.cursor()
cursor.execute('select count(*) from CPL_REP.FRM_C001_STATUS_CAT')

for row in cursor:
    print(row)

So, let's execute it

C:\python>python testconn2.py
(4,)

Replacing count*) from * to print all rows

C:\python>python testconn2.py
(1, 'Scheduled', 1, None, None, None, None)
(2, 'Waiting Execution', 1, None, None, None, None)
(3, 'Running', 1, None, None, None, None)
(4, 'Ended', 1, None, None, None, None)

Summary

There is no issue in cx_Oracle to access other tables in different schemas, as long as you have the right privileges to do so. In your case, IF it works by SQL Developer, and it doesn't on Python, probably you are not connecting to the right PDB.