ORA-01410: invalid ROWID tips Oracle Error Tips by Burleson Consulting (S. Karam)

 

The Oracle docs note this on the ora-01410 error*:

Oracle Faqs contained this thread concerning ORA-01410, in which a resolution was found:

Question:

From: Poratips

I am running a sql update on a daily basis. Last weekend I got following error:

ORA-01410: invalid ROWID

and the table was locking.

The users were also trying to insert/update the record and it was locking for them. Our DBA found that the update I am running is causing the problem and the lock. This is a very serious situation, as it's causing a problem with our online application.

Could you please tell me what's the wrong with this code? It was running fine, and we only seem to be encountering the problem on Sundays. It's still running ok, but another department wants me to change the code. They are asking me to change the logic so it will not cause the locking problem. How can I prevent the locking?

Here's some more information:

DCS_INVENTORY table has records: 7500
NR_INVENTORY_UPLOAD has records:7000

It's updating almost 5700 records. Here's the update I'm using:

UPDATE
========
UPDATE DCS_INVENTORY A
SET (A.STOCK_LEVEL) =
(select X.STOCK_LEVEL
from NR_INVENTORY_UPLOAD X
WHERE X.inventory_id = A.INVENTORY_ID
and X.STOCK_LEVEL != 0 and STOCK_LEVEL != 0)
where exists (select X.STOCK_LEVEL
from NR_INVENTORY_UPLOAD X
WHERE X.inventory_id = A.INVENTORY_ID
and X.STOCK_LEVEL != 0 and STOCK_LEVEL != 0);
COMMIT;

Answer:

From: Poratips

I have implemented procedure to handle this situation:

CREATE OR REPLACE PROCEDURE SP_STOCK_LEVEL_UP
IS
l_cnt number := 0;
CURSOR C1 IS Select A.stock_level current_level, x.stock_level new_level
From DCS_INVENTORY A , NR_INVENTORY_UPLOAD X
WHERE X.inventory_id = A.INVENTORY_ID
and X.STOCK_LEVEL != 0
and A.STOCK_LEVEL != 0
FOR UPDATE NOWAIT;
BEGIN
FOR CREC IN C1 LOOP
update DCS_INVENTORY
SET STOCK_LEVEL = CREC.NEW_LEVEL
WHERE CURRENT OF C1;
END LOOP;
COMMIT;
dbms_OUTPUT.PUT_LINE('update DONE');
l_cnt := l_cnt+1;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -54
THEN
dbms_OUTPUT.PUT_LINE('YOU CANNOT UPDATE, PLEASE WAIT');
ROLLBACK;
ELSE
raise;
dbms_OUTPUT.PUT_LINE('ERRORS!');
ROLLBACK;
END IF;
END;
/