List of values as table

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

Or yet another, similar:

SQL> select column_value
  2  from table(sys.odcivarchar2list('Little', 'Foot', 'Scott', 'Tiger'))
  3  order by column_value;

COLUMN_VALUE
----------------------------------------------------------------------------
Foot
Little
Scott
Tiger

SQL>

Advertisement

Answer