python cx_Oracle invalid identifier

Question

I'm trying to query oracle db.

import cx_Oracle

dsn_tns = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=some.server.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)))'
con = cx_Oracle.connect('USER', 'PWD', dsn_tns)

cur = con.cursor()
cur.execute('select * from admin.summary where NUMBER = "C123456"')

res = cur.fetchall()
print res

cur.close()
con.close()

And got:

$ python cx_oracle.py
Traceback (most recent call last):
  File "cx_oracle.py", line 9, in <module>
    cur.execute('select * from admin.summary where NUMBER = "C123456"')
cx_Oracle.DatabaseError: ORA-00936: missing expression

I also tried to change query string to

'select * from admin.summary where NUMBER = 'C1012445''

And got:

$ python cx_oracle.py
  File "cx_oracle.py", line 9
    cur.execute('select * from admin.summary where NUMBER = 'C1012445'')
                                                                    ^
SyntaxError: invalid syntax

Any suggestion? Python version is 2.7

Try this `cur.execute("select * from admin.summary where NUMBER = 'C123456'")` does it work? — Allan, Feb 18 '19 at 13:02
@Allan I tried this - same ORA-00936: missing expression error — Goroshek, Feb 18 '19 at 13:05
Let's do a bit more troubleshooting -> does `select sysdate from dual;` work? Try also without the `;` Let me know if this works — Allan, Feb 18 '19 at 13:09
@Allan yeah,its works: `$ python cx_oracle.py [(datetime.datetime(2019, 2, 18, 16, 10, 32),)]` — Goroshek, Feb 18 '19 at 13:11
Ok good, does `select * from admin.summary` work? Also what is the version of your Oracle DB? — Allan, Feb 18 '19 at 13:12
@Allan it works, but I think I don't have permissions to view that table, so i got a error. DB version is 12 `cx_Oracle.DatabaseError: ORA-00942: table or view does not exist` — Goroshek, Feb 18 '19 at 13:14
You can create a table and then just query it (if you have the access to do so...) What we have proved is that your connection to the DB does work and the DB does answer properly... Also note that your string values in the where clause should always be in single quotes, and the whole query in double quotes. — Allan, Feb 18 '19 at 13:18
@Allan The problem is that i can query that string from other software like PLSQL, but cant from the python. — Goroshek, Feb 18 '19 at 13:19
@Allan I change string to `"select * from admin.summary where 'NUMBER' = 'C123456'"` and got empty list, but in fact list need to be full of values: `$ python cx_oracle.py []` — Goroshek, Feb 18 '19 at 13:25
NUMBER should not be between quotes, as it is a column name. — Allan, Feb 18 '19 at 13:29
Also have a quick look at this link: https://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle to check what are the tables you have access to. `SELECT owner, table_name FROM all_tables` and `SELECT table_name FROM user_tables` if you don't see your table `admin.summary` then you need to request permissions to your DBA. Good luck — Allan, Feb 18 '19 at 13:32

score 1 · Accepted Answer · answered Feb 18 '19 at 23:47

NUMBER is a reserved word in SQL used for a data type. By default it won't be a column name, unless someone forced it with:

SQL> create table t ("NUMBER" number);

Table created.

If they did this, then your SQL needs to also quote the column name like:

cur.execute("""select "NUMBER" from t where "NUMBER" = 1""")

Or, in your case, like:

cur.execute("""select * from admin.summary where "NUMBER" = 'C123456'""")

However, unless you always use the same value in the 'where' clause, you should use a bind variable for C123456. See how https://github.com/oracle/python-cx_Oracle/blob/master/samples/BindQuery.py does it.

Using bind variables helps scalability and helps stop SQL injection attacks.

APC · Answer 2 · 2019-02-19T08:02:09.830

cur.execute('select * from admin.summary where NUMBER = "C123456"'

In SQL double-quotes are used for database identifier names (tables and columns), not string literals. So the Oracle compiler is looking for a column called C123456.

cur.execute('select * from admin.summary where NUMBER = 'C1012445'')

Your string is bounded by single quotes, so it ends after the = and the Python interpreter doesn't know what to do with C123456.

Try escaping the quotes like this:

cur.execute('select * from admin.summary where NUMBER = ''C1012445'' ')

As @ChristopherJones points out, NUMBER is a reserved word and cannot be used as a name in Oracle. I assumed the posted code was a redaction but if somebody has been foolish enough to force through such a column name they must have done so by using double-quotes. In which case all subsequent references to the column must also be escaped by double-quotes:

cur.execute('select * from admin.summary where "NUMBER" = ''C1012445'' ')

NUMBER is a reserved word:```SQL> create table t (number CHAR); create table t (number CHAR) * ERROR at line 1: ORA-00904: : invalid identifier ``` — Christopher Jones, Feb 18 '19 at 23:48

python cx_Oracle invalid identifier

2 Answers2