Error : ORA-01410: invalid ROWID while executing a package [message #332701] |
Wed, 09 July 2008 07:00  |
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 #332719 is a reply to message #332701] |
Wed, 09 July 2008 08:18  |
 |
Kevin Meade
Messages: 2103 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
|
|
|