Re: Invalid Dates and Exception Handling in PL/SQL

Redrobot5050 schreef:
> 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
>

What does the rest of the procedure do?
Why not process the error (pack_errs.date_error) in the first exception block?

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...