68

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
;
Improve this question

8 Answers 8

Reset to default
102

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))
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

It works...! and it's what I was looking for. If you can figure out why Tony Andrews' answer (stackoverflow.com/questions/10353969/…) is giving me a warning and produces no inserts - please let me know.
@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.
If you have a list of strings you can use sys.odcivarchar2list('A','B','C')
This is what lead me to discover SYS.ODCIVARCHAR2LIST which is what I needed. Thanks!
@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.
|
73

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.

Improve this answer

2 Comments

Nice. I didn't even have to grant permissions to that procedure!
work in oracle , and for string. More usefull than the selected solution
16

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
Improve this answer

Comments

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.
Improve this answer

2 Comments

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
@Justin Cave: Thank you, it was brilliant!
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));
Improve this answer

4 Comments

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

Hi it is also possible for Strings with XML-Table

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

Comments

1

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
Improve this answer

Comments

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.