CODEWITHSUNDEEP
sqldatabaseoracle-databasecsvoracle12c
Betlista
Betlista

Reputation: 10549

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).

Upvotes: 4

Views: 10113

Answers (5)

Alex Poole
Alex Poole

Reputation: 191265

If you aren't on 12c and can't use json_table (or even if you are/can but don't want to) you could use an XML sequence via xmltable instead:

select *
from xmltable('"value1", "value2", "value3"');

Result Sequence                                                                 
--------------------------------------------------------------------------------
value1
value2
value3

I'd probably use an ODCI collection by default, but might be interesting to compare how all of these options compare with large data volumes - with 30 values you might not be able to see much of a difference.

Upvotes: 2

user330315
user330315

Reputation:

Starting with Oracle 12c you could use JSON_TABLE for that: 

select *
from json_table('["value1", "value2"]', '$[*]' 
                 columns val varchar(20) path '$');

Upvotes: 2

Popeye
Popeye

Reputation: 35900

Also, You can use the connect by query:

SQL> select regexp_substr('VALUE1,VALUE2','[^,]+', 1, level) from dual
  2     connect by level <= regexp_count('VALUE1,VALUE2', '[^,]+');

REGEXP_SUBSTR('VALUE1,VALUE2','[^,]+',1,LEVEL)
----------------------------------------------------
VALUE1
VALUE2

SQL>

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 2

GMB
GMB

Reputation: 222432

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');

Upvotes: 3

Related Questions