1 Reply Latest reply: Jan 29, 2013 6:25 AM by Tridus RSS

    Invalid UPDATE generated by Oracle .net driver - ORA-00904

    Vsevolod Afanassiev
      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.