3

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?

8

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

6

Here is Feature Request 13697: Add support for the VALUES() constructor, so it's not currently supported. In that ticket you can see the workaround,

The above query can currently be emulated (quite tediously) in Oracle using:

SELECT 1 FROM dual
UNION ALL
SELECT 2 FROM dual

So in Oracle, you'd use

SELECT 1 AS num, 'one' AS letter FROM dual
UNION ALL SELECT 2, 'two'   FROM dual
UNION ALL SELECT 3, 'three' FROM dual;
0

No Love for CTES?

WITH CTE_VALUES
AS
(
    SELECT 1 AS NUM_VAL, 'one' AS TEXT_VAL FROM DUAL
    UNION ALL
    SELECT 2 AS NUM_VAL, 'two' AS TEXT_VAL FROM DUAL
    UNION ALL
    SELECT 3 AS NUM_VAL, 'three' AS TEXT_VAL FROM DUAL
)
--Then use the CTE as you desire in your query.  Nothing Permanent at all
SELECT NUM_VAL, 
TEXT_VAL 
FROM CTE_VALUES; 
2
  • 1
    It's still the same mechanism to create the table that I found on the issue: SELECT ... UNION ALL (it's just in a CTE but that's not doing anything special) – Evan Carroll Apr 16 '18 at 21:17
  • Agreed @EvanCarroll and I definitely upvoted your answer, but the OP asked about creating a virtual table. Either a temp table or CTE would be the approach for that. – SQLDevDBA Apr 17 '18 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.