ORA-01410 Invalid Rowid [message #296982] |
Tue, 29 January 2008 16:40  |
lotusdeva
Messages: 201 Registered: March 2005
|
Senior Member |
|
|
Hello --
I was wondering if anyone ran into the same problem. I have a procedure that updates 180k rows via a SELECT FOR UPDATE clause in cursor as follows (I wanted to lock these rows to make sure no other session modifies them):
PROCEDURE updateDtd (
p_code_in IN VARCHAR2,
p_upd IN NUMBER,
ernm OUT NUMBER,
erms OUT VARCHAR2)
IS
cd_fd NUMBER(20);
CURSOR c1
IS
SELECT some_code
FROM t1, t2
WHERE t1.id = t2.id
AND code_id = p_code_in
FOR UPDATE OF c1;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO cd_fd;
IF some_code IS NOT NULL THEN
UPDATE t1
SET de_code = p_upd
WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
errnum := SQLCODE;
errmsg := 'No records found';
WHEN OTHERS THEN
ernm := SQLCODE;
erms := SUBSTR (SQLERRM, 1, 100);
RAISE;
END updateDtd;
Upon the execution I get the following error: ORA-01410 Invalid Rowid
What am I doing wrong?
Oracle9i Enterprise Edition Release 9.2.0.6.0
Thank you!
|
|
|
Re: ORA-01410 Invalid Rowid [message #296993 is a reply to message #296982] |
Tue, 29 January 2008 20:04   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Is c1 a column of table t1? If not, then you are not locking the t1 row, and it can be deleted by another user. That would give you ORA-01410.
Ross Leishman
|
|
|
|
Re: ORA-01410 Invalid Rowid [message #297190 is a reply to message #296993] |
Wed, 30 January 2008 09:35   |
lotusdeva
Messages: 201 Registered: March 2005
|
Senior Member |
|
|
Yes, c1 is a column of t1. I updated the code, which I posted wrong...
PROCEDURE updateDtd (
p_code_in IN VARCHAR2,
p_upd IN NUMBER,
ernm OUT NUMBER,
erms OUT VARCHAR2)
IS
cd_fd NUMBER(20);
CURSOR c1
IS
SELECT some_code
FROM t1, t2
WHERE t1.id = t2.id
AND code_id = p_code_in
FOR UPDATE OF some_code;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO cd_fd;
IF some_code IS NOT NULL THEN
UPDATE t1
SET de_code = p_upd
WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
errnum := SQLCODE;
errmsg := 'No records found';
WHEN OTHERS THEN
ernm := SQLCODE;
erms := SUBSTR (SQLERRM, 1, 100);
RAISE;
END updateDtd;
|
|
|
Re: ORA-01410 Invalid Rowid [message #297192 is a reply to message #297052] |
Wed, 30 January 2008 09:43   |
lotusdeva
Messages: 201 Registered: March 2005
|
Senior Member |
|
|
WHEN NO_DATA_FOUND THEN
errnum := SQLCODE;
errmsg := 'No records found';
WHEN OTHERS THEN
errnum := SQLCODE;
errmsg := SUBSTR (SQLERRM, 1, 100);
RAISE;
Here is the outout with this code:
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "mypackage.my_procedure", line 54
ORA-01410: invalid ROWID
ORA-06512: at line 1
Here is the output without this code
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at "mypackage.my_procedure", line 56
ORA-06512: at line 1
|
|
|
|
|