How to make dual table with many rows in one col?

Question

I tried to do so, but it returns me in columns not rows

SELECT 386 ,417,420,421,422,423 ... from dual

Possible duplicate of [How can I select from list of values in Oracle](https://stackoverflow.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle) — SmartDumb, Mar 14 '19 at 08:46

Amir Molaei · Accepted Answer · 2019-03-14T06:13:50.477

3

Try this:

select column_value col1 from
table(sys.odcinumberlist((386) ,(417),(420),(421),(422),(423)))

edited Mar 14 '19 at 06:13

answered Mar 14 '19 at 05:53

Amir Molaei

score 1 · Answer 2 · answered Mar 14 '19 at 05:52

1

use union all

SELECT 386 from dual
union all
SELECT 417 from dual
union all
SELECT 420 from dual
union all
SELECT 386 from dual

answered Mar 14 '19 at 05:52

score 0 · Answer 3 · answered Mar 14 '19 at 05:52

0

You can use UNION or UNION ALL as a work around to get the desired output.

SELECT 386 from dual UNION ALL SELECT 417 from dual UNION ALL SELECT 420 from dual ...

answered Mar 14 '19 at 05:52

vamosRafa

3 Answers3