cx_Oracle.DatabaseError: ORA-00942: table or view does not exist

Question

I am trying to access an Oracle database table using a python script. I have written following code:

 import cx_Oracle

 con = cx_Oracle.connect("system/[email protected]/XE")
 cur = con.cursor()
 cur.execute(" SELECT * FROM SUPPLIER")
 for row in cursor.fetchall(): 
      print(row)

and the output is:

Traceback (most recent call last):
  File "C:/Users/ADMIN/AppData/Local/Programs/Python/Python36- 
32/my_file.py", line 6, in <module>
    cur.execute(" SELECT * FROM SUPPLIER")
cx_Oracle.DatabaseError: ORA-00942: table or view does not exist

Can anyone tell me how to solve this error?

Answer 1

I bet it's a schema name issue. You connect as user SYSTEM, but want to select from a table called SUPPLIER which is very likely not in the schema SYSTEM.

You can find out where the table is by running in the Apex SQL workshop:

SELECT owner FROM all_tables WHERE table_name = 'SUPPLIERS';

You'll need to put the owner in front of the table. For instance, if the last query returned 'MYUSER', you'll need to change the query to

cur.execute(" SELECT * FROM MYUSER.SUPPLIER")