I have an ITEM table with one of the column as CREATED_DATE. In a clustered enviroment, many copies of services will pick items from this table and process it. Each service should pick the oldest 10 items from the ITEM table.
I am able to select top 10 rows using this in a Stored Procedure:
select * from (
select item_id, row_number() over (order by CREATED_DATE) rownumber
FROM item )
where rownumber < 11
Since many service should use this, I am using select ... for update to update the rows as "processing". But the below FOR UPDATE statement, fails for the above select statement with error "ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."
OPEN items_cursor FOR
**select Statement**
FOR UPDATE;
Please help me with a solution.
DCookie's answer doesn't solve multisession processing (it's just FOR UPDATE syntax fix). If you won't manipulate rownumber range, every instance of service if going to select for update the same rows. If you execute that_for_update_select in two sessions, the second one is going to wait until first finishes the transaction. Parallel processing will be an illusion.
I would consider efficient bulk processing together with for update skip locked approach. My answer below:
declare
con_limit constant number default 10;
cursor cItems is
select i.item_id, i.created_date
from item i
order by i.created_date
for update skip locked;
type t_cItems is table of cItems%rowtype;
tItems t_cItems;
begin
open cItems;
while true loop
fetch cItems bulk collect into tItems limit con_limit;
-- processing tItems
exit when tItems.count < con_limit;
end loop;
end;
Possible long transaction could be a disadvantage. Consider using Oracle Streams Advanced Queuing (DBMS_AQ) as an alternative to this solution.