If you were on 12c, pattern matching (match_recognize) is the way to go here:
with rws as (
select 19 n from dual union all
select 20 n from dual union all
select 26 n from dual union all
select 28 n from dual union all
select 29 n from dual union all
select 32 n from dual union all
select 33 n from dual union all
select 34 n from dual
)
select *
from rws match_recognize (
order by n
measures
count(*) num#,
first ( n ) st,
last ( n ) en
pattern ( strt cons{0,1} )
define
cons as n = prev ( n ) + 1
)
NUM# ST EN
2 19 20
1 26 26
2 28 29
2 32 33
1 34 34
As you're not, you'll need something a little more convoluted...
You can use the Tabititosan method to split the rows into groups of consecutive values.
To then further subdivide these into pairs, one approach is:
For each set of consecutive values, calculate a new row number. Then divide this by 2. And take the ceiling of this value.
Add this to the original group to convert them to pairs:
with rws as (
select 19 n from dual union all
select 20 n from dual union all
select 26 n from dual union all
select 28 n from dual union all
select 29 n from dual union all
select 32 n from dual union all
select 33 n from dual union all
select 34 n from dual
), grps as (
select n,
n - row_number ()
over ( order by n ) grp,
n - row_number ()
over ( order by n )
+ ceil ( row_number ()
over ( order by n ) / 2 )
grp2
from rws
), pairs as (
select grp, n,
grp + ceil ( row_number ()
over ( partition by grp order by n ) / 2
) grp_2
from grps
)
select count (*) num#, min ( n ), max ( n )
from pairs
group by grp, grp_2
order by grp, grp_2;
NUM# MIN(N) MAX(N)
2 19 20
1 26 26
2 28 29
2 32 33
1 34 34