How to get lowest uninterrupted sequence of numbers

We have an application that needs to get n number of order items BUT(a) the first item number must be the lowest available
(b) the n number selected must all be sequential numbers.

so ... if I had a couple of million order items with item numbers 2300003 to 4504003, for example; and I needed 200 items, I'd like the first to be 2300003 and the last to be 2300203. However I don't want to (or I'd prefer not to) lock the entire table whilst I do this. There might be, in treality, several hundred thousand rows and many concurrent users.

Is locking FOR UPDATE the only real way of doing this? I suspect it is.

U may use "pivot" tables method for ur problem:
1. create some type of numbers
2. create "pivot" function that has to return list of order ids.
3. use table(cast()) construction for return needed result
example:

Code:

---

create or replace type tb_orders as table of number;

create or replace
function get_orders_id( id_start number, id_end date ) return tb_orders is
  tb tb_orders := tb_orders();
  j pls_integer := 0;
  i pls_integer := 0;
begin
  for i in id_start..id_end loop
    j := j + 1;
    tb(j) := i;
  end loop;
  return tb;
end;
/

---

Then u can use selects like following:

Code:

---

select *
from ur_order_table o,
    table(cast(get_orders_id(2300003,2300203) as tb_orders)) t
where t.column_value = o.order_id;

---

or

Code:

---

select * from ur_order_table o
where o.order_id in table(cast(get_orders_id(2300003,2300203) as tb_orders))
for update of o.order_info;

---