CODEWITHSUNDEEP
oracle-database
ceving
ceving

Reputation: 23814

How to write a table literal in Oracle?

A table with one column and one row can be created with:

select 'create' as col from dual;

This can be used to build table joins:

with
  a as (select 'create' as ac from dual),
  b as (select 'delete' as bc from dual)
select * from a left outer join b on (ac = bc);

Now I would like to have two rows. I did it in this way:

select 'create' as col from dual
union
select 'delete' as col from dual;

But is there a more compact notation for this? I tried

select ('create', 'delete') as col from dual;

but it does not work.

Upvotes: 11

Views: 5440

Answers (2)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

You can use collection type and TABLE operator, for example (works in Oracle 10g):

SQL> SELECT column_value FROM TABLE(SYS.ODCIVARCHAR2LIST('abc', 'def', 'ghi'));

COLUMN_VALUE
--------------------------------------------------------------------------------
abc
def
ghi

Upvotes: 15

Glenn
Glenn

Reputation: 9150

A couple of ways to generate rows. You could use rownum against a table with a larger number of rows:

SELECT roWnum AS a
  FROM user_objects
  WHERE rownum <= 3

You could use a hierarchical query:

SELECT level AS a
  FROM dual
  CONNECT BY LEVEL <= 3

EDIT: change int sequence to alpha sequence:

SELECT CHR( ASCII('a') + level - 1 )
  FROM dual
  CONNECT BY LEVEL <= 3

Upvotes: 2

Related Questions