Does Oracle support the VALUES expression construct

oracle

PostgreSQL supports the SQL spec's VALUES expression in what the spec calls a <table value constructor>

SELECT *
FROM (VALUES
  (1, 'one'),
  (2, 'two'),
  (3, 'three')
) AS t(num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

Trying it on dbfiddle, I can't get it to work in Oracle. I keep getting

ORA-00903: invalid table name

Does Oracle support the VALUES ( expression [, ...] )? If not what is the alternative method of creating a virtual table from row-literals provided in the query?

Best Answer

Does Oracle support the VALUES ( expression [, ...] )? If not what is the alternative method of creating a table from row-literals provided in the query?

The most concise option we've got is with insert all:

insert all 
  into t values(1,'one')
  into t values(2,'two')
  into t values(3,'three')
select * from dual

select * from t;

NUM | WORD 
--: | :----
  1 | one  
  2 | two  
  3 | three

dbfiddle here

Note that the syntax is an extension to the standard and allows inserting into multiple tables with a single statement and conditional inserts, as the example shows:

insert all
when 1 = 1 then
  into t values (num, word)
when num > 0 then
  into t values (-num, 'minus ' || word)
select 0 as num, 'zero' as word from dual union all 
select 1, 'one'   from dual union all
select 2, 'two'   from dual union all 
select 3, 'three' from dual ;

7 rows affected

select * from t;

NUM | WORD       
--: | :----------
  0 | zero       
  1 | one        
  2 | two        
  3 | three      
 -1 | minus one  
 -2 | minus two  
 -3 | minus three

dbfiddle here

Related Question