I am trying to assist our developers with this issue:
We are planning to switch from MS driver for .Net to Oracle driver for .Net,
however Oracle .net driver sometimes generates invalid UPDATE.
This is how it works: we provide SELECT statement, let's say
SELECT A, B, C FROM TABLE_A WHERE ()
Oracle executes it and returns values A=A1, B=B1, C=C1.
The driver generates UPDATE statement that has all columns in the WHERE clause:
UPDATE TABLE_A SET C=C2 WHERE A=A1, B=B1, C=C1.
It works fine.
However if SELECT statement contains expression then sometimes invalid UPDATE gets generated. In simplified form:
SELECT A+B D,C FROM TABLE_A WHERE
.Net driver generates UPDATE statement
UPDATE TABLE_A SET C=C2 WHERE A=A1, B=B1, D=D1, C=C1.
As this table doesn't have column "D" the UPDATE fails with ORA-00904 "D" invalid identifier
It could be fixed by adding AS
SELECT A+B AS D,C FROM TABLE_A WHERE
This is complex app written some time ago, our preference is to avoid changing SQL.
We tried two versions: Oracle driver for .net 126.96.36.199 and 188.8.131.52.
Both have the same issue.