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:
x
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
;
Advertisement
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))