2 Replies Latest reply on Jan 17, 2020 11:38 AM by 811879

    Oracle 12c ResultSet updateRow getting : ORA-01732: data manipulation operation not legal on this view

    811879
    811879 Jan 17, 2020 5:01 AM

      Hi All,

       

      I am getting error "Getting ORA-01732: data manipulation operation not legal on this view" while trying to update a table by calling resultset.updateRow(). It is working fine on Oracle 11g DB but started failing after we migrated to Oracle 12c. I've downloaded ojdbc7_g.jar and enabled debug mode for Oracle but could not find any issue other than receiving response with "ORA-01732" code from server. I am suspecting some DB configuration is preventing "SELECT FOR UPDATE" operation as normal update query works fine. Although when I run  "SELECT FOR UPDATE"  from Oracle Clients like TOAD or SQL Developer it works fine. What am I missing ?

      Please need your input to find the  root cause. All suggestions are welcomed. Thank you very much.

       

      P.S. My Functional user has DELETE/UPDATE/INSERT permission on this table. I checked that It is not a view or materialized view or synonym. It is a table

       

      Oracle Version:

      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

       

       

      Error Response:

       

      00 00 00 7F 06 00 00 00     |........|

      00 00 08 01 06 00 00 00     |........|

      00 00 00 00 00 00 00 04     |........|

      01 02 01 05 00 02 06 C4     |........|

      00 00 01 04 01 07 06 00     |........|

      00 00 00 00 03 02 3C 9E     |......<.|

      01 A0 00 02 05 6F 00 00     |.....o..|

      00 02 00 00 00 00 00 00     |........|

      3E 4F 52 41 2D 30 31 37     |>ORA-017|

      33 32 3A 20 64 61 74 61     |32:.data|

      20 6D 61 6E 69 70 75 6C     |.manipul|

      61 74 69 6F 6E 20 6F 70     |ation.op|

      65 72 61 74 69 6F 6E 20     |eration.|

      6E 6F 74 20 6C 65 67 61     |not.lega|

      6C 20 6F 6E 20 74 68 69     |l.on.thi|

      73 20 76 69 65 77 0A        |s.view. |

       

      Java Code:

       

      sql : select STATUS from mySchema.myTable where run_id = 1 FOR UPDATE OF STATUS ";

       

      PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

      stmt.setMaxRows(1);

      ResultSet rs = stmt.executeQuery();

      while(rs.next()) {

        String v = rs.getString(columnName);

        System.out.println("value "+v);

        rs.updateString(columnName, "PENDING");

        rs.updateRow();

      }

      rs.close();

      stmt.close();

      conn.close();

       

      Regards,

      Ritam.