How to repeat the rows n times

Question

I want to repeat the rows of a table n number of times.

suppose i have a table as below

Tab1

A B C
-----
1 2 3
2 3 4

and n =9

i want to get the result as below

1 2 3
2 3 4
1 2 3
2 3 4
1 2 3
2 3 4
1 2 3
2 3 4
1 2 3

I tried with connect by level and not getting the result as expected.

Answer 1

Like this?

SQL> with test (a, b, c) as
  2    (select 1, 2, 3 from dual union all
  3     select 2, 3, 4 from dual
  4    ),
  5  temp as
  6    (select a, b, c,
  7       row_number() over (order by column_value, a) rn
  8     from test cross join table(cast(multiset(select level from dual
  9                                              connect by level <= 9
 10                                             ) as sys.odcinumberlist))
 11    )
 12  select a, b, c
 13  from temp
 14  where rn <= 9
 15  order by rn  ;

         A          B          C
---------- ---------- ----------
         1          2          3
         2          3          4
         1          2          3
         2          3          4
         1          2          3
         2          3          4
         1          2          3
         2          3          4
         1          2          3

9 rows selected.

SQL>

What does it do?

• lines #1 - 4 represent your sample data
• CTE temp (lines #5 - 11) created all those rows; row_number is used to "rank" them, ordered by column_value (think of it as of a level pseudocolumn, if it is closer to you) and the a column value (why? Your sample output suggests so)
• final query (lines #12 - 15) selects the result for rn <= 9 (as you wanted to get 9 rows)