ORA-01410: invalid ROWID

oracleplsql

I get ORA-01410: Invalid ROWID error when executing the following piece of code:

DECLARE
        CURSOR c1 IS
      SELECT e.empno,
             e.ename,
             e.sal,
             d.dname
      FROM   emp e,
             dept d
      WHERE  e.deptno = d.deptno
             AND e.deptno = 10
      FOR UPDATE OF sal NOWAIT;
    c2 c1%ROWTYPE;
BEGIN
    OPEN c1;

    LOOP
        FETCH c1 INTO c2;

        Exit WHEN c1%NOTFOUND;

        IF c2.dname = 'ACCOUNTING' THEN
          UPDATE emp1
          SET    sal = sal + 1000
          WHERE  CURRENT OF c1;
        END IF;
    END LOOP;
END;
/

Please suggest a way to overcome it.

Best Answer

In your code, you are locking the following records in the c1 cursor result set for the column sal:

     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            9451 ACCOUNTING
      7839 KING            12001 ACCOUNTING
      7934 MILLER           8301 ACCOUNTING

For example, from another session this query

SELECT sal FROM emp FOR UPDATE;

will block until the next commit or rollback statement will be issued (check out v$session).

I guess the problem is that you are updating the sal column values of table emp1 (and not emp one), but these records are not locked. You plan on updating emp1 records that have not been referenced by the previous SELECT FOR UPDATE statement, so their ROWIDs become invalids. From here: The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

In fact, if you change the code like this:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp1
    SET    sal = sal + 1000;
END IF;
...

or if you replace emp1 with emp:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp
    SET    sal = sal + 1000;
    WHERE  CURRENT OF c1;
END IF;
...

it would work.

Related Question