Let's say I want to define some tabular data on the fly to use in a query, without creating a physical table:

 ------------ ------------ 
|COLUMN_VALUE|COLUMN_VALUE|
 ------------ ------------ 
|1           |a           |
|2           |b           |
|5           |e           |
|4           |d           |
|3           |c           |
 ------------ ------------ 

(note the ordering)

How can I do this as succinctly and simply as possible?

What I could come up with (not so pretty):

with
    x as (
        select
            column_value
        from
            table (sys.odcinumberlist(1, 2, 5, 4, 3))),

    y as (
        select
            column_value
        from
            table (sys.odcivarchar2list('a', 'b', 'e', 'd', 'c')))
select
    x.column_value,
    y.column_value
from
    x
inner join y on x.rownum = y.rownum;

However this does not work, as it errors out with ORA-01747: invalid user.table.column, table.column, or column specification. Apparently the rownum pseudocolumn is not supported when using the built-in table() function.

Using row_number() over (order by column_value asc) in the CTE's seems to wor, but forces ordering of the column values. This is undesirable as the values should appear in the order in which they are defined in the table function.

CodePudding user response：

There is a community suggestion on the Oracle Database Ideas forum to add an ordinality pseudocolumn to scalar collections, but I'd say it's unlikely to be implemented. There is also a suggestion for syntax to allow sets to be declared on the fly using a values clause similar to Postgres Values lists, though I can't find it right now.

Currently your options are:

1. Use multiple select from dual queries with union all to define all required rows explicitly.

2. Define custom object and collection types in order to use table() and object declarative syntax.

I agree neither is ideal.

Regarding generated row numbering, you can use

row_number() over(order by null)

Technically this is not guaranteed to preserve the order, but it seems to do so in practice as of Oracle 21c.

select column_value
     , row_number() over (order by null) as rn
from   table (sys.odcivarchar2list('a', 'b', 'e', 'd', 'c'));

COLUMN_VALUE         RN
------------ ----------
a                     1
b                     2
e                     3
d                     4
c                     5

CodePudding user response：

The solution was to alias the rownum column (also tidied up the query):

select
    x.column_value,
    y.column_value
from
    (select column_value, rownum as rn from table (sys.odcinumberlist(1, 2, 5, 4, 3))) x
        inner join
        (select column_value, rownum as rn from table (sys.odcivarchar2list('a', 'b', 'e', 'd', 'c'))) y on x.rn = y.rn;