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

This seems like a duplicate - https://stackoverflow.com/questions/8785459/anonymous-table-or-varray-type-in-oracle I tried to google it (before I asked) I found it only based on answers below... — Betlista, Mar 25 '20 at 09:59

score 3 · Answer 1 · answered Mar 25 '20 at 09:40

3

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

answered Mar 25 '20 at 09:40

GMB

216,147
25
84
135

1

Thanks, worked for me. PL/SQL I do not need to maintain is better than what I had in mind. – Betlista Mar 25 '20 at 09:49

score 2 · Accepted Answer · answered Mar 25 '20 at 09:41

2

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>

answered Mar 25 '20 at 09:41

Littlefoot

131,892
15
35
57

1

Thanks, worked for me. PL/SQL I do not need to maintain is better than what I had in mind. – Betlista Mar 25 '20 at 09:47
I had to pick a "winner", so I picked this one. [Here](https://developpaper.com/examples-of-anonymous-table-varray-types-in-sql-oracle/) the author says `odcivarchar2list is not good, because it chews a lot of memory` which is ok for my usage (short list of values). – Betlista Mar 25 '20 at 10:52
1

In my case I asked about strings (varchar in Oracle DB), there is also `OdciNumberList` for numbers... – Betlista Mar 25 '20 at 10:59
Yes, there is - but, as you said, you asked about strings. Those built-in types are sometimes handy (like in your case) as you don't have to create your own ones. – Littlefoot Mar 25 '20 at 11:23

score 2 · Answer 3 · answered Mar 25 '20 at 10:04

2

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

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

answered Mar 25 '20 at 10:04

This would be my preferred option if it's not a list of strings, but list of objects - `select * from json_table('[ {foo: 1, bar: "value1"}, {foo: 2, bar: "value2"} ]', '$[*]' columns foo number path '$.foo', bar varchar2(20) path '$.bar');` – Betlista Mar 25 '20 at 10:45

Alex Poole · Answer 4 · 2020-03-25T10:33:14.683

2

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.

edited Mar 25 '20 at 10:33

answered Mar 25 '20 at 10:15

Alex Poole

183,384
11
179
318

Thanks, with hundreds of records I'd use extra table, this might help someone in future... I'm on Oracle 12c (which I didn't mention too). – Betlista Mar 25 '20 at 10:29
Well, this works on later versions too... I didn't phrase that very well *8-) – Alex Poole Mar 25 '20 at 10:31

score 1 · Answer 5 · answered Mar 25 '20 at 09:41

1

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>

answered Mar 25 '20 at 09:41

Popeye

35,427
4
10
31

Thanks, that worked too, but at the moment I like the other two options (using PL/SQL) more as for me it's easier to read. I know I mentioned non-PL/SQL way in question, I hope you understand... – Betlista Mar 25 '20 at 09:51

List of values as table

5 Answers5