How to Select from List of Values in Oracle

How can I select from list of values in Oracle

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

Select list as column in Oracle SQL

You can use a system-provided collection type; you select from it using the TABLE operator (even that is no longer necessary since Oracle 12, as I demonstrate below). Note that the column name is COLUMN_NAME - that is the name Oracle chose when they created the system-provided type.

Let's create a small table for testing:

create table people_table (person_id varchar2(10), person_name varchar2(10));

insert into people_table (person_id, person_name) values ('2003', 'Maria');
insert into people_table (person_id, person_name) values ('2005', 'Peter');

Then, here is how you can do what you wanted:

select person_id from people_table
union all
select column_value from sys.odcivarchar2list('1000', '1001', '1002')
;

PERSON_ID
---------
2005
2003
1000
1001
1002

Do a Google search for SYS.ODCIVARCHAR2LIST (and similar SYS.ODCINUMBERLIST) if you are not familiar with it; it's quite useful.

Oracle: Can I select from a list of values?

You can use a database "table of number" type - either create one or use one that handily exists like SYS.KU$_OBJNUMSET:

select sysdate, sysdate - offset.column_value / 24 as offsetSysdate
from TABLE (KU$_OBJNUMSET(1,2,3,4)) offset

Note that above offset is now an alias for the TABLE, and there is a pseudo-column called column_value for table types like this. Also no need for DUAL table.

If you prefer to create your own type:

create type num_tab as table of number;
/

select sysdate, sysdate - offset.column_value / 24 as offsetSysdate
from TABLE(num_tab(1,2,3,4)) offset;

How to select a list but return rows from an Oracle database

That's weird, but the type name don't suggest it's public API anyway :)

You can always roll your own:

create type num_t as table of number;

select column_value from num_t(1,3,5,9);

how to select a list of 10,000 unique ids from dual in oracle SQL

Use a collection (they are not limited to 1000 items like an IN clause is):

SELECT COLUMN_VALUE AS id
FROM   TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       )

SYS.ODCIVARCHAR2LIST and SYS.ODCINUMBERLIST are collection types that are supplied in the SYS schema.

You can join this directly to whichever table you are SELECTing from without needing to use the DUAL table:

SELECT y.*
FROM   your_table y
       INNER JOIN TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       ) i
       ON (y.id = i.COLUMN_VALUE);

If you can get a collection type created then you do not even need the TABLE expression and can use it directly in the WHERE clause using the MEMBER OF operator:

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(200);
/

SELECT *
FROM   yourtable
WHERE  id MEMBER OF stringlist(
                      'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
                    );

You can even pass the values as a bind parameter - see my answer here

List of values as table

Or yet another, similar:

SQL> select column_value
  2  from table(sys.odcivarchar2list('Little', 'Foot', 'Scott', 'Tiger'))
  3  order by column_value;

COLUMN_VALUE
----------------------------------------------------------------------------
Foot
Little
Scott
Tiger

SQL>

Oracle SQL Variable: SELECT with STRING list of values in an IN clause

Enclose the DEFINE value in double-quotes:

define letters = "'a','b','c','d'"

set verify on

select * from dual where dummy in (&letters);

old   1: select * from dual where dummy in (&letters)
new   1: select * from dual where dummy in ('a','b','c','d')

no rows selected

Note that the challenge is really how to DEFINE a multi-word string, as it's the same problem:

SQL> define letters = I love kittens
SQL> define letters
DEFINE LETTERS         = "I" (CHAR)

SQL> define letters = 'a','b','c','d'
SQL> define letters
DEFINE LETTERS         = "a" (CHAR)

Double-quotes fix that:

SQL> define letters = "I love kittens"
SQL> define letters
DEFINE LETTERS         = "I love kittens" (CHAR)

Just for completeness, the other way to set a define variable is from a select statement using the column new_value approach. Historically this was provided for setting page headings dynamically in SQL*Plus reports, but it's handy in scripts. (It's also the only way to set the type to number, not that it makes any difference.)

SQL> col letters new_value letters
SQL> select q'{'a','b','c','d'}' as letters from dual;

LETTERS
---------------
'a','b','c','d'

1 row selected.

SQL> def letters
DEFINE LETTERS         = "'a','b','c','d'" (CHAR)

SQL> select 123.456 as letters from dual;

   LETTERS
----------
   123.456

1 row selected.

SQL> def letters
DEFINE LETTERS         =    123.456 (NUMBER)

---

Related Topics