Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Invalid Dates and Exception Handling in PL/SQL
Redrobot5050 wrote:
> 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
Please don't post to every usenet group. Please choose only one when responding.
How do you know you are getting ORA-01839?
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Feb 1 14:25:53 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> declare
2 x DATE;
3 begin
4 x := TO_CHAR('31-FEB-2007');
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 4
SQL> Perhpas 1830 not 1839.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Feb 01 2007 - 16:26:57 CST