SQL or PL/SQL techniques to generate rows.

=======================================================
=======================================================
SQL or PL/SQL techniques to generate rows.
=======================================================
=======================================================

1.

SELECT ROWNUM
FROM ( SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3,4,5,6,7,8,9,10) )
WHERE ROWNUM <= &N

2.

SELECT *
FROM ( SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N )

3.

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N;

4.

create or replace type row_table as table of number;


Create or replace function gen_row (num_rows in number)
return row_table
parallel_enable pipelined is
begin
for x in 1..num_rows loop
pipe row (x);
end loop;
return;
end;


select * from table(gen_row(&N));

5.

select to_number(column_value) colval
from xmltable('for $i in 1 to &n return $i');

6.

select integer_value
from dual
where 1=2
model
dimension by ( 0 as key )
measures ( 0 as integer_value )
rules upsert ( integer_value[ for key from 1 to 9 increment 1 ] = cv(key) );

7.

select rownum from all_objects where rownum<=5;


8.

select val
from (select 1 rn from dual)
model
dimension by (rn)
measures (1 val)
rules
iterate (&N)
( val[iteration_number] = iteration_number+1 )
order by val


[note: this is not mine]