Today I answered a question on otn regarding order by.
Let’s build a different test case.
You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column.
SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A;
SYS@LSC01> GRANT SELECT ON SCOTT.EMP TO USER_A WITH GRANT OPTION;
SYS@LSC01> CREATE VIEW USER_A.V AS SELECT ENAME FROM SCOTT.EMP
ORDER BY HIREDATE;
SYS@LSC01> GRANT CREATE SESSION TO USER_B IDENTIFIED BY B;
SYS@LSC01> GRANT SELECT ON USER_A.V TO USER_B;
SYS@LSC01> CONNECT USER_B/B
USER_B@LSC01> SELECT ROWNUM, ENAME FROM USER_A.V WHERE ROWNUM<4; ROWNUM ENAME ---------- ---------- 1 SMITH 2 ALLEN 3 WARD
To get top 3, that was easy 8)
USER_B@LSC01> SELECT R, ENAME FROM
2 (SELECT ROWNUM R,ENAME
3 FROM USER_A.V
4 ORDER BY ROWNUM DESC)
5 WHERE ROWNUM<4 ORDER BY R; R ENAME ---------- ---------- 12 MILLER 13 SCOTT 14 ADAMS
Here we have a sorted view. The OP had an external file. It could be an XML input, or any table or collection that is sorted but does not have a sort key