How can I select from list of values in Oracle
SqlOracleSelectOracle11gSql Problem Overview
I am referring to this stackoverflow answer:
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
;
Sql Solutions
Solution 1 - Sql
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))
Solution 2 - Sql
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.
Solution 3 - Sql
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.
Solution 4 - Sql
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
Solution 5 - Sql
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));
Solution 6 - Sql
Hi it is also possible for Strings with XML-Table
SELECT trim(COLUMN_VALUE) str FROM xmltable(('"'||REPLACE('a1, b2, a2, c1', ',', '","')||'"'));
Solution 7 - Sql
[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.