Re: A RowId in Oracle 8i

"Bynia Doyle" <bynia.doyle_at_bt.com> wrote in message news:95eilr$j87$1_at_pheidippides.axion.bt.co.uk...
> Hi,
> In 7.3.4 I I used Rowids in a statement that was passed to dbms_sql as as
> follows:
> Delete from Table where Rowid in (Rowid1, Rowid2,..., Rowidn).
>
> The (Rowid1, Rowid2,..., Rowidn) was stored in a variable of type VARCHAR2
> and was construted in a loop.
>
> When I'm attempting to do use the same approach in Oracle 8.1.6 I get
> ORA-01410: invalid ROWID
>
> I gather that the data type of RowId is not the same as it was in 7.3.4 -
 any
> ideas on the changes to the statement to get it to work?
>

In Oracle 7, rowid's consisted of the Block Number, the Row Number within the Block, and the data File Number where the block resided.

That File Number was absolute -you were allowed 1023 datafiles per database, and no exceptions, and hence if you told me it was File 6, there could be no discussion as to what file you meant.

Oracle 8 allowed 1023 datafiles *per tablespace*, but the counter gets reset for the 1024th file in tablespace 2 -so there is now the possibility of multiple File 6's (ie, File 6 in DATA01 and File 6 in INDEX05). To get round that, the ROWID was changed to be the OBJECT Number, plus the File Number, plus the Block Number plus the Row Number.

So not only is there a new piece of information, but the order of the old information was also changed.

Allegedly this is not a problem until you try and add the 1024th file to the database (ie, you are supposed to be able to use the old format even in Oracle 8 and above, provided you don't have more than the formerly-allowed 1023 datafiles). Clearly, the reality is not quite living up to the promise.

Check out DBMS_ROWID. It's a package that allows you to manipulate and convert between the rowid's.

And, although we all know that selection by ROWID is the fastest method of retrieving data, I think this points up the very handy lesson that you use ROWID in code at your peril, since Oracle can (and probably will again) change it to suit its purposes.

Regards
HJR
> Thanks,
> Bynia.
>
> Bynia.
>
> bynia.doyle_at_bt.com
>
> ** All possible disclaimers apply **
Received on Sun Feb 04 2001 - 00:41:37 CST