66

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
;
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
rapt
  • 11,810
  • 35
  • 103
  • 145

7 Answers7

95

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))
Kirill Leontev
  • 10,641
  • 7
  • 43
  • 49
  • It works...! and it's what I was looking for. If you can figure out why Tony Andrews' answer (http://stackoverflow.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle/10354083#10354083) is giving me a warning and produces no inserts - please let me know. – rapt Apr 30 '12 at 12:37
  • 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 – Sonic Soul Jun 16 '15 at 17:52
  • 2
    @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. – Kirill Leontev Jun 17 '15 at 18:48
  • I have been looking for that for hours ! – Gary SEBASTIANI Nov 05 '15 at 12:26
  • 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` – Hooplator15 May 27 '16 at 17:04
  • 4
    If you have a list of strings you can use sys.odcivarchar2list('A','B','C') – jornhd Aug 15 '17 at 09:34
  • what if my list contains values of different datatypes? Eg:(1,11,'hello') – Ravi Shankar Oct 17 '18 at 06:27
  • 4
    This is what lead me to discover SYS.ODCIVARCHAR2LIST which is what I needed. Thanks! – Skystrider Mar 14 '19 at 16:10
  • 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. – M. Justin Nov 01 '19 at 22:06
  • 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. – Vasin Yuriy Nov 13 '19 at 11:25
  • 1
    @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](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=88e3358bd748f5c8a8ea4fb120e9395e) 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. – MT0 Jan 25 '22 at 14:11
71

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.

WoMo
  • 7,136
  • 2
  • 29
  • 36
14

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
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
8

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.
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 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 – Alessandro Rossi Apr 29 '12 at 14:00
  • @Justin Cave: Thank you, it was brilliant! – rapt Apr 30 '12 at 12:30
6

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));
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 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? – rapt Apr 27 '12 at 16:40
  • 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. – Tony Andrews Apr 27 '12 at 16:54
  • 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. – rapt Apr 30 '12 at 12:34
  • 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). – Tony Andrews Apr 30 '12 at 12:35
4

Hi it is also possible for Strings with XML-Table

SELECT trim(COLUMN_VALUE) str FROM xmltable(('"'||REPLACE('a1, b2, a2, c1', ',', '","')||'"'));
SkateScout
  • 815
  • 14
  • 24
0

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

de05ARG
  • 13
  • 3