How can I select from list of values in Oracle
oracle, oracle11g, select, sql
Solution
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))
Problem
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 ; ```