How can I select from list of values in Oracle

Question

I am referring to this stackoverflow answer:

How can I select from list of values in SQL Server

How could something similar be done in Oracle?

I've seen the other answers on this page that use UNION and although this method technically works, it's not what I would like to use in my case.

So I would like to stay with syntax that more or less looks like a comma-separated list of values.

UPDATE regarding the create type table answer:

I have a table:

CREATE TABLE BOOK
(   "BOOK_ID" NUMBER(38,0)
)

I use this script but it does not insert any rows to the BOOK table:

create type number_tab is table of number;

INSERT INTO BOOK (
    BOOK_ID
)
SELECT A.NOTEBOOK_ID FROM
    (select column_value AS NOTEBOOK_ID from table (number_tab(1,2,3,4,5,6))) A
;

Script output:

TYPE number_tab compiled
Warning: execution completed with warning

But if I use this script it does insert new rows to the BOOK table:

INSERT INTO BOOK (
    BOOK_ID
)
SELECT A.NOTEBOOK_ID FROM
    (SELECT (LEVEL-1)+1 AS NOTEBOOK_ID FROM DUAL CONNECT BY LEVEL<=6) A
;

Buffo

Answer 1

You don't need to create any stored types, you can evaluate Oracle's built-in collection types.

select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5))

Hap

It works...! and it's what I was looking for. If you can figure out why Tony Andrews' answer (#10354469) is giving me a warning and produces no inserts - please let me know. – Buffo

thanks! worked for me on orcl 11g. i can't help but wonder how did they come up with these names? odcinumberlist vs dbms_debug_vc2coll none of this seems thought out at all – Enigmatic

@SonicSoul because none of them was actually meant for "casual" usage like in my answer above. ODCI stands for oracle data cartridge interface, dbms_debug_vc2_coll is meant to be used with DBMS_DEBUG package. – Hap

I have been looking for that for hours ! – Unwearied

Just to be helpful for those that may not know, you could do ` order by 1 asc` on the ending to sort the result set. select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5)) order by 1 asc – Goosegog

If you have a list of strings you can use sys.odcivarchar2list('A','B','C') – Izmir

what if my list contains values of different datatypes? Eg:(1,11,'hello') – Wash

This is what lead me to discover SYS.ODCIVARCHAR2LIST which is what I needed. Thanks! – Stricken

I like it. Do you know if there is an official Oracle page that explains the table(...) syntax? My search-fu is failing me, since it appears to be really hard to search for the word "table" in the context of a database and find anything useful. – Aquarist

Works only if size of list less then 1000. If not, will be error: too many arguments for function. Same limit as for IN list. – Pilchard

@VasinYuriy That is not a limitation of the SYS.ODCI*LIST types as they can hold 32767 items but it is a limitation on SQL functions that their expression list can have a maximum of 999 arguments. You can construct and populate a list in PL/SQL with more than 1000 items and pass it to the SQL statement and it will work db<>fiddle and you can pass more than 1000 items in the constructor in PL/SQL; you just can't pass more than 999 items in an SQL statement. – Trophoplasm

@M.Justin see the SQL Language Reference for the SELECT statement. Especially search for table_collection_expression on that page. – Accordion

Answer 2

If you are seeking to convert a comma delimited list of values:

select column_value 
from table(sys.dbms_debug_vc2coll('One', 'Two', 'Three', 'Four'));

-- Or

select column_value 
from table(sys.dbms_debug_vc2coll(1,2,3,4));

If you wish to convert a string of comma delimited values then I would recommend Justin Cave's regular expression SQL solution.

Felton

Nice. I didn't even have to grant permissions to that procedure! – Dropwort

work in oracle , and for string. More usefull than the selected solution – Trinitrophenol

Answer 3

Starting from Oracle 12.2, you don't need the TABLE function, you can directly select from the built-in collection.

SQL> select * FROM sys.odcinumberlist(5,2,6,3,78);

COLUMN_VALUE
------------
           5
           2
           6
           3
          78

SQL> select * FROM sys.odcivarchar2list('A','B','C','D');

COLUMN_VALUE
------------
A
B
C
D

Perigon

Answer 4

There are various ways to take a comma-separated list and parse it into multiple rows of data. In SQL

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select '1,2,3,a,b,c,d' str from dual
  3  )
  4   select regexp_substr(str,'[^,]+',1,level) element
  5     from x
  6* connect by level <= length(regexp_replace(str,'[^,]+')) + 1
SQL> /

ELEMENT
----------------------------------------------------
1
2
3
a
b
c
d

7 rows selected.

Or in PL/SQL

SQL> create type str_tbl is table of varchar2(100);
  2  /

Type created.

SQL> create or replace function parse_list( p_list in varchar2 )
  2    return str_tbl
  3    pipelined
  4  is
  5  begin
  6    for x in (select regexp_substr( p_list, '[^,]', 1, level ) element
  7                from dual
  8             connect by level <= length( regexp_replace( p_list, '[^,]+')) + 1)
  9    loop
 10      pipe row( x.element );
 11    end loop
 12    return;
 13  end;
 14
 15  /

Function created.

SQL> select *
  2    from table( parse_list( 'a,b,c,1,2,3,d,e,foo' ));

COLUMN_VALUE
--------------------------------------------------------------------------------
a
b
c
1
2
3
d
e
f

9 rows selected.

Mercurous

In my experience I've always used the same logic of the question. Why the ways you described to achieve that result should be preferred to the use of collections? Especially the one that uses regexp and hierarchical query looks very complex to evaluate! Thanks – Borne

@Justin Cave: Thank you, it was brilliant! – Buffo

Answer 5

You can do this:

create type number_tab is table of number;

select * from table (number_tab(1,2,3,4,5,6));

The column is given the name COLUMN_VALUE by Oracle, so this works too:

select column_value from table (number_tab(1,2,3,4,5,6));

Allergy

It looks like it should work.. but I am getting: TYPE number_tab compiled. Warning: execution completed with warning, and where I use this code (inside an encapsulating INSERT), the encapsulating INSERT does not work. E.g. I do INSERT INTO BOOK ( BOOK_ID ) SELECT A.NOTEBOOK_ID FROM (select column_value AS NOTEBOOK_ID from table (number_tab(1,2,3,4,5,6))) A ; - no row was inserted... what am I doing wrong? – Buffo

I don't know what you are doing wrong, or what is wrong: I am on 11G also and it works from me. What is the warning you are getting on creating the type? I don't get any warning. – Allergy

It's basically what I wanted...! ...and you were the first one to come up with this direction. But I am not sure why the create type number_tab is table of number; gives me a warning... If you can figure it out please let me know. Thank you. – Buffo

Run the create type command in SQL Plus, and then type show errors after you get the warning to see the details. In other tools like Toad, SQL Developer you can probably find out using the "Errors" tab on the type object (or something like that). – Allergy

Answer 6

Hi it is also possible for Strings with XML-Table

SELECT trim(COLUMN_VALUE) str FROM xmltable(('"'||REPLACE('a1, b2, a2, c1', ',', '","')||'"'));

Uproot

Answer 7

[Deprecated] - Just to add for the op, The issue with your second code it seems to be that you haven't defined there your "number_tab" type.

AS :

CREATE type number_tab is table of number;

SELECT a.notebook_id FROM (
SELECT column_value AS notebook_id FROM table (number_tab(1,2,3,4,5,6) )  ) a; 


INSERT INTO BOOK (  BOOK_ID )
SELECT a.notebook_id FROM (
SELECT column_value AS notebook_id FROM table (number_tab(1,2,3,4,5,6) )  ) a;

DROP type number_tab ; 

Sorry, I couldn't reproduce your error, could you send us the version of oracle used and the same code used for the procedure in the first instance?, that could help. All the best.

Holp

Answer 8

Oracle Database 23ai supports table values constructor. So from this release you can do:

select * from (
  values ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ) 
) t ( id );

        ID
----------
         1
         2
         3
         4
         5
         6

Kliman