Database Reference
In-Depth Information
EODA@ORA12CR1> create index
2 t_idx on
3 t( decode( processed_flag, 'N', 'N' ) );
Index created.
EODA@ORA12CR1> insert into t
2 select r,
3 case when mod(r,2) = 0 then 'N' else 'Y' end,
4 'payload ' || r
5 from (select level r
6 from dual
7 connect by level <= 5)
8 /
5 rows created.
EODA@ORA12CR1> select * from t;
ID P PAYLOAD
---------- - --------------------
1 Y payload 1
2 N payload 2
3 Y payload 3
4 N payload 4
5 Y payload 5
Then we basically need to find any and all unprocessed records. One by one we ask the database “Is this row
locked already? If not, then lock it and give it to me.” That code would look like this:
EODA@ORA12CR1> create or replace
2 function get_first_unlocked_row
3 return t%rowtype
4 as
5 resource_busy exception;
6 pragma exception_init( resource_busy, -54 );
7 l_rec t%rowtype;
8 begin
9 for x in ( select rowid rid
10 from t
11 where decode(processed_flag,'N','N') = 'N')
12 loop
13 begin
14 select * into l_rec
15 from t
16 where rowid = x.rid and processed_flag='N'
17 for update nowait;
18 return l_rec;
19 exception
20 when resource_busy then null;
when no_data_found then null;
21 end;
22 end loop;
