Is there not an ugly way to use a multi-column multi-line table literal in an Oracle 11g query?
I am trying to use a "literal" multi-column table to query an Oracle 11g connection.
Here's the best I've come up with based on this answer ( this answer offers 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 table in Oracle? Unfortunately, I am unable to create temporary tables.
<rant>
As I expected, PostgreSQL has good, sane syntax for literal tables , but Oracle is a mess.</rant>
EDIT: I've tried the string-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 error with a wrong id.
source to share
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
source to share