To address the reason for this:
RowNum is a pseudo-column supplied by Oracle. It is generated while the SELECT-clause is being processed. Since the WHERE-clause is handled before the SELECT-clause, the
RowNum does not have a proper value yet.
One can argue whether or not it makes sense to have Oracle throw an exception in situation, but because
RowNum still is a pseudo-column it's still valid to have it there.
Note: Don't confuse this with
RowId, which is an entire different story!
Note that what I wrote about
RowNum is only true for
IN () and maybe others. If you check for, e.g.
RowNum < 10, you only get nine records!? I don't know why that is the case!