Select from dual as one row?

Question

To select many strings from dual as one column. I have tried the solution below and i can live with it, its relatively easy with PL/SQL macros to add "select from dual". I'm wondering is there any other way to achive this?

select 'AAA' as code
          from dual
        union all
        select 'ABQ'
          from dual
        union all
        select 'ACA'
          from dual
        union all   
          from dual

2

There is no PL/SQL in your example – Jan 07 '15 at 10:12

score 3 · Accepted Answer · answered Jan 07 '15 at 10:05

For a single column query there is. You need a database type that is a table of VARCHAR2, and some always exist in oracle including:

SYS.KU$_VCNT
SYS.DBMS_DEBUG_VC2COLL

(and of course you could create your own if you prefer).

Then you can query like this:

select * from table (SYS.KU$_VCNT ('AAA','ABQ','ACA'));

For a query with more than one column, you would need to create a type specifically for that query, which isn't such a useful option. But just for completeness this is how you could do it:

create type my_obj_t as object(n number, d date, c varchar2(100));
create type my_tab_t as table of my_obj_t;

select * from table (my_tab_t(my_obj_t(1,sysdate,'aaa'),
                              my_obj_t(2,date '2014-12-31','bbb'),
                              my_obj_t(3,sysdate+2,'bbb')));

Awesome answer Tony, Thank you. Is there any other embedded table types for numbers, boolean etc? — Ali Arda Orhan, Jan 08 '15 at 11:24
@AliArdaOrhan - I'm sure there must be, but I don't currently know them! — Tony Andrews, Jan 08 '15 at 12:00

Select from dual as one row?

1 Answers1