Tuesday, 25 August 2009

Generate Days in Month (PIPELINED Functions)

This cool example is not one I can take the credit for but since it is used pretty heavily in our organisation, I thought I would share it as it's not only pretty cool buy also demonstrates how useful Oracle Pipelined functions can be.

In essence a Pipeline table function (introduced in 9i) allow you use a PL/SQL function as the source of a query rather than a physical table. This is really useful in our case to generate all the days in a calendar month via PL/SQL and query them back within our application.

To see this in operation, simply create the following objects:

CREATE OR REPLACE TYPE TABLE_OF_DATES IS TABLE OF DATE;

CREATE OR REPLACE FUNCTION GET_DAYS_IN_MONTH
(
pv_start_date_i IN DATE
)
RETURN TABLE_OF_DATES PIPELINED
IS
lv_working_date DATE;
lv_days_in_month NUMBER;
lv_cnt NUMBER;
BEGIN

lv_working_date := TO_DATE(TO_CHAR(pv_start_date_i, 'RRRRMM') || '01', 'RRRRMMDD');
lv_days_in_month := TRUNC(LAST_DAY(lv_working_date)) - TRUNC(lv_working_date);

PIPE ROW(lv_working_date);

FOR lv_cnt IN 1..lv_days_in_month
LOOP
lv_working_date := lv_working_date + 1;
PIPE ROW (lv_working_date);
END LOOP;

RETURN;

END GET_DAYS_IN_MONTH;
/

Once your objects are successfully complied, you can generate all the days in a month by executing the following query:

SELECT column_value the_date
, TO_CHAR(column_value, 'DAY') the_day
FROM TABLE (get_days_in_month(sysdate));

THE_DATE THE_DAY
------------------------
01-AUG-09 SATURDAY
02-AUG-09 SUNDAY
03-AUG-09 MONDAY
04-AUG-09 TUESDAY
05-AUG-09 WEDNESDAY
06-AUG-09 THURSDAY
07-AUG-09 FRIDAY
08-AUG-09 SATURDAY
09-AUG-09 SUNDAY
10-AUG-09 MONDAY
11-AUG-09 TUESDAY
12-AUG-09 WEDNESDAY
13-AUG-09 THURSDAY
14-AUG-09 FRIDAY
15-AUG-09 SATURDAY
16-AUG-09 SUNDAY
17-AUG-09 MONDAY
18-AUG-09 TUESDAY
19-AUG-09 WEDNESDAY
20-AUG-09 THURSDAY
21-AUG-09 FRIDAY
22-AUG-09 SATURDAY
23-AUG-09 SUNDAY
24-AUG-09 MONDAY
25-AUG-09 TUESDAY
26-AUG-09 WEDNESDAY
27-AUG-09 THURSDAY
28-AUG-09 FRIDAY
29-AUG-09 SATURDAY
30-AUG-09 SUNDAY
31-AUG-09 MONDAY


I hope someone finds this example as useful as we do. The credits go to Simon Hunt on this one as it was "borrowed" from one of his apps. Since I offered to buy him a beer he has promised not to make too big a deal out it :)

As always, you can read up on this topic here

4 comments:

VinceM said...

Nice use of pipelined functions. You can also do this in pure SQL using something like the following:

with first_day as (
select trunc(sysdate, 'MONTH') the_date from dual
)
select month_dates.the_date, to_char(month_dates.the_date, 'DAY') the_day
from (
select (first_day.the_date + level - 1) the_date
from first_day
connect by rownum <= (last_day(first_day.the_date) - first_day.the_date + 1)
) month_dates
/

Regards...

Anonymous said...

Does it even need to be that complicated?:

SELECT trunc(sysdate, 'MONTH') + rownum - 1 the_date
FROM all_objects
WHERE rownum <= to_char(last_day(sysdate), 'DD');

Davide Moraschi said...

I like VinceM solution.
Way faster and cost effective!

nice ONE

BTW, I like the Lake of Como also ;-)

Neelesh Shah said...

http://www.neelesh.com/2009/12/generate-days-in-month.html