Hi Tom,

first at all, thanks for all your support. I wish a blessed christmas time for you, your team and families.


I´m looking forward for the best practise of a cursor to fetch just one (or a few) row(s) on my own sorting and lock them. Without locking the whole resultset or include all rows with the condition.

select *
  from MyTable
 where < MyCondition >
 order By < MyOrderby >
 fetch first 1 row only
   for update skip locked;

Unfortunately the combination of fetch-first-only and for update causes a ORA-02014 :(

- without fetch-first-only it will lock the whole resultset
- a rownum condition instead of fetch-first-only will not consider my orderby
- an inner subselect with fetch-first-only will not skip the locked rows and finally fetch zero rows if the inner sql only fetch already locked rows

select *
  from MyTable
 where Rowid in (select Rowid
                   from MyTable
                  where < MyCondition >
                  order By < MyOrderby >
                  fetch first 1 row only)
   for update skip locked;

Whats your best practice or advice?

Thanks, Norman

declare
  l_cursor sys_refcursor;
  l_row emp%rowtype;
begin
  open l_cursor for
    SELECT * FROM emp ORDER BY empno FOR UPDATE SKIP LOCKED;
  fetch l_cursor into l_row;
  if l_cursor%found then
    dbms_output.put_line('locked just the first row');
  end if;
  close l_cursor;
end;
/