Invalid Dates and Exception Handling in PL/SQL

Redrobot5050

unread,

Feb 1, 2007, 1:19:28 PM2/1/07

to

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

Frank van Bortel

unread,

Feb 1, 2007, 2:40:37 PM2/1/07

to

Redrobot5050 schreef:

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

DA Morgan

unread,

Feb 1, 2007, 5:26:57 PM2/1/07

to

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

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
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Rene Nyffenegger

unread,

Feb 2, 2007, 12:27:49 PM2/2/07

to

I don't know what's wrong with your code, the following snippet

***************************************************************
* create or replace 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'
* ) as
*
* v_date_from date;
* v_date_to date;
* invalid_date exception;
* pragma exception_init(invalid_date,-01839);
*
* begin
*
* BEGIN
* v_date_from := to_date(p_day_begin||
* p_month_begin||
* p_year_begin,
* 'DDMMYYYY');
*
* v_date_to := to_date(p_day_end||
* p_month_end||
* p_year_end,'DDMMYYYY');
*
* EXCEPTION WHEN invalid_date THEN
* dbms_output.put_line('caught 1');
* raise invalid_date;
* END;
*
*
* dbms_output.put_line('ok');
*
*
* exception
* when invalid_date then
* dbms_output.put_line('caught 2');
* end;
* /
* show err
*
* exec VIEW_REPORTS('1','11','2006', '31','11','2006');
***************************************************************

prints

caught 1
caught 2

hth,
Rene

--
Rene Nyffenegger
http://www.adp-gmbh.ch