Skip to Main Content
Forums

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

811879Jan 16 2020 — edited Jan 17 2020

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.
This post has been answered by 811879 on Jan 17 2020
Jump to Answer

Comments

EdStevens
948744 wrote:
Hi, 

I have been experimenting on exception handling in oracle plsql. During my experimentation I made the following anonymous plsql block.

<snip>

Did you notice the name of this particular forum?

Please close this thread and ask your question in 3077
BluShadow
Question now moved to Oracle Discussion Forums » Oracle Database » SQL and PL/SQL
BluShadow
Answer
The problem in your block 2 code is that you've not just used the same exception name, but you've assigned them with the same exception code. If the code is different then it will work...
SQL> set serverout on
SQL> ed
Wrote file afiedt.buf

  1  <<outer_block>>
  2  declare
  3      exc exception;
  4      pragma exception_init(exc,-20001);
  5  begin
  6      <<inner_block>>
  7      declare
  8          exc exception;
  9          pragma exception_init(exc,-20002);
 10      begin
 11          raise_application_error(-20002,'Error raised');
 12      exception
 13          when outer_block.exc then
 14              dbms_output.put_line('outer Exception caught ' );
 15          when inner_block.exc then
 16              dbms_output.put_line('Inner Exception caught ' );
 17      end;
 18* end;
SQL> /
Inner Exception caught

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  <<outer_block>>
  2  declare
  3      exc exception;
  4      pragma exception_init(exc,-20001);
  5  begin
  6      <<inner_block>>
  7      declare
  8          exc exception;
  9          pragma exception_init(exc,-20002);
 10      begin
 11          raise_application_error(-20001,'Error raised');
 12      exception
 13          when outer_block.exc then
 14              dbms_output.put_line('outer Exception caught ' );
 15          when inner_block.exc then
 16              dbms_output.put_line('Inner Exception caught ' );
 17      end;
 18* end;
SQL> /
outer Exception caught

PL/SQL procedure successfully completed.
Marked as Answer by sudher · Sep 27 2020
BluShadow
Oh, and the reason for that is described here...

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#sthref2000

PRAGMA is a compiler directive, not a run time directive, so you can't re-assign the same code to different exceptions on-the-fly during execution of your code, hence why the code was complaining because it was considering your outer EXC and inner EXC to be the same code and hence you were effectively testing for the same exception value twice, regardless of you prefixing the name with the outer/inner labels.

You can certainly re-use exception names at run time and that follows standard variable scoping rules, but if you assign exception names to actual error code values, those need to be either unique or, if you've assigned multiple exception names to the same code you need to ensure you only test for one of them within your exception handlers.
1 person found this helpful
1 - 4

Post Details

Added on Jan 16 2020
#11g, #java-database-connectivity-jdbc, #jdbc
2 comments
1,585 views