Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Invalid Dates and Exception Handling in PL/SQL

Re: Invalid Dates and Exception Handling in PL/SQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 01 Feb 2007 14:26:57 -0800
Message-ID: <1170368818.227133@bubbleator.drizzle.com>


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.org
Received on Thu Feb 01 2007 - 16:26:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US