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_work...