Oracle: how to limit number of rows in "select .. for update skip locked"

Question

I have got a table:

table foo{
  bar number,
  status varchar2(50)
}

I have multiple threads/hosts each consuming the table. Each thread updates the status, i.e. pessimistically locks the row.

In oracle 12.2.

select ... for update skip locked seems to do the job but I want to limit number of rows. The new FETCH NEXT sounds right, but I cant get the syntax right:

SELECT * FROM foo ORDER BY bar 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY 
FOR UPDATE SKIP LOCKED;

What is the simplest way to achieve this, i.e. with minimum code¹ (ideally without pl/sql function)?

I want something like this:

select * from (select * from foo 
               where status<>'baz' order by bar
) where rownum<10 for update skip locked

PS 1. We are considering moving away from oracle.

Answer 1

I suggest to create pl/sql function and use dynamic sql to control the number of locked records. The lock is acquired at a fetch time. So fetching N records automatically locks them. Keep in mind that records are unlocked once you finish the transaction - commit or rollback. The following is the example to lock N records and return their id values as an array (assume you have added the primary key ID column in your table):

create or replace function get_next_unlocked_records(iLockSize number)
return sys.odcinumberlist
is
  cRefCursor sys_refcursor;
  aIds       sys.odcinumberlist := sys.odcinumberlist();
begin
  -- open cursor. No locks so far
  open cRefCursor for 
    'select id from foo '||
    'for update skip locked';

  -- we fetch and lock at the same time 
  fetch cRefCursor bulk collect into aIds limit iLockSize;

  -- close cursor
  close cRefCursor;

  -- return locked ID values, 
  -- lock is kept until the transaction is finished
  return aIds; 

end;

sys.odcinumberlist is the built-in array of numbers.

Here is the test script to run in db:

declare 
  aRes sys.odcinumberlist;
begin
  aRes := get_next_unlocked_records(10);
  for c in (
    select column_value id
    from   table(aRes)
  ) loop
    dbms_output.put_line(c.id);
  end loop;
end;