Can one retrieve only rows X to Y from a table?

SELECT * FROM (
 SELECT ename, rownum rn
          FROM emp WHERE rownum < 101
) WHERE  RN between 91 and 100 ;

Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).

SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:

SELECT *
FROM   tableX
WHERE  rowid in (
 SELECT rowid FROM tableX
  WHERE rownum <= 7
MINUS
 SELECT rowid FROM tableX
 WHERE rownum < 5);

The generic solution to get full information of rows between x and y

---

SELECT * FROM emp WHERE empno in (SELECT empno FROM emp GROUP BY rownum,empno HAVING rownum BETWEEN &x AND &y);