I have used ROWID in stored procedures where I may
be updating the same row several times in the procedure.

the first time I needed the row, I selected the rowid into a variable called v_rowid, and the later I did all my updates
like this :

update table BLAH
set col1=whatever, col2 = whatever
where rowid = v_rowid;

I don't think rowid should be used in tables to reference rows
in other tables because the data will get messed up during
alter table MOVEs and export/import. I guess they are OK
in views though, if you have a need for that.

-John