How do I get N rows in Oracle SQL SELECT statement given number by variable?

Question

In Oracle SQL, I have a variable that is the number of rows to fetch, I can only use SQL SELECT statement, so no PL/SQL block.

If the variable has a value set I must fetch the number of rows in the variable, if not then fetch as many rows as possible (infinite).

I tried:

select * from system_options
THEN FETCH FIRST
    CASE :lim
        THEN :lim
        ELSE 9999
    END
ROWS ONLY

This gives me a ORA-00933: SQL command not properly ended.

Another option would be by not having the variable set then not having the THEN FETCH statement.

Answer 1

You have several syntax errors:

• You do not need the THEN before the FETCH FIRST; and
• The CASE expression is missing a WHEN clause.

I don't have SQL/Plus to hand to test using a bind variable but something like this:

select * from system_options
FETCH FIRST CASE WHEN :lim IS NOT NULL THEN :lim ELSE 9999 END ROWS ONLY

Or, you can use COALESCE:

select * from system_options
FETCH FIRST COALESCE( :lim, 9999 ) ROWS ONLY

db<>fiddle here