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

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...
26 August 2009 at 10:01
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');
17 September 2009 at 16:01
Davide Moraschi said…
I like VinceM solution.
Way faster and cost effective!

nice ONE

BTW, I like the Lake of Como also ;-)
11 November 2009 at 13:06
Neelesh Shah said…
http://www.neelesh.com/2009/12/generate-days-in-month.html
5 December 2009 at 05:34
Post a Comment

Popular posts from this blog

Custom Authentication / Authorisation Schemes (Part 1)

I often see posts on the APEX forum asking how to implement custom Authentication / Authorisation schemes within their applications. The following is something I have used in several apps over the last couple of years and provides a great base for securing your application. The code base is largley based on the rather excellent article " Storing Passwords in the Database " found here with a few tweaks and changes. Firstly, just to clarify, Authentication Schemes control access to the application and Authorisation Schemes control access to page items / regions and even pages themselves. Implementing your own Authentication I tend to set up an APP_USERS table that stores Username and encrypted passwords that I Authenticate against when page 101 is submitted. All this will be explained in detail as we go. Create the APP_USERS table CREATE TABLE APP_USERS ( USERNAME VARCHAR2(10), PASSWORD VARCHAR2 (255) ); Create the Application Security Package CREATE OR REPLACE PACKAGE app_sec...
Read more

Custom Error Handling in APEX

If like me you have written several PL/SQL procedures within your APEX app, you will have had to deal with the issue of error handling. As a general rule, I try to hide all Oracle Error Messages from the user and replace these with something more meaningful for example: If a user breaks a unique key by trying to insert the same value twice, the following error message is not uncommon: ORA-00001 Unique constraint violated There is little or no merit rendering this message to a user as those outside the Oracle community, may not be able to deduce what went wrong. We can capture this error and translate it to a user friendly message explaining what went wrong for example: The record you tried to create already exists. Please try again In order to achieve this in APEX, the process is relativly simple. Set up your Custom Error Page 1. Create a new page with the following attributes: Page type: Blank Page Page Number: your choice (for this example I used page 500) Page Alias: ERRPAGE Name:...
Read more

Sum to Parent Nodes in Hierarchy Queries: CONNECT_BY_ROOT

In one of our Applicaitons, the table DEPT contains a self referncing join (Pigs Ear) as it models our organsational department hierarchy. For example: CREATE TABLE DEPT ( DEPT_ID NUMBER NOT NULL ,PARENT_ID NUMBER ,DEPT_NAME VARCHAR2 (100) NOT NULL ,EMPLOYEES NUMBER NOT NULL ,CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID) ,CONSTRAINT DEPT_FK01 FOREIGN KEY (PARENT_ID) REFERENCES DEPT (DEPT_ID) ); INSERT INTO DEPT VALUES (1,NULL,'IT', 100); INSERT INTO DEPT VALUES (2,1,'DEVELOPMENT', 12); INSERT INTO DEPT VALUES (3,1,'SUPPORT', 15); INSERT INTO DEPT VALUES (4,1,'TEST', 25); INSERT INTO DEPT VALUES (5,2,'PL/SQL', 2); INSERT INTO DEPT VALUES (6,2,'Java', 1); INSERT INTO DEPT VALUES (7,2,'SQL', 11); INSERT INTO DEPT VALUES (8,2,'C++', 3); INSERT INTO DEPT VALUES (9,4,'Functional', 3); INSERT INTO DEPT VALUES (10,4,'Non Functional', 5); COMMIT; A quick tree walk using CONNECT BY PRIOR shows you the Parent / Child rel...
Read more