Generate multiple rows

For the first post of the year, one of my New Year's resolutions is to write more posts than last year which shouldn't be too hard, I wanted to collect some different ways of generating multiple records. Sometimes I need, for whatever reason, generate multiple records. These are a few different ways of doing so. This is not really "generating multiple rows", but just a simple way of having multiple lines of output. Just take a big table and limit the number of rows by using "where rownum <= ":

SQL> select 'Happy New Year' msg
  2    from all_objects
  3   where rownum <= 10
  4  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

One of my favorite ways is to use the "Connect by level" trick:

SQL> select 'Happy New Year' msg
  2    from dual
  3  connect by level <= 10
  4  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

Another way is to use the MODEL clause (Oracle 10g). I still find the MODEL clause quite hard to read and understand.

SQL> select msg
  2    from dual
  3    model
  4   dimension by (0 d)
  5   measures (cast ('m' as varchar2(30)) msg)
  6   rules iterate (10)
  7     (msg [iteration_number] = 'Happy New Year')
  8  /

MSG
------------------------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

And lastly the Recursive Subquery Factoring way (Oracle 11g)

SQL> with lots(r)
  2  as
  3  (select 1 r from dual
  4    union all
  5   select r+1 from lots
  6   where r < 10
  7  )
  8  select 'Happy New Year' msg
  9    from lots
 10  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

I know there are more ways of generating multiple rows as output, this is not meant as a complete list of all possibilities. This post was meant to sent a subliminal message to wish you a Happy New Year!