Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;
10 rows affected

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;


VAL


10

9

8

7

6


SELECT "A1"."VAL" "VAL" FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" FROM "ROWNUM_ORDER_TEST" "A2") "A1" WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC


VAL


10

9

8

7

6


declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY',
        output_sql_text => x);
        
  dbms_output.put_line(x);
end;
/
1 rows affected

dbms_output:
SELECT "A1"."VAL" "VAL" FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" FROM "FIDDLE_XKGHWTJGZUDFARCLOYEE"."ROWNUM_ORDER_TEST" "A2") "A1" WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES',
        output_sql_text => x);
        
  dbms_output.put_line(x);
end;
/
1 rows affected

dbms_output:
SELECT "A1"."VAL" "VAL" FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank" FROM "FIDDLE_XKGHWTJGZUDFARCLOYEE"."ROWNUM_ORDER_TEST" "A2") "A1" WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
        output_sql_text => x);
        
  dbms_output.put_line(x);
end;
/


1 rows affected

dbms_output:
SELECT "A1"."VAL" "VAL" FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber" FROM "FIDDLE_XKGHWTJGZUDFARCLOYEE"."ROWNUM_ORDER_TEST" "A2") "A1" WHERE "A1"."rowlimit_$$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4 ORDER BY "A1"."rowlimit_$_0"