[Solved] Offset/Fetch giving errors

Yes, you're right. That's another parser bug. I've opened a ticket (PLSQLCOP-269). Parse error when using expressions in offset/rowcount in row_limiting_clause.

The first issue is caused by dealing with keyword offset. It is allowed as table alias and that leads to a conflict. 

Right now the best what you can do is using simple variables or constants. This should work.

Can you please provide a full example? Thx

A simple example where I get the error:

SELECT naam
FROM dl.patient
OFFSET 10 ROWS
FETCH NEXT 3 ROWS ONLY;

Hmm, this looks like a parser bug. Thanks for reporting this. I'll open a ticket.

However, It usually does not make sense to use the row_limiting_clause without ordering the result. And since you asked for a workaround...

SELECT naam
  FROM dl.patient
 ORDER BY naam
OFFSET 10 ROWS
 FETCH NEXT 3 ROWS ONLY;

Using a subquery works as well: 

SELECT * FROM (SELECT naam FROM dl.patient) a
OFFSET 10 ROWS
 FETCH NEXT 3 ROWS ONLY;

When using expressions in the offset/fetch clause it also seems to go wrong (although it is ordered).

 e.g. the following query:

SELECT /*+ first_rows(25) */

                 api.patient_ot (patient_nr => p.patient_id

                                ,afdelings_code => p.afdeling_id

                                ,naam => p.naam

                                ,voornaam => CASE l_tonenvoornaam WHEN 1 THEN p.voornaam ELSE null END

                                ,geslacht => p.geslacht

                                ,geboortedatum => p.geboorte_dt

                                ,email => p.e_mail

                                ,rijksregister_nr => p.rijksregister_nr

                                ,gsm_nr => p.gsm_nr

                                ,adressen => NULL)

                ,COUNT (1) OVER ()

        BULK COLLECT INTO l_patientlist.items, l_rowcount

        FROM dl.patient p WHERE 1=1

        AND (p_naam is null OR p.naam LIKE p_naam)

        AND (p_voornaam is null OR p.voornaam LIKE p_voornaam)

        AND (p_patient_nr is null OR p.patient_id = p_patient_nr)

        AND (p_rijksregister_nr is null OR p.rijksregister_nr = p_rijksregister_nr)

        AND (p_geboortedatum is null OR p.geboorte_dt = TO_DATE(p_geboortedatum))

        AND (p_afdelings_code is null OR p.afdeling_id = p_afdelings_code)

        ORDER BY p.voornaam, p.naam

        OFFSET (l_patientlist.page - 1) * l_patientlist.limit ROWS

        FETCH NEXT l_patientlist.limit ROWS ONLY;

When putting the expressions in a variable it works fine.