How to skip/offset rows in Oracle database?

Question

I am writing a very simple query for an Oracle DB (version 9).

Somehow I can get first 5 rows:

select * from cities where rownum <= 5

But skipping 5 rows returns an empty result:

select * from cities where rownum >= 5

Using:

• Oracle SQL Developer
• Oracle DB version 9

Why is the second query returning an empty result?

Answer 1

In Oracle Database 12c (release 1) and above, you can do this very simple, for skip 5 rows:

SELECT * FROM T OFFSET 5 ROWS

and for skip 5 rows and take 15 rows:

SELECT * FROM T OFFSET 5 ROWS FETCH NEXT 15 ROWS ONLY