List of values as table

Question

I'm looking for a smarter way to have a list of values as a table in Oracle.

What I do nowadays is

select 'value1' as val from dual
 union
select 'value2' from dual

What I'm hoping for is some function/way/magic, that I'll do for example

select 'value1', 'value2' from dual -- + some additional magic

I'm looking for non-PL/SQL way which I think is overkill, but I'm not saying definite no to PL/SQL if that's the only option, but I can look here Create an Oracle function that returns a table for inspiration for PL/SQL. But extra table to have a list seems still easier to maintain than PL/SQL.

The motivation for not using select distict from transactional table is that I want to have a defined list of values, and with that approach, I can miss those I expect there but have no records in the table.

The expected number of elements in the list is several tens of records (like 30).

Answer 1

Here is one option:

select column_value 
from table(sys.dbms_debug_vc2coll('value1', 'value2', 'value3', 'value4'));

Starting Oracle 12.2, you don't need the table function:

select column_value 
from sys.dbms_debug_vc2coll('value1', 'value2', 'value3', 'value4');