Is there a non-ugly way to use a multi-column, multi-row table literal in an Oracle 11g query?

Question

I'm trying to use a "literal" multi-column table for a join query with Oracle 11g.

Here's the best I've come up with, based on this answer ( this answer suggests a much nicer syntax, but it only works for a single-column table as far as I can tell):

SELECT * from
(
 -- The ugly... it burns...
 select 'APPLE' as n, 'FRUIT' as m from dual
 union all select 'CARROT' as n, 'VEGGIE' as m from dual
 union all select 'PEACH' as n, 'FRUIT' as m from dual
 union all select 'CUCUMBER' as n, 'VEGGIE' as m from dual
 union all select 'ORANGE' as n, 'FRUIT' as m from dual
)

Is there a less ugly way to create a multi-row, multi-column literal table in Oracle? Unfortunately, I cannot create temporary tables.

<rant>As I've come to expect, PostgreSQL has a nice, sane syntax for literal tables, but Oracle is a mess.</rant>

EDIT: I had tried the row-value constructor syntax as suggested by @Mark Chesney but that doesn't work either: select n,m from dual where (n,m) in (...) gives me an Invalid Identifier error.

Milly

For multicolumn tables with #11470134 solution you can join two (or more) select * from table() queries – Spay

@agent5566, what would I use as the join condition, given that each of the tables to be joined contains only one column? – Milly

ROWNUM of each subquery – Spay

Answer 1

For two columns you can use ODCIObjectList:

select objectschema m, objectname n
  from table(sys.ODCIObjectList(
    sys.odciobject('APPLE', 'FRUIT'), 
    sys.odciobject('CARROT', 'VEGGIE'),
    sys.odciobject('PEACH', 'FRUIT'),
    sys.odciobject('CUCUMBER', 'VEGGIE'),
    sys.odciobject('ORANGE', 'FRUIT')));

M          N         
---------- ----------
APPLE      FRUIT     
CARROT     VEGGIE    
PEACH      FRUIT     
CUCUMBER   VEGGIE    
ORANGE     FRUIT 

For more columns you can define your own types:

create type t as object (a varchar2(10), b varchar2(10), c number);
create type tt as table of t;

select * from table( tt (
    t('APPLE', 'FRUIT', 1),
    t('APPLE', 'FRUIT', 1122), 
    t('CARROT', 'VEGGIE', 3),
    t('PEACH', 'FRUIT', 104),
    t('CUCUMBER', 'VEGGIE', 5),
    t('ORANGE', 'FRUIT', 6) ) )

A          B                   C
---------- ---------- ----------
APPLE      FRUIT               1 
APPLE      FRUIT            1122 
CARROT     VEGGIE              3 
PEACH      FRUIT             104 
CUCUMBER   VEGGIE              5 
ORANGE     FRUIT               6

Incinerate

Thanks. This is still ugly (obviously not your fault!), but it's the least ugly solution I've seen so far. I can't CREATE TYPE either on this server but this'll work for the 2-column lookup. – Milly

If you can't define types then there are some predefined which may suit you, not many of them. – Incinerate

Answer 2

For many columns you can join like this

SELECT a.v field1, b.v field2
  FROM (SELECT column_value v, rownum r
          FROM TABLE(sys.odcivarchar2list('abc', 'def', 'ghi'))) a
  JOIN (SELECT column_value v, rownum r
          FROM TABLE(sys.odcivarchar2list('abc', 'def', 'ghi'))) b ON a.r = b.r

Spay

Answer 3

From Oracle 23, you can use the VALUES syntax:

SELECT *
FROM   ( VALUES ('APPLE', 'FRUIT'),
                ('CARROT', 'VEGGIE'),
                ('PEACH', 'FRUIT'),
                ('CUCUMBER', 'VEGGIE'),
                ('ORANGE', 'FRUIT')
       ) t (m, n);

In earlier versions, you can use SELECT ... FROM DUAL UNION ALL ... and wrap it in a sub-query factoring clause (a CTE in other SQL dialects) and name the columns in the signature to simplify the query:

WITH t (m, n) AS (
  select 'APPLE',    'FRUIT'  from dual union all
  select 'CARROT',   'VEGGIE' from dual union all
  select 'PEACH',    'FRUIT'  from dual union all
  select 'CUCUMBER', 'VEGGIE' from dual union all
  select 'ORANGE',   'FRUIT'  from dual
)
SELECT * from t

Which both output:

M	N
APPLE	FRUIT
CARROT	VEGGIE
PEACH	FRUIT
CUCUMBER	VEGGIE
ORANGE	FRUIT

fiddle

Chervonets