I need to get dummy data using SQL – with 10 rows and 10 columns or 100×100 or other arbitrary number.

I was thinking of doing it like this:

    select
        (select * from
                (
                    select     level lvl
                    from       dual
                    connect by level <= 10
                )
            pivot (
                max(lvl)
                for lvl in (1,2,3,4,5,6,7,8,9,10)
            )
        )
    from dual
    connect by level <= 10

but this code throws an error

Is it possible to do this with a single SQL query?

And if not, what is the preferable way to do this?

>Solution :

Your current construction is:

select ( select < 10 columns > ... )
from dual
connect by level <= 10

Oracle is expecting a single column in the subquery, not 10 (or 100).

You don’t need a subquery though, you can do:

select * from
(
    select     level lvl
    from       dual
    connect by level <= 10
)
pivot (
    max(lvl)
    for lvl in (1,2,3,4,5,6,7,8,9,10)
)
connect by level <= 10

fiddle