How can I select from list of values in Oracle

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
;

Answer

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

Advertisement

Answer