Lets consider that we need to generate 10 rows starting from 101-105 and 201 to 205
1. select case
when level <= 5 then 100 + level
else 195 + level
end id
from dual
connect by level <= 10
order by id
2.
select level id
from dual
where level between 101 and 105
or level between 201 and 205
connect by level <= 205
order by id
3.
select 100 * x.x + y.y id
from (
select level x
from dual
connect by level <= 2
) x
cross join (
select level y
from dual
connect by level <= 5
) y
order by id
4.
select case
when rownum <= 5 then 100 + rownum
else 195 + rownum
end id
from dual
connect by rownum <= 10
order by id
5.
select to_number(column_value) id
from xmltable('101,102,103,104,105,201,202,203,204,205')
order by id
6.
select to_number(column_value) id
from xmltable('101 to 105, 201 to 205')
order by id
7.
select to_number(column_value) id
from xmltable('for $i in (100, 200), $j in 1 to 5 return $i + $j')
order by id
8.
with rowsource(id) as (
select 1 id
from dual
union all
select id + 1 id
from rowsource
where id < 205
)
select id
from rowsource
where id between 101 and 105
or id between 201 and 205
order by id
9.
with rowsource(id) as (
select 101 id
from dual
union all
select case id
when 105 then 201
else id + 1
end id
from rowsource
where id < 205
)
select id
from rowsource
order by id
Courtesy: (PL/SQL Challenge by Steven Feurestien)
http://www.plsqlchallenge.com/pls/apex/f?p=10000:659:5558871037305::NO:659:P659_COMP_EVENT_ID,P659_QUIZ_ID,P659_QUESTION_ID:140896,,7589&cs=1CADD4B82BF7B20DE4520099D400CACB9
