Forum Stats

  • 3,758,427 Users
  • 2,251,387 Discussions
  • 7,870,189 Comments

Discussions

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

811879
811879 Member Posts: 7

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.

Tagged:

Best Answer

  • 811879
    811879 Member Posts: 7
    edited Jan 17, 2020 4:39AM Accepted Answer

    I am able to resolve this issue. In Our new DB "View Merging" was turned off by DBA as we were getting error  "ORA-00979: not a group by expression" while executing one of our stored proc. In oracle debug log  I saw "SELECT FOR UPDATE" query was translated into the following update statement and which is eventually using a temp table/view.

    update (select rowid as "__Oracle_JDBC_interal_ROWID__", STATUS from  mySchema.myTable where run_id = 1 ) set STATUS = 'PENDING' WHERE  ROWID = 'AAAjyeACgAAAAVvAAA';

    Just for reference

    Oracle 12c Bug : ORA-00979

    http://nimishgarg.blogspot.com/2017/06/complex-view-merging-query.html

    Learning something new everyday.

Answers

  • 811879
    811879 Member Posts: 7
    edited Jan 17, 2020 4:39AM Accepted Answer

    I am able to resolve this issue. In Our new DB "View Merging" was turned off by DBA as we were getting error  "ORA-00979: not a group by expression" while executing one of our stored proc. In oracle debug log  I saw "SELECT FOR UPDATE" query was translated into the following update statement and which is eventually using a temp table/view.

    update (select rowid as "__Oracle_JDBC_interal_ROWID__", STATUS from  mySchema.myTable where run_id = 1 ) set STATUS = 'PENDING' WHERE  ROWID = 'AAAjyeACgAAAAVvAAA';

    Just for reference

    Oracle 12c Bug : ORA-00979

    http://nimishgarg.blogspot.com/2017/06/complex-view-merging-query.html

    Learning something new everyday.

  • 811879
    811879 Member Posts: 7
    edited Jan 17, 2020 6:38AM

    Fixed by turning "View Merging" On..