The 12th SELECT of Xmas - Data generation through the MODEL clause

Okay, I missed twelfth night, but here's my final SELECT of Christmas : Data generation using the MODEL clause.

SQL has this annoying habit of only dealing with data that actually exists. To generate 'missing' data Oracle developers have come up with various tricks for generating lots of rows from nothing. Sometimes these involve the standard Oracle views which the developer assumes (or hopes) will return enough rows to meet their needs. ALL_TAB_COLUMNS tends to be a good one as the standard Oracle views themselves will throw back several thousand rows, never mind your own application tables.

Unfortunately, these are real views, and returning those few thousand rows will actually involve a degree of IO activity which is especially irritating when you really have no interest in the results.

Other people have come up with creative uses of DUAL and hierachial queries.
Personally, I'm not keen on them. To my mind, it isn't the intended use of the CONNECT BY clause and it is not documented that it would work that way. I just don't really TRUST it.

But in 10g, Oracle came up with the MODEL clause. It looks very complicated. It probably is very complicated. And I've avoided even looking at it for a long while.
But it is intended to generate data that doesn't actually exist.

So I've made a very simple example of a data generator using the MODEL clause.

SELECT days
FROM (select 1 days from dual) mnth
MODEL
  DIMENSION BY (days)
  MEASURES (days v)
  RULES UPSERT
  (v[FOR days FROM 1 TO 31 INCREMENT 1] = 1)
order by 1
/

Here is a slightly different technique which includes an UNTIL clause so that you can put in limits other than a simple count.

SELECT TO_CHAR(sysdate+days ,'DD-Mon-YYYY')
FROM (select 1 days from dual) mnth
MODEL
  DIMENSION BY (days)
  MEASURES (days v)
  RULES ITERATE (32)
  UNTIL (TO_CHAR(TRUNC(SYSDATE) +
             (ITERATION_NUMBER+1) ,'MON') !=
                     to_char(sysdate,'MON'))
  (v[ITERATION_NUMBER] = 1)
order by sysdate+days
/