List of datetime array using Oracle

The original problematic line :

 select p_from_date + level - 1 dt from
       (
       select to_date('1980-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss') as p_from_date,
       to_date( '1980-11-03 00:00:00','yyyy-mm-dd hh24:mi:ss') as p_to_date
       from dual
       )
     connect by level <= p_to_date - p_from_date

result :

DT
-------------------------
1980-11-01 00:00:00
1980-11-02 00:00:00

the fix :

 select p_from_date + level/24 dt from
       (
       select to_date('1980-11-01 01:00:00','yyyy-mm-dd hh24:mi:ss') as p_from_date,
       to_date( '1980-11-03 09:00:00','yyyy-mm-dd hh24:mi:ss') as p_to_date
       from dual
       )
     connect by level/24 <= p_to_date - p_from_date
 

result :

DT
-------------------------
1980-11-01 02:00:00
1980-11-01 03:00:00
1980-11-01 04:00:00
1980-11-01 05:00:00
1980-11-01 06:00:00
1980-11-01 07:00:00
1980-11-01 08:00:00
1980-11-01 09:00:00
1980-11-01 10:00:00
1980-11-01 11:00:00
1980-11-01 12:00:00
1980-11-01 13:00:00
1980-11-01 14:00:00
1980-11-01 15:00:00
1980-11-01 16:00:00
1980-11-01 17:00:00
1980-11-01 18:00:00
1980-11-01 19:00:00
1980-11-01 20:00:00
1980-11-01 21:00:00
1980-11-01 22:00:00
1980-11-01 23:00:00
1980-11-02 00:00:00
1980-11-02 01:00:00
1980-11-02 02:00:00
1980-11-02 03:00:00
1980-11-02 04:00:00
1980-11-02 05:00:00
1980-11-02 06:00:00
1980-11-02 07:00:00
1980-11-02 08:00:00
1980-11-02 09:00:00
1980-11-02 10:00:00
1980-11-02 11:00:00
1980-11-02 12:00:00
1980-11-02 13:00:00
1980-11-02 14:00:00
1980-11-02 15:00:00
1980-11-02 16:00:00
1980-11-02 17:00:00
1980-11-02 18:00:00
1980-11-02 19:00:00
1980-11-02 20:00:00
1980-11-02 21:00:00
1980-11-02 22:00:00
1980-11-02 23:00:00
1980-11-03 00:00:00
1980-11-03 01:00:00
1980-11-03 02:00:00
1980-11-03 03:00:00
1980-11-03 04:00:00
1980-11-03 05:00:00
1980-11-03 06:00:00
1980-11-03 07:00:00
1980-11-03 08:00:00
1980-11-03 09:00:00

 56 rows selected 

p/s : now, am applying self-restraint not to jump onto our customer..