Very Computer

Here's something that I'm trying to find a justification for at the behest of
a younger and less experienced colleague: Why, when one selects from a table
via pseudocolumn ROWID, and the ROWID is not in the specified table, does
Oracle give you "1410-invalid ROWID" rather than "1403-no data found"?

I've tried this not only with ROWIDs that I've made up (see below), but also
with valid ROWIDs that exist in other tables (so, clearly it _is_ a valid
rowid, just not for the table in question).  Any takers on this one?

-----

PL/SQL Release 2.1.3.2.1 - Production

SQL> select rowid from dual;

ROWID
------------------
00000324.0000.0001

SQL> select * from dual where rowid = '00000324.0000.0001';

D
-
X

SQL> select rowid from dual where rowid = '00000324.0000.0002';
ERROR:
ORA-01410: invalid ROWID

no rows selected

I noticed that the "invalid ROWID" message occurs if the block and file portions
of the rowid (the first and last pieces of the rowid) are not part of the table,
or are past the table's high water mark.  The "invalid ROWID" message does not
occur if the block and file portions are valid for that table, but the offset
(the middle piece) points to a row that does not exist.  In your second select
statement, you are referencing a file number (0002) that perhaps doesn't exist
in your system.

The format of rowid is:

block.offset_in_block.file

--
Roger Harris
DBA
Kraft Foods
White Plains, NY