Pulling object array with multiple threads from an Oracle table by row locking PL/SQL function

Sometimes we encounter a situation that we have many Java threads pulling a record list from an Oracle table via JDBC continuously and process them in any manner. In these situations we may want such a case that one record should be selected by only one thread so that it can be processed only once. Then we meet with this solution:

Select the records which are not selected before with locking them and update their status as selected so that they can not be selected by different thread again.

In the example below i tried to show how to overcome such a problem with a locking cursor. The variables in curly brackets can be customized. They can be your own objects.

CREATE OR REPLACE FUNCTION pull_records return {new collection of object type} is
rowidarr      {new collection of varchar2};
recordarr     {new collection of object type};

CURSOR curpull IS SELECT ROWIDTOCHAR(ROWID) FROM {TABLE_NAME} where {pulled_before = false} FOR UPDATE NOWAIT SKIP LOCKED;

begin
    OPEN curpull();
    FETCH curpull BULK COLLECT INTO rowidarr LIMIT 1;
    CLOSE curpull;
    
    IF rowidarr IS NULL THEN
      rowidarr := {new table of varchar2};
    END IF;   

    -- update pulled records as pulled_before so that they are not selected in the next cycle
    FORALL i IN NVL(rowidarr.FIRST, 1) .. NVL(rowidarr.LAST, -1)
      UPDATE {TABLE_NAME} e SET {e.pulled_before = true} WHERE ROWID = CHARTOROWID(rowidarr(i))   RETURNING value(e) BULK COLLECT INTO recordarr;
    
    -- if recordarr is null then return an empty array 
    IF recordarr IS NULL THEN
      recordarr := {new collection of object type};
    END IF;
    
    COMMIT;
    return recordarr;
    
    EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
end;

The key section in the code above is FOR UPDATE NOWAIT SKIP LOCKED. This does the job for us. It locks the selected rows and we update the indicator field after locking. Then after committing, the rows are free with their new selected before statuses :)) So the other threads never see them in the "not pulled" state.