offset with parameter in PL/SQL procedure

I have a PL/SQL procedure. It executes a SQL statement and returns a json responce.

I would like to limit the rows returned using the offset clause. eg:

select *    
from wherever
where something = parameter
offset 0 rows 
fetch next 25 rows only;

However I don’t seem able to use variables to replace the 0 and the 25 in the example above.

Of course there are ways to work around this. I can write code like we had to before the offset clause appeared in 11g. But it looks ugly and possibly won’t perform as well….am I missing something. Can a parameter be used in the offset clause of SQL in a proc or cursor?

For example the following proc does compile but the cursor returned is always empty (its not when I hard code in values):

procedure test (pPageSize in pls_integer:=25, pPageno in pls_integer:=1, RecipeList out sys_refcursor) is
      vNextRows pls_integer;
      vOffset pls_integer; 
    begin
        vOffset:= pPageSize*(pPageno-1);
        vNextRows:= pPageSize;          

        open RecipeList 
            for
            select *      
            from recipes       
            order by recipeno
            offset vOffset rows 
            fetch next vNextRows rows only; 
       
end test;  

Answer

Like this:

declare
  start_row integer := 0;
  fetch_rows integer := 5;
begin
  
  dbms_output.put_line ( 'First ' || fetch_rows );
  for t in ( 
    select * from all_tables 
    offset start_row rows 
    fetch first fetch_rows rows only 
  ) loop
    dbms_output.put_line ( t.table_name );
  end loop;
  
  start_row := 5;
  dbms_output.put_line ( 'Next ' || fetch_rows );
  for t in ( 
    select * from all_tables 
    offset start_row rows 
    fetch first fetch_rows rows only 
  ) loop
    dbms_output.put_line ( t.table_name );
  end loop;
end;
/