In Oracle when do I use the keyword ONLY in a query

I’m going through the Oracle certification course and I’m looking at the examples. The instructor isn’t really talking about when to use ONLY and when not to use ONLY. Is the ONLY keyword used in specific instances and is ONLY semantics or does it do something specific that I missed?

Here’s an example

SELECT product_name, quantity FROM inventories INNER JOIN products USING(product_id) ORDER BY quantity DESC FETCH NEXT 5 ROWS ONLY;

Is this required? If I don’t give the ONLY keyword will it make a difference?

Answer

SELECT product_name, quantity
FROM   inventories
       INNER JOIN products USING(product_id)
ORDER BY quantity DESC
FETCH NEXT 5 ROWS ONLY;

Is this required? If I don’t give the ONLY keyword will it make a difference?

Lets take a simple example and run it:

SELECT * FROM DUAL
ORDER BY DUMMY
FETCH NEXT 5 ROWS;

Outputs:

ORA-00905: missing keyword

Then:

SELECT * FROM DUAL
ORDER BY DUMMY
FETCH NEXT 5 ROWS ONLY;

Outputs:

DUMMY
X

db<>fiddle here

So, yes, a keyword is required; as you can see from the syntax diagram for the row limiting clause of the SELECT statement:

Then you must end the row limiting FETCH clause with either ONLY or WITH TIES.