Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01410 Invalid Rowid
ORA-01410 Invalid Rowid [message #296982] Tue, 29 January 2008 16:40 Go to next message
lotusdeva
Messages: 198
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 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
FOR UPDATE OF c1

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 #297052 is a reply to message #296982] Wed, 30 January 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the purpose of:
    WHEN OTHERS THEN
       ernm := SQLCODE;
       erms := SUBSTR (SQLERRM, 1, 100);
       RAISE;

If you don't do that, your caller still know the error number and message and more it can know at which line there was an error, something it can no more know now.

Regards
Michel
Re: ORA-01410 Invalid Rowid [message #297190 is a reply to message #296993] Wed, 30 January 2008 09:35 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Go to previous messageGo to next message
lotusdeva
Messages: 198
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


Re: ORA-01410 Invalid Rowid [message #297194 is a reply to message #297192] Wed, 30 January 2008 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lotusdeva wrote on Wed, 30 January 2008 16:43
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


Ah! the harmful power of "when others".
You hide the actual error with an artificial one.
Thank you, thank you so much for this.
I can't say how much I thank you for this.

Regards
Michel

Re: ORA-01410 Invalid Rowid [message #297197 is a reply to message #297194] Wed, 30 January 2008 10:27 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
never thought that WHEN OTHERS was harmful, but I can see how it can be now.. Razz
Previous Topic: After-update trigger
Next Topic: NUMTODSINTERVAL bring back same dates
Goto Forum:
  


Current Time: Mon Dec 05 10:36:54 CST 2016

Total time taken to generate the page: 0.04866 seconds