Generate a List of Dates

There are many methods to accomplish the same. A few are listed. It probably serves more as a demo to some of the oracle features.

Method 1 – PLSQL Loop

You can also simply build a loop in PL/SQL to generate the list of dates. However for this to work in regular SQL, it will have to be defined using an Oracle ‘Pickler’ or PIPELINED Function.
More info can be found in “Developing and Optimizing Pipelined Functions”.

Method 2 – MODEL Queries

Oracle has the lesser known feature of Model Queries.
Those type of queries allow accessing and modifying oracle SQL output like it is Excel.

The Model allows you to modify the generated data by accessing cells like excel cells to:

• access it for read,
• access existing cells/rows for changing (UPDATE)
• access existing + non existing (UPDATE + INSERT = UPSERT), allowing you to create additional data that didn’t exist before.

SELECT i,stamp
FROM (SELECT 1 AS i,TRUNC(SYSDATE) AS tstamp FROM DUAL)
MODEL
DIMENSION BY (i)
MEASURES (tstamp)
RULES UPSERT SEQUENTIAL ORDER (
tstamp[FOR i FROM 2 TO 10 INCREMENT 1] = tstamp[i=cv(i)-1]+1
);

MEASURES defines the columns you want to access in an array fashion (like you would access a multidimensional array in java or other programming languages.
DIMENSION BY lists out what the indexes are you want to use for accessing the array.
The RULES defines what the edits are that need to take place.

So with above example we can in combination with a loop build a range of dates, starting from one single preexisting date.

Method 3 – Recursive Subquery Factoring

WITH t AS (
  -- Anchor Query (first Row)
  SELECT TRUNC(SYSDATE) AS tstamp FROM DUAL
  -- Recursive Query (subsequent Rows)
  UNION ALL SELECT t.tstamp+1 AS stamp FROM t WHERE t.tstamp+1 <= TRUNC(SYSDATE)+10
)
SELECT * FROM t

Method 4 – Hierarchical Queries using CONNECT BY

SELECT TRUNC(SYSDATE) + (LEVEL-1) AS tstamp FROM DUAL
CONNECT BY LEVEL <= 10

Elegance in Simplicity …

References

• Oracle-Developer.net article on improving performance with pipelined table functions
• Oracle 11gR2 Documentation on
  • Model Queries
  • Hierarchical Queries