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.