Oracle 11g OFFSET FETCH gives error

Question

I am making a query and trying to fetch a random number of rows from a database but Oracle keeps telling me my command has not been ended properly.

select *
from random_table
order by random_column_name
offset 0 rows
fetch first 10 rows only

This is the code I'm using.

I would greatly appreciate any support since I've spent quite some time searching for an explanation for the outcome I'm getting, but to no avail.

Answer 1

That syntax isn't valid until Oracle Database 12c.

You would say instead

select *
from random_table
where rownum < 11
order by random_column_name;

Answer 2

Oracle (works on the versions before 12c):

select *
from (
  select *, rownum rn
  from (
    select *
    from random_table
    order by random_col) t
  where rownum <= 20) f
where rn > 10

Answer 3

None of the above worked for me in Oracle 11.2, so here's another way

select * from (
  select random_table.*,
         row_number() over (ORDER BY update_date DESC) line_number
   FROM random_table where status = 100
) WHERE line_number between 5 AND 10

In the example above, I've added filtering on my status and ordering by my latest update_date, but could be anything.

Here the pagination is determined by start point (5) and end point (10), shown above. This makes it easy to calculate based upon page number and page size (page 2 with page size 5 in this case), if needed.