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
– Allan, Commented Feb 18, 2019 at 13:02
@Allan I tried this - same ORA-00936: missing expression error — Goroshek
– Goroshek, Commented Feb 18, 2019 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
– Allan, Commented Feb 18, 2019 at 13:09
@Allan yeah,its works: $ python cx_oracle.py [(datetime.datetime(2019, 2, 18, 16, 10, 32),)] — Goroshek
– Goroshek, Commented Feb 18, 2019 at 13:11
Ok good, does select * from admin.summary work? Also what is the version of your Oracle DB? — Allan
– Allan, Commented Feb 18, 2019 at 13:12

Christopher Jones · Accepted Answer · 2019-02-18 23:47:41Z

1

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.

answered Feb 18, 2019 at 23:47

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

APC · Accepted Answer · 2019-02-19 08:02:09Z

0

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'' ')

edited Feb 19, 2019 at 8:02

answered Feb 18, 2019 at 15:08

APC

147k20 gold badges180 silver badges289 bronze badges

1 Comment

Christopher Jones Over a year ago

NUMBER is a reserved word:

SQL> create table t (number CHAR); create table t (number CHAR)                 * ERROR at line 1: ORA-00904: : invalid identifier

Collectives™ on Stack Overflow

python cx_Oracle invalid identifier

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related