How get all date from date range in one row - oracle sql

Question

How get all date from date range?

eg. 01/01/2021 to 03/01/2021

need result as one row:

col1        col2        col3
----------------------------------
01/01/2021  02/01/2021  03/01/2021

Answer 1

need result as one row:

You cannot easily do this as Oracle (and SQL, in general) requires a fixed, known set of columns to be able to execute the query.

---

If you want to generate the values as rows then that is easy and you can use a hierarchical query:

SELECT DATE '2021-01-01' + LEVEL - 1 AS value
FROM   DUAL
CONNECT BY DATE '2021-01-01' + LEVEL - 1 <= DATE '2021-01-03';

Or a recursive sub-query factoring clause:

WITH date_ranges ( start_date, end_date ) AS (
  SELECT DATE '2021-01-01', DATE '2021-01-03' FROM DUAL
UNION ALL
  SELECT start_date + 1, end_date FROM date_ranges WHERE start_date + 1 <= end_date
)
SELECT start_date AS value
FROM   date_ranges

The ideal solution to your problem of generating the values as columns would then be to transpose the rows to columns in whatever 3rd-party application you are using to access the database (PHP, C#, Java, Python, etc.) and do NOT try to pivot it in SQL if you have a dynamic number of columns.

Now, if you have a fixed set of columns then it is possible. For example, if you always have 3 dates:

WITH date_range ( start_date ) AS (
  SELECT DATE '2021-01-01' FROM DUAL
)
SELECT start_date AS col1,
       start_date + 1 AS col2,
       start_date + 2 AS col3
FROM   date_range;

If you want to try pivoting to a dynamic number of columns then you want a dynamic pivot and will need to use one of the solutions in the linked answer (but don't, do it in the 3rd-party application instead).

db<>fiddle here