Invalid Dates and Exception Handling in PL/SQL

Hello all,

I have inherited support responsibilities an oracle web application which uses PL/SQL and the Oracle Web Toolkit. There is a routine I am trying to fix up which is used to generate a daily status report. This report receives two dates in the form of drop down menus (specifying day, month, and year) and concatenates them to create two date ranges.

However, the routine does not check for valid dates, so it can crash if given a range like 01-NOV-2006 to 31-NOV-2006. The oracle error it will return when an invalid date range is specified is -01839.

Here is my problem:
I want to catch the exception (-01839) using pragma exception_init and re-direct the user to an error page, using our pack_errs package. My problem is that this does not seem to be working and I don't know why:

Here's a snippet of code:

procedure VIEW_REPORTS(p_day_begin varchar2 default '1',
                       p_month_begin  VARCHAR2 default 'JAN',
                       p_year_begin   VARCHAR2 DEFAULT '2006',
                       p_day_end varchar default '31',
                       p_month_end  VARCHAR2 default 'DEC',
                       p_year_end   VARCHAR2 DEFAULT '2006',
                       p_report_type varchar2 default 'DAILY',
                       p_report_selection VARCHAR2 DEFAULT 'ALL') as

 v_date_from date;
  v_date_to date;
  invalid_date exception;
  perm_exception exception;
  pragma exception_init(invalid_date,-01839);

begin

--some permissions code here that I've ommitted for the sake of this
example

  case p_report_type
  when 'DAILY' then
   BEGIN
    v_date_from := to_date(p_day_begin||p_month_begin|| p_year_begin,'DDMONYYYY');

    v_date_to := to_date(p_day_end||p_month_end|| p_year_end,'DDMONYYYY'); <--- Exception occurs here, when

    EXCEPTION

      WHEN invalid_date THEN
       raise invalid_date;
     END;

--rest of the procedure

exception
  when invalid_date then --again, catching the error.     pack_errs.date_error;
end;

I have successfully used Pragma exception_init to override an oracle exception so that I could handle it with our error package in the past, but it does not appear to work in this case. Does anyone have any suggestions?

Thanks in Advance,
Chris Received on Thu Feb 01 2007 - 12:19:28 CST