Invalid update generated by Oracle .net driver

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Wed, 23 Jan 2013 22:58:11 -0800 (PST)
Message-ID: <f6deb6a1-0278-47b8-ad7a-0ce4c1aab371_at_googlegroups.com>

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 2.102.2.20 and 2.112.3.0. Both have the same issue. Received on Thu Jan 24 2013 - 07:58:11 CET