CODEWITHSUNDEEP
pythoncx-oracle
Tibco
Tibco

Reputation: 13

Python cx_oracle ignores numbers by positional bind variables

cursor.execute("SELECT * FROM t WHERE c1=:2 AND c2=:1", (1, 2))

is executed as

SELECT * FROM t WHERE c1=1 AND c2=2

instead of 

SELECT * FROM t WHERE c1=2 AND c2=1

as I expected. Why?

In fact all the :numbers are completely ignored

sql = ":5, :0, :0, :2, :1, :3"

is interpreted the same as

sql = ":1, :2, :3, :4, :5, :6"

Is this by intention or a bug?

Upvotes: 1

Views: 1069

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

cx_Oracle is using OCI which has this behavior. You can read about it in this white paper (bottom of page 14 and top of page 15):

http://www.oracle.com/technetwork/database/database-technologies/php/whatsnew/building-best-drivers-131920.pdf

Effectively Oracle scans from left to right and assigns position numbers to bind variables in the order they are encountered. You can use named bind variables instead to avoid this potential confusion.

Upvotes: 1

Related Questions