I got an requirement to write a procedure to fetch the records (which are in READY status to process) in FIFO basis from Inbound_ledger table and send to App for further processing.
RECORD_ID column is primary key.
So I am planning to put below update statement first which updates the status to IN_PROGRESS and pass those records to app.
UPDATE Inbound_ledger a
SET status = 'IN_PROGRESS'
binding_ID = p_binding_id -- Unique Parameter passed from procedure for each call.
WHERE a.record_id IN (SELECT record_id FROM
(SELECT record_id,rownum sort_order
FROM Inbound_ledger
WHERE status = 'READY'
ORDER BY updated_on)
WHERE sort_order <= 5000);
-- Returning the records for futher processing.
OPEN C1 FOR
SELECT * FROM Inbound_ledger
where binding_id = p_binding_id and status = 'IN_PROGRESS';
Please advise alternate to above update statement to get records of FIFO.