CODEWITHSUNDEEP
oracle-databaseplsqlcursor
Centurion
Centurion

Reputation: 14304

How to update cursor records using WHERE CURRENT OF?

I'm getting "ORA-01410: Invalid ROWID" exception when executing this block. Any ideas why?

DECLARE
  CURSOR c_orders IS
    SELECT * from orders FOR UPDATE OF no;
  v_order_record c_orders%ROWTYPE;
BEGIN
  OPEN c_orders; 
  LOOP
    FETCH c_orders INTO v_order_record;
    UPDATE orders SET no = 11 WHERE CURRENT OF c_orders;
    EXIT WHEN c_orders%NOTFOUND; 
  END LOOP;  
  CLOSE c_orders; 
END;

However, everything works if using FOR IN syntax:

DECLARE
  CURSOR c_orders IS
    SELECT * from orders FOR UPDATE OF no;
BEGIN
  FOR rec IN c_orders
  LOOP
    UPDATE orders SET no = 11 WHERE CURRENT OF c_orders;
  END LOOP; 
END;

Upvotes: 1

Views: 31406

Answers (1)

Mat
Mat

Reputation: 206699

Move the exit when to before the update - you can't update something that doesn't exist.

Upvotes: 4

Related Questions