create function return date

- get previous date ( just date -1)
- if previous date is sat/sun, call function again with the date
- if prevous date is in the list of dates on the holiday table, call function again with the date.

As with any coding with recursive calls, be careful.

Hi Tom,
A very good answer. But when I executed the qry on Oracle8i
(Oracle8i 8.1.7.4.0) it gave me the below error.
ORA-01436: CONNECT BY loop in user data

Is this because of version. Please guide.

Asheesh,

On 8.1.7.4, the CONNECT BY trick needs another clause (shall I say hack?) to make it work. Try following

select dt,
       (select max(dt-R)
          from (select rownum R from dual connect by level <= 5 and prior dbms_random.random is not null)
         where to_char((dt-R),'DY') not in ('SAT','SUN')
           and (dt-R) not in ( trunc(dt,'y'),
                               next_day(trunc(dt,'y')-1,'MON')+14,
                               next_day(add_months(trunc(dt,'y'),1)-1,'MON')+14,
                               next_day(last_day(add_months(trunc(dt,'y'),4))-7,'MON'),
                               add_months(trunc(dt,'y'),6)+3,
                               next_day(add_months(trunc(dt,'y'),8)-1,'MON'),
                               next_day(add_months(trunc(dt,'y'),9)-1,'MON')+7,
                               next_day(add_months(trunc(dt,'y'),10)-1,'THU')+21,
                               next_day(add_months(trunc(dt,'y'),10)-1,'THU')+22,
                               add_months(trunc(dt,'y'),11)+24 )
        ) prior_bd
  from (select to_date('01-jan-2008','dd-mon-yyyy')+level-1 dt from dual connect by level 
<= 366 and prior dbms_random.random is not null)

Thanks to Frank Zou and AskTom where I learned this.

Is it just me, or does that query "lie" a little?

Try plugging in Jan 1 2006:

SQL > l
1 select dt,
2 (select max(dt-R)
3 from (select rownum R from dual connect by level <= 5)
4 where to_char((dt-R),'DY') not in ('SAT','SUN')
5 and (dt-R) not in ( trunc(dt,'y'),
6 next_day(trunc(dt,'y')-1,'MON')+14,
7 next_day(add_months(trunc(dt,'y'),1)-1,'MON')+14,
8 next_day(last_day(add_months(trunc(dt,'y'),4))-7,'MON'),
9 add_months(trunc(dt,'y'),6)+3,
10 next_day(add_months(trunc(dt,'y'),8)-1,'MON'),
11 next_day(add_months(trunc(dt,'y'),9)-1,'MON')+7,
12 next_day(add_months(trunc(dt,'y'),10)-1,'THU')+21,
13 next_day(add_months(trunc(dt,'y'),10)-1,'THU')+22,
14 add_months(trunc(dt,'y'),11)+24 )
15 ) prior_bd
16* from (select to_date('01-jan-2006','dd-mon-yyyy')+level-1 dt from dual connect by level <= 366)
SQL > /

DT PRIOR_BD
----------- -----------
01-JAN-2006 30-DEC-2005
02-JAN-2006 30-DEC-2005
03-JAN-2006 02-JAN-2006 <-- Jan 2 is a holiday, Jan 1 falls on SUN!!
04-JAN-2006 03-JAN-2006
05-JAN-2006 04-JAN-2006

If you want/need the full formulas for dates, I wrote a calendar in Excel a while back (Canadian holidays only, sorry):
http://dot.ditto.googlepages.com/Calendar.xls
Feel free to grab a copy and check the formulas for the dates (most involve some MOD logic to shift those that land on weekends to weekdays).

However, what I've learned (thanks to 9/11 ) is you can't calculate all the business days (there's always 1 exception) ... you almost need to use a table to either:

1) store the business dates
or
2) store the holidays

.. then work from there ...
(we use the formulas I use in that spreadsheet to populate our table ... )

but for those who want to in-line it, go ahead and check the formulas out ... (just go to the Calculations tab and select "Tools->Protection->Unprotect" .. there's no password, it's just to prevent accidental updats)

For example, the following "properly" calculates new years in Tom's query:

trunc(dt,'y')+greatest(0,2-mod(to_number(to_char(trunc(dt,'y'), 'd')),7)),

(it just needs an adjustment ..)
(there's probably an easier way ... I haven't played with these forumlas lately .. heh)

Some of the others might need tweaking as well .. (ie July 4?)

KISS - Keep it simple, stupid.

Sorry if I caused any duress Tom!! Not my intent, just trying to help (as were you - apparently) ..

and I do totally 100% agree with you ...

Getting specs from people is hard, as a developer in my job, I know how tough it can be, and strange some of the "specs" we get!!

Anyway, if it helps, your original query is useful regardless, as it shows the basic premise, and sometimes that is HUGE. (if you can get the basic premise of something, you can easily modify it as needed based on YOUR specific requirements) ;)

So please .. don't hide any rocks, we need ya!! :)
I have learned so much from you and this site over the last 5+ years since I found you here it isn't funny! I'd hate to think where I'd be today if it wasn't for you! (probably convincing people that it's better to use explicit cursors, avoid bind variables, and avoid UNIONS at all costs!! Eeek!! )

It is hard when you aren't paid to do somebody else's work though .. ;)

believe me, some of us understand that!

Dear Tom
I have a similar situation at the project that i am working in. There is a function which calculates the previous day.
I thought of rewriting that function code in SQL in an attempt to create better performance.

To my disappointment i found out that performance of the function procedural code was comparable with straight SQL. I tried rerunning the test harness and found out that the results are not consistent --> sometimes straight SQL wins and sometimes it doesnt. The thing i noticed was consistent is that the number of recursive calls for procedural code is always greater than that of straight sql
STAT...recursive calls

Perhaps i am interpreting something incorrectly. Could you please explain as to why this could be happening.

I shall phrase the expected functionality

1. User passes a date to a function(get_previous_day). This function then returns the previous working day.
2. A working day is defined as one which is not on SAT or SUN
or not among the dates stored in the HOLIDAY table.


DROP TABLE HOLIDAY PURGE;

DROP FUNCTION get_previous_day ;

CREATE TABLE HOLIDAY
(
HOLIDAY_DATE DATE NOT NULL,
CALENDAR_CODE CHAR(5) NOT NULL,
USER_ID VARCHAR2(20) NOT NULL,
DATETIME_STAMP DATE NOT NULL
);

Insert into HOLIDAY
SELECT TO_DATE('04/10/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/03/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/10/2022','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/24/2022','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/26/2022','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/02/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/16/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/20/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/29/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/04/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/09/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/23/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2023','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/15/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/19/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/27/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/02/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/14/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/28/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2024','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/20/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/17/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/26/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/01/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/13/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/27/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2025','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2026','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/19/2026','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/16/2026','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/25/2026','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/07/2026','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/12/2026','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/26/2026','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2026','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2027','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/18/2027','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/15/2027','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/31/2027','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/05/2027','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/06/2027','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/11/2027','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/25/2027','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/17/2028','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/21/2028','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/29/2028','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2028','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/04/2028','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/09/2028','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/23/2028','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2028','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/15/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/19/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/28/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/03/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/08/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/22/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2029','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/21/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/18/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/27/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/02/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/14/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/28/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2030','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/20/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/17/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/26/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/01/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/13/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/27/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2031','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2032','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/19/2032','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/16/2032','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/31/2032','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/05/2032','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/06/2032','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/11/2032','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/25/2032','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/17/2033','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/21/2033','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/30/2033','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2033','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/05/2033','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/10/2033','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/24/2033','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/26/2033','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/02/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/16/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/20/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/29/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/04/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/09/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/23/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2034','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/15/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/19/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/28/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/03/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/08/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/22/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2035','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/21/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/18/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/26/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/01/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/13/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/27/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2036','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2037','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/19/2037','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/16/2037','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/25/2037','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/07/2037','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/12/2037','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/26/2037','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2037','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2038','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/18/2038','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/15/2038','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/31/2038','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/05/2038','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/06/2038','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/11/2038','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/25/2038','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/21/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/18/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/26/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/01/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/13/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/27/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2008','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/19/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/16/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/25/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/07/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/12/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/26/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2009','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/18/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/15/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/31/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/05/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/06/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/11/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/25/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/17/2011','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/21/2011','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/30/2011','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2011','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/05/2011','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/10/2011','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/24/2011','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/26/2011','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/02/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/16/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/20/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/28/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/03/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/08/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/22/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2012','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/21/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/18/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/27/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/02/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/14/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/28/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2013','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/20/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/17/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/26/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/01/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/13/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/27/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2014','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2015','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/19/2015','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/16/2015','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/25/2015','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/07/2015','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/12/2015','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/26/2015','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2015','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/18/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/15/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/30/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/05/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/10/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/24/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/26/2016','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/02/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/16/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/20/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/29/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/04/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/09/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/23/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2017','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/15/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/19/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/28/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/03/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/08/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/22/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2018','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/21/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/18/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/27/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/02/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/14/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/28/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2019','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2020','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/20/2020','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/17/2020','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/25/2020','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/07/2020','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/12/2020','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/26/2020','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/25/2020','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2021','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/18/2021','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/15/2021','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/31/2021','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/05/2021','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/06/2021','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('10/11/2021','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('11/25/2021','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('01/17/2022','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('02/21/2022','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('05/30/2022','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('07/04/2022','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('09/05/2022','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL UNION ALL
SELECT TO_DATE('12/24/2010','MM/DD/YYYY'),'NYB','SYSTEM',SYSDATE FROM DUAL;

COMMIT;


EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'HOLIDAY',CASCADE=>TRUE);

CREATE FUNCTION get_previous_day (date_in IN DATE)
RETURN DATE
/*
Function to return the last business day of the month for a given date
*/
IS
prev_day DATE;
is_weekend NUMBER := 0;
is_business_day NUMBER := 0;
is_holiday NUMBER := 0;
BEGIN
prev_day := date_in - 1;

WHILE (is_business_day = 0)
LOOP
-- Is Weekend?
IF TO_CHAR (prev_day, 'D') IN ('7', '1')
THEN
is_weekend := 1;
END IF;

-- Is Holiday?
BEGIN
SELECT 1
INTO is_holiday
FROM holiday
WHERE holiday_date = prev_day;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
is_holiday := 0;
END;

IF (is_weekend = 1 OR is_holiday = 1)
THEN
prev_day := prev_day - 1;
is_weekend := 0;
is_holiday := 0;
ELSE
is_business_day := 1;
END IF;
END LOOP;

RETURN prev_day;
-- DBMS_OUTPUT.put_line (month_end);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN prev_day;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error: ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END;

/*Start the test harness*/
exec RUNSTATS_PKG.RS_START;

-- Gets the previous working day for 10-Mar-3011 using the procedural code
select get_previ

ops$tkyte%ORA11GR2> alter table holiday add constraint holiday_pk primary key(holiday_date);

Table altered.

STAT...calls to get snapshot s 17 5 -12
LATCH.cache buffers chains 243 229 -14
STAT...buffer is not pinned co 19 0 -19
STAT...no work - consistent re 25 5 -20
STAT...DB time 23 2 -21
STAT...consistent gets from ca 36 15 -21
STAT...recursive calls 27 4 -23
STAT...session logical reads 77 49 -28
STAT...consistent gets from ca 51 22 -29
STAT...consistent gets 51 22 -29

Run1 Run2 Diff Pct
658 1,494 836 44.04%
I am using Oracle 11g release 11.1.0.6.0

Regards
George

Thanks for your reply.

Now, tell us - how do you call this function (give us the typical use case)?
This function is called from multiple packages, where there is a need to calculate the previous working day for a given date(95% of time the passed in value is the current_date).

Is it called from SQL somewhere?
<ib>This function is called plenty of times within a single day in multiple packages(This is typically used in multiple batch jobs. Some cases there is a call to this function within the .NET code->OLTP screens)

How often do you call this function?
This is called from PL/SQL packages. Sometimes in .NET code as straight SQL==>SELECT get_previous_day(<passed_in_date>)FROM DUAL using ODP.NET

Timing a SINGLE INVOCATION is not extremely useful.
Ok. I tried this multiple times and the results seem to flip flop, which is not something that i expected to see.
My concern is that if i use this to benchmark, i would be with poor prognosis to build a strong case for making a change.

Just to tell you, i follow your advice in writing straight forward SQL here. In fact it has helped me move something faster(batch job taking 1 hr to just under 15 min) using SQL and subquery caching.

where is the :
ops$tkyte%ORA11GR2> alter table holiday add constraint holiday_pk primary key(holiday_date);

Table altered.
There is a unique index on the holiday_date and calendar code but there isn't a primary key. That certainly is something i would be adding.

WHEN OTHERS CODE--> Is something that caught my eye too,i would be getting rid of that inherited code.

But please share your thoughts on something else here..
Is there any place where you have noticed that STRAIGHT SQL proved to be slower(less wall clock, or more Latches) than PL/SQL(keeping all other conditions the same)?

Regards
George

ops$tkyte%ORA11GR2> create table t as select created from all_objects;

Table created.

ops$tkyte%ORA11GR2> begin
  2      for x in (select * from t) loop null; end loop;
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_result    date;
  3  begin
  4      for x in (select created from t)
  5      loop
  6          l_result := get_previous_day( x.created );
  7      end loop;
  8  
  9      for x in (select get_previous_day(created) from t)
 10      loop
 11          null;
 12      end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_date      date;
  3      l_result    date;
  4  begin
  5      for x in (select created from t)
  6      loop
  7          with data as (select level l from dual connect by level <= 8)
  8          select max(x.created-l) into l_result
  9            from data
 10           where to_char( x.created-l,'DY' ) not in ('SAT', 'SUN' )
 11             and not exists (select null from holiday where holiday_date = (x.created-l));
 12      end loop;
 13  
 14      for x in (select  ( with data as (select level l from dual connect by level <= 8)
 15                         select max(created-l)
 16                           from data
 17                          where to_char( created-l,'DY' ) not in ('SAT', 'SUN' )
 18                            and not exists (select null from holiday where holiday_date = (created-l)) ) result
 19                  from t )
 20      loop
 21          null;
 22      end loop;
 23  
 24  end;
 25  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_stop(10000);
Run1 ran in 1801 cpu hsecs
Run2 ran in 863 cpu hsecs
run 1 ran in 208.69% of the time

Name                                  Run1        Run2        Diff
LATCH.row cache objects             14,150         781     -13,369
STAT...session cursor cache hi      25,404       6,814     -18,590
STAT...table scan rows gotten    1,012,528   1,048,077      35,549
STAT...sorts (rows)                  1,472      73,229      71,757
STAT...sorts (memory)                  149      73,217      73,068
STAT...workarea executions - o          57      73,189      73,132
STAT...buffer is not pinned co     288,622     361,826      73,204
STAT...session logical reads       290,599     369,279      78,680
STAT...consistent gets             290,515     369,230      78,715
STAT...consistent gets from ca     290,515     369,230      78,715
STAT...index fetch by key          287,397     366,871      79,474
STAT...session uga memory          -65,512      65,512     131,024
STAT...physical read bytes          16,384     172,032     155,648
STAT...cell physical IO interc      16,384     172,032     155,648
STAT...physical read total byt      16,384     172,032     155,648
STAT...session pga memory         -131,072      65,536     196,608
STAT...consistent gets - exami     287,798      73,379    -214,419
STAT...opened cursors cumulati     287,565      72,396    -215,169
STAT...execute count               287,595      72,396    -215,199
STAT...calls to get snapshot s     287,596      72,392    -215,204
LATCH.shared pool                  301,522      73,073    -228,449
STAT...consistent gets from ca       2,708     295,831     293,123
STAT...no work - consistent re       2,683     295,813     293,130
LATCH.cache buffers chains         317,593     665,337     347,744
STAT...recursive calls             577,609      74,317    -503,292

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
639,394     740,567     101,173     86.34%

PL/SQL procedure successfully completed.

That was just too cool!

Now i am getting the comparison results on the latch% consistent.
Straight SQL has fewer latches than the function call.
Only for the first time run did i notice that the wall clock was slower for the second method.
In all the other runs the wall clock came out faster for straight sql.

I am guessing that the first part of the code of your code is a warm up routine? I didnt have that in my test case.

/*Start the test harness*/
exec RUNSTATS_PKG.RS_START;

-- Gets the previous working day for 10-Mar-3011 using the procedural code
declare
l_result date;
begin
for i in 1..10000
loop
select get_previous_day(to_date('3/10/3011','MM/DD/YYYY'))
into l_result
from dual;
end loop;
end;


exec RUNSTATS_PKG.RS_MIDDLE;

-- Gets the previous working day for 10-Mar-3011 using straight SQL
declare
l_result2 date;
begin
for i in 1..10000
loop
with data as(select level lvl from dual connect by level<=8)
select max(to_date('3/10/3011','MM/DD/YYYY') -lvl)
into l_result2
from data
where to_char(to_date('3/10/3011','MM/DD/YYYY') -lvl,'DY') NOT IN('SAT','SUN')
and not exists(select null from holiday where holiday_date=(to_date('3/10/3011','MM/DD/YYYY')-lvl));
end loop;
end;


--Stop the test harness
exec RUNSTATS_PKG.RS_STOP(10000);

first time run
Run1 ran in 518 cpu hsecs
Run2 ran in 2148 cpu hsecs
run 1 ran in 24.12% of the time
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
181,071 163,094 -17,977 111.02%
second run
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
100,959 71,043 -29,916 142.11%
Run1 ran in 420 cpu hsecs
Run2 ran in 253 cpu hsecs
third run
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
114,938 60,853 -54,085 188.88%
Run1 ran in 419 cpu hsecs
Run2 ran in 253 cpu hsecs

Also I have implemented the scalar subquery caching in the packages.That was one of the quick wins i had when i rewrote the package.
Regarding the scalar subquery caching. If the values being passed are unique then scalar subquery wouldnt have much effect on the current SQL. Is that a true statement?
If yes then would the cached results be of use for another session to make use if they execute the same query?


Thanks and Regards
George

One could argue that Oracle's documentation teaches it. In alot of
examples of exception handling they have WHEN OTHERS accompanied by
"dbms_output" calls then RAISE. Right in the section teaching
exception handling it is found,
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25519/errors.htm

Look at the overview.

EXCEPTION
WHEN ex_name_1 THEN statements_1 -- Exception handler
WHEN ex_name_2 OR ex_name_3 THEN statements_2 -- Exception handler
WHEN OTHERS THEN statements_3 -- Exception handler
END;

Sort of says pretty clearly, "Hey programmers, make sure to handle
WHEN OTHERS". You wouldn't have documented it that way! You have
argued vehemently that you wish Oracle never even had WHEN OTHERS.
Their overview puts it out there as an overall guidance.

Example 11-3 shows a "WHEN OTHERS" accompanied by a dbms_output then a
RAISE. Nothing about anything, just that its being handled. That
programming style would get a Tom Kyte beating if posted here.

Look at example 11.15 and 11.16. This one is describing exceptions in
the declaration sections. Well, those are really nasty buggers, but
nothing about this example explains that, instead it shows it being
handled with a dbms_output call. Again, what good does that do in
productionized code? Its as though this PLSQL code is expected to be
run by a user directly, and that user has serveroutput on.

Look at example 11.22. There we see SQLCode and substr(SQLERRM,1,64).
Hm, are they saying that is good practice? Yeah, I know they are
writing to a table, but then they aren't using autonomous, or
explaining what you might say about only top level should do such a
thing. And then, there is a dbms_output call. What good does the when
others clause do in production? I think if that was posted onto your
site, you would loudly question the person posting it.

Read the wording of the section, "Advantages of Exception Handlers".
Third paragraph in that section starts with a big suspect. "With
exception handlers, you need not know every possible error or
everywhere that it might occur. You need only include an
exception-handling part in each block where errors might occur." Go
to the example there and what do we find? A WHEN OTHERS with a
dbms_output then a RAISE. Oracle docs, in black and white, basically
say, you need to handle all exceptions. Pretty poor coding practice if
posted on your site. You'd ask why is it there, but its in the docs
for exception handling.

I forgot to finish with, I'm not sure why code that is posted has WHEN
OTHERS without a RAISE, but, the way Oracle teaches in its docs
seemingly isn't helping the cause. I do know I've seen code in the
docs that have this shown. Its not prevalent, but it is there. I
will try to make sure to save those URLS.

CREATE or replace FUNCTION get_previous_day (date_in IN DATE)
RETURN DATE
/*
Function to return the last business day of the month for a given date
*/
IS
prev_day DATE;
is_weekend NUMBER := 0;
is_business_day NUMBER := 0;
is_holiday NUMBER := 0;
BEGIN
prev_day := date_in - 1;

WHILE (is_business_day = 0)
LOOP
-- Is Weekend?
IF TO_CHAR (prev_day, 'D') IN ('7', '1')
THEN
is_weekend := 1;
END IF;


IF (is_weekend = 1)
THEN
prev_day := prev_day - 1;
is_weekend := 0;
is_holiday := 0;
ELSE
is_business_day := 1;
END IF;
END LOOP;

RETURN prev_day;
-- DBMS_OUTPUT.put_line (month_end);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN prev_day;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error: ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END;

/*Start the test harness*/
exec RUNSTATS_PKG.RS_START;

-- Gets the previous working day for 10-Mar-3011 using the procedural code
select get_previous_day(to_date('3/10/3011','MM/DD/YYYY')) from dual;

When I tried this for only one date, it failed..
select get_previous_day(to_date('1/31/2011','MM/DD/YYYY')) from dual;

As Tom (and for the same reason) I did not analyze the code but when I see:

TO_CHAR (prev_day, 'D') IN ('7', '1')

I know the code will not work in my country (or if it works in my country, it will not work in most other ones).

Regards
Michel

Hi Tom,

Thanks foe the query.

If the requirement is to find out the working date 50 days back then how to achieve this.

Regards;
Gopal

Sir
Sorry for asking this.
Can you please explain how the First Query return by Tom in the posting actually is working,

Thanks in Advance