Oracle Select numbers from an IN clause

Question

I'm looking for the best way to select numbers directly from an in clause.

Basically like:

SELECT * FROM (2,6,1,8);

That doesn't work. I can do it this way:

SELECT Lv FROM (  SELECT Level LV
                  FROM DUAL
                  CONNECT BY Level < 20)
WHERE Lv IN (2,6,1,8);

But that seems to be a bit clunky. Is there a more elegant way?

Answer 1

If you don't explicitly need the IN clause, you could use UNION:

  select 2 from dual
  union
  select 6 from dual
  union
  select 1 from dual
  union
  select 8 from dual

There is a more elegant variant to INSERT multiple rows into a table:

INSERT ALL
   INTO table (col) VALUES ('a')
   INTO table (col) VALUES ('b')
   INTO table (col) VALUES ('c')
SELECT * FROM dual;

But I don't know a way to do that for a SELECT.

Answer 2

It's more elegant if you materialize an auxiliary numbers table:

SELECT num FROM numbers WHERE num IN (2,6,1,8);

And this is also useful when combined with another table.

For instance, I've had a case where I needed to populate large configuration tables with changes from piecewise results:

Big SP or Excel sheet or report identifies missing cost centers in config gives a large set of results which need to be inserted with varying data in some groups.

Paste partial results into a individual comma separated lists:

INSERT INTO {stuff}
SELECT {stuff}, 130 as line_item
FROM numbers
WHERE numbers.num IN ({pasted a section of results})

INSERT INTO {stuff}
SELECT {stuff}, 135 as line_item
FROM numbers
WHERE numbers.num IN ({pasted another section of results})

Answer 3

You can do

select column_value from table(sys.dbms_debug_vc2coll(1,2,3,4,5));

but that actually returns a varchar2. You can create your own TYPE and use that

create type tab_num is table of number;
/
select column_value from table(tab_num(1,2,3,4,5));

It's also worth looking at the MODEL clause. It looks complicated, but it is very good at generating data

SELECT x from dual
MODEL DIMENSION BY (1 AS z) MEASURES (1 x)
  RULES ITERATE (7) (x[ITERATION_NUMBER]=ITERATION_NUMBER+1)