在Oracle 12c上，可以使用CROSS APPLY：

select *
from num_ranges
cross apply(
   select level - 1 + start_number as my_new_number
   from dual
   connect by level <= end_number - start_number + 1
);

在Oracle 11.2及更早版本中，可以使用分层查询：

with
     num_ranges ( id, start_number, end_number ) as (
       select 1, 1,  5 from dual union all
       select 2, 9, 12 from dual
     )
-- End of simulated input data (for testing purposes only, not part of the solution).
-- SQL query begins below this line.
select     id, start_number + level - 1 as nmbr
from       num_ranges
connect by level <= end_number - start_number + 1
       and prior id = id
       and prior sys_guid() is not null
order by   id, nmbr  --  If needed
;

        ID       NMBR
---------- ----------
         1          1
         1          2
         1          3
         1          4
         1          5
         2          9
         2         10
         2         11
         2         12

具体来说，如果不使用相同的id将新行链接到现有行，就会生成大量不必要的行。这就是为什么你需要prior id = id。需要附加条件prior sys_guid() is not null，以便Oracle不会看到它不应该看到的周期（这正是由“prior id = id”引起的）。
在Oracle 12.1或更高版本中，可以使用lateral子句：

select   n.id, l.nmbr
from     num_ranges n,
         lateral ( select start_number + level - 1 as nmbr from dual
                   connect by level <= end_number - start_number + 1 ) l
order by id, nmbr  --  If needed
;

这个应该能用速度也很快。

with cte as (select 0 as c from dual)
, cte4 as (select c from cte union all select c from cte union all select c from cte union all select c from cte)
, cte256 as (select t0.c from cte4 t0, cte4 t1, cte4 t2, cte4 t3)
, nums as (select row_number() over(order by null) as n from cte256 t0, cte256 t1, cte256 t2)
select NR.id, nums.n as NUMBER_
from nums
join NUM_RANGES NR on nums.n between NR.START_NUMBER and NR.END_NUMBER
;

您可以使用分层查询：

WITH recursive_query (id, nmbr, end_number ) AS (
  SELECT id,
         start_number,
         end_number
  FROM   num_ranges
UNION ALL
  SELECT id,
         nmbr + 1,
         end_number
  FROM   recursive_query
  WHERE  nmbr + 1 <= end_number
)
SEARCH DEPTH FIRST BY id SET order_id
SELECT id,
       nmbr
FROM   recursive_query;

其中，对于样本数据：

CREATE TABLE num_ranges ( id, start_number, end_number ) as
  SELECT 1, 1,  5 FROM DUAL UNION ALL
  SELECT 2, 6, 10 FROM DUAL;

输出：
| ID| NMBR|
| - -----|- -----|
| 1| 1|
| 1| 2|
| 1| 3|
| 1| 4|
| 1| 5个|
| 2|六|
| 2|七个|
| 2|八|
| 2|九个|
| 2|十个|
fiddle