Home » SQL & PL/SQL » SQL & PL/SQL » Error : ORA-01410: invalid ROWID while executing a package
Error : ORA-01410: invalid ROWID while executing a package [message #332701] Wed, 09 July 2008 07:00 Go to next message
RaajG
Messages: 1
Registered: July 2008
Junior Member
When a package is executed, i get the error
ORA-01410: invalid ROWID

Can anyone let me know what could be the cause for this error.Please note that the similar code works fine in other databases.

All databases are in 11.5.10 (10g) version.
Re: Error : ORA-01410: invalid ROWID while executing a package [message #332708 is a reply to message #332701] Wed, 09 July 2008 07:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
http://ora-01410.ora-code.com/

[Updated on: Wed, 09 July 2008 07:20]

Report message to a moderator

Re: Error : ORA-01410: invalid ROWID while executing a package [message #332719 is a reply to message #332701] Wed, 09 July 2008 08:18 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
This error is sometimes elusive. The following code snippet demonstrates two things: 1) that the error is not what most people think it is, 2) the most common example of how to get the error. The most common case of the error is application mistakes wherein the app constructs a rowid that does not conform to rowid structure for your database. The structure of rowid has changed once over the releases so many people converting databases can get this error. By no means is the necessarily the reason for your error but these insights may help you.

create table a (a number);
insert into a values (1);
commit;

set serveroutput on

declare
   arowid_v rowid;
begin
   dbms_output.put_line('getting the row from table a');
   select a.rowid into arowid_v from a where a = 1;
   dbms_output.put_line('row found, not deleteing the row');
   delete from a;
   dbms_output.put_line('row deleted, now trying to find the row again using rowid');
   select a.rowid into arowid_v from a where rowid = arowid_v;
end;
/

select * from a where rowid = null;

select * from a where rowid = 'abc';


I am posting responses to the three "queries" above here, as a courtesy to others reading this thread, but I expect you will run this code to see for yourself what happens.

SQL> declare
  2     arowid_v rowid;
  3  begin
  4     dbms_output.put_line('getting the row from table a');
  5     select a.rowid into arowid_v from a where a = 1;
  6     dbms_output.put_line('row found, not deleteing the row');
  7     delete from a;
  8     dbms_output.put_line('row deleted, now trying to find the row again using rowid');
  9     select a.rowid into arowid_v from a where rowid = arowid_v;
 10  end;
 11  /
getting the row from table a
row found, not deleteing the row
row deleted, now trying to find the row again using rowid
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9

SQL> select * from a where rowid = null;

no rows selected

SQL> select * from a where rowid = 'abc';
select * from a where rowid = 'abc'
                              *
ERROR at line 1:
ORA-01410: invalid ROWID


As you can see, the error you describe does not occurr when looking for a row with a rowid that no longer exists. It also does not occurr when using a null value. It happens when using a value that has the wrong structure for your database.

This is at least the most common reason.

Good luck, Kevin


Previous Topic: A SIMPLE QUERY ABOUT ORACLE JOBS
Next Topic: using SYSDATE (code interpretation)
Goto Forum:
  


Current Time: Sun Dec 04 04:34:20 CST 2016

Total time taken to generate the page: 0.12271 seconds