CODEWITHSUNDEEP
plsqlplsqldeveloper
Ali Arda Orhan
Ali Arda Orhan

Reputation: 784

Select from dual as one row?

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

Upvotes: 2

Views: 1532

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

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')));

Upvotes: 3

Related Questions