Is it possible to have multiple rows with SELECT FROM DUAL?

Question

Can I have something like

SELECT (1, 2, 4542, 342) FROM DUAL;

and get it like this?

  |  1    |
  |  2    |
  |  4542 |
  |  342  |

Answer 1

Instead of DUAL, combine the TABLE operator with a pre-built collection to return multiple rows. This solution has a small syntax, avoids type conversions, and avoids potentially slow recursive queries. But it's good to understand the other solutions as well, they are all useful in different contexts.

select * from table(sys.odcinumberlist(1, 2, 4542, 342));

Answer 2

Well if (1, 2, 4542, 342) were a string you could do this:

with cte as (
    SELECT '1, 2, 4542, 342' as str 
    FROM DUAL
)
select regexp_substr(str,'[^,]+',1,level) 
from cte
connect by level <= regexp_count(str, ',')+1
/

Answer 3

No. dual has just one row, but you can use union all:

SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 4542 FROM DUAL UNION ALL
SELECT 342 FROM DUAL;

This is just one way to generate a table "on-the-fly" in Oracle.