Ok, so you make me think about it and -- well, there is a SQL solution after all (isn't there always....)
ops$tkyte@ORA920> create table t ( ss int, se int );
Table created.
ops$tkyte@ORA920> variable serial_start number;
ops$tkyte@ORA920> variable serial_end number;
ops$tkyte@ORA920> exec :serial_start := 30; :serial_end := 40;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select * from t;
SS SE
---------- ----------
40 140
60 145
100 110
150 250
ops$tkyte@ORA920> select max( decode(mod(rn,2), 1, r, null ) ),
2 max( decode(mod(rn,2), 0, r, null ) )
3 from (
4 select r, last_r, next_r, row_number() over ( order by r ) rn
5 from (
6 select r,
7 nvl(lag(r) over ( order by r )+1, -1) last_r,
8 nvl(lead(r) over ( order by r )-1, -1 ) next_r
9 from ( select rownum + :serial_start-1 r
10 from all_objects
11 where rownum <= :serial_end-:serial_start+1 )
12 where 0 = (select count(*)
13 from t where ss <= r
14 and se >= r
15 AND rownum = 1)
16 )
17 where r <> last_r or r <> next_r
18 )
19 group by ceil(rn/2)
20 /
MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
30 39
ops$tkyte@ORA920> exec :serial_start := 150; :serial_end := 250;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select * from t;
SS SE
---------- ----------
200 230
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select max( decode(mod(rn,2), 1, r, null ) ),
2 max( decode(mod(rn,2), 0, r, null ) )
3 from (
4 select r, last_r, next_r, row_number() over ( order by r ) rn
5 from (
6 select r,
7 nvl(lag(r) over ( order by r )+1, -1) last_r,
8 nvl(lead(r) over ( order by r )-1, -1 ) next_r
9 from ( select rownum + :serial_start-1 r
10 from all_objects
11 where rownum <= :serial_end-:serial_start+1 )
12 where 0 = (select count(*)
13 from t where ss <= r
14 and se >= r
15 AND rownum = 1)
16 )
17 where r <> last_r or r <> next_r
18 )
19 group by ceil(rn/2)
20 /
MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
150 199
231 250
ops$tkyte@ORA920> exec :serial_start := 90; :serial_end := 120;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select * from t;
SS SE
---------- ----------
50 98
100 150
160 190
200 230
ops$tkyte@ORA920> select max( decode(mod(rn,2), 1, r, null ) ),
2 max( decode(mod(rn,2), 0, r, null ) )
3 from (
4 select r, last_r, next_r, row_number() over ( order by r ) rn
5 from (
6 select r,
7 nvl(lag(r) over ( order by r )+1, -1) last_r,
8 nvl(lead(r) over ( order by r )-1, -1 ) next_r
9 from ( select rownum + :serial_start-1 r
10 from all_objects
11 where rownum <= :serial_end-:serial_start+1 )
12 where 0 = (select count(*)
13 from t where ss <= r
14 and se >= r
15 AND rownum = 1)
16 )
17 where r <> last_r or r <> next_r
18 )
19 group by ceil(rn/2)
20 /
MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
99
Now that works as long as the number of rows in all objects exceeds the difference between start and stop. If not, you can always go one more step:
ops$tkyte@ORA920> create or replace type numArray as table of number
2 /
Type created.
ops$tkyte@ORA920> create or replace function virtual_table( p_numrows in number ) return numArray
2 pipelined
3 as
4 begin
5 for i in 1 .. p_numrows loop
6 pipe row(i);
7 end loop;
8 return;
9 end;
10 /
Function created.
ops$tkyte@ORA920> variable serial_start number;
ops$tkyte@ORA920> variable serial_end number;
ops$tkyte@ORA920> exec :serial_start := 30; :serial_end := 40;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select * from t;
SS SE
---------- ----------
40 140
60 145
100 110
150 250
ops$tkyte@ORA920> select max( decode(mod(rn,2), 1, r, null ) ),
2 max( decode(mod(rn,2), 0, r, null ) )
3 from (
4 select r, last_r, next_r, row_number() over ( order by r ) rn
5 from (
6 select r,
7 nvl(lag(r) over ( order by r )+1, -1) last_r,
8 nvl(lead(r) over ( order by r )-1, -1 ) next_r
9 from ( select rownum + :serial_start-1 r
10 from TABLE(virtual_table(:serial_end-:serial_start+1))
11 )
12 where 0 = (select count(*)
13 from t where ss <= r
14 and se >= r
15 AND rownum = 1)
16 )
17 where r <> last_r or r <> next_r
18 )
19 group by ceil(rn/2)
20 /
MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
30 39
Now for the "how and why" it works.
Read the query from the inside out. Take each query and run it in turn (removing aggregates). Like this:
ops$tkyte@ORA920> select rownum + :serial_start-1 r
2 from TABLE(virtual_table(:serial_end-:serial_start+1))
3 /
R
----------
30
31
32
33
34
35
36
37
38
39
40
11 rows selected.
<b>there is every number in our "series"...</b>
ops$tkyte@ORA920> select r,
2 nvl(lag(r) over ( order by r )+1, -1) last_r,
3 nvl(lead(r) over ( order by r )-1, -1 ) next_r
4 from ( select rownum + :serial_start-1 r
5 from TABLE(virtual_table(:serial_end-:serial_start+1))
6 )
7 where 0 = (select count(*)
8 from t where ss <= r
9 and se >= r
10 AND rownum = 1)
11 /
R LAST_R NEXT_R
---------- ---------- ----------
30 -1 30
31 31 31
32 32 32
33 33 33
34 34 34
35 35 35
36 36 36
37 37 37
38 38 38
39 39 -1
10 rows selected.
<b>now for each number in our series -- only keep the ones that are NOT already in the table (thats what 0 = (select count(*) ...) does.
For each one not in the table already -- look backwards and forwards in the set and grab the prior/next R. If null (end points in the set), use -1 (you said they must be > 0 so that is "safe")
</b>
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select r, last_r, next_r, row_number() over ( order by r ) rn
2 from (
3 select r,
4 nvl(lag(r) over ( order by r )+1, -1) last_r,
5 nvl(lead(r) over ( order by r )-1, -1 ) next_r
6 from ( select rownum + :serial_start-1 r
7 from TABLE(virtual_table(:serial_end-:serial_start+1))
8 )
9 where 0 = (select count(*)
10 from t where ss <= r
11 and se >= r
12 AND rownum = 1)
13 )
14 where r <> last_r or r <> next_r
15 /
R LAST_R NEXT_R RN
---------- ---------- ---------- ----------
30 -1 30 1
39 39 -1 2
<b>Next, we only keep the ones where the prior or next R is NOT minus or plus 1... Also, we assign row_number() to each one in turn -- 1,2,3,4,5,6.... we'll use this to pivot below. Each ODD row will be a start point, each EVEN row an end point:</b>
ops$tkyte@ORA920> select ( decode(mod(rn,2), 1, r, null ) ),
2 ( decode(mod(rn,2), 0, r, null ) ),
3 ceil(rn/2)
4 from (
5 select r, last_r, next_r, row_number() over ( order by r ) rn
6 from (
7 select r,
8 nvl(lag(r) over ( order by r )+1, -1) last_r,
9 nvl(lead(r) over ( order by r )-1, -1 ) next_r
10 from ( select rownum + :serial_start-1 r
11 from TABLE(virtual_table(:serial_end-:serial_start+1))
12 )
13 where 0 = (select count(*)
14 from t where ss <= r
15 and se >= r
16 AND rownum = 1)
17 )
18 where r <> last_r or r <> next_r
19 )
20 /
(DECODE(MOD(RN,2),1,R,NULL)) (DECODE(MOD(RN,2),0,R,NULL)) CEIL(RN/2)
---------------------------- ---------------------------- ----------
30 1
39 1
<b>see how the decode makes a "sparse matrix", if we had more in the series -- like
MAX(DECODE(MOD(RN,2),1,R,NULL)) MAX(DECODE(MOD(RN,2),0,R,NULL))
------------------------------- -------------------------------
150 199
231 250
did, this would look like:
150 1
199 1
231 2
250 2
and the max "squishes out" the nulls for us nicely</b>
QED ;)