Re: Catching date conversion exceptions in PL/SQL ...
Date: Mon, 16 Dec 2002 21:12:13 -0800
Message-ID: <3DFEB22D.896ECDB2_at_exesolutions.com>
Avi Abrami wrote:
> DA Morgan wrote:
> >
> > Ludwig Nörg wrote:
> >
> > > Hello guys,
> > >
> > > I try to convert a number field in the format '[d]dmmyy' with the
> > > following construct:
> > >
> > > FUNCTION conv_birthday(birthday NUMBER) RETURN DATE IS
> > > ret_birthday DATE;
> > > BEGIN
> > > ret_birthday := TO_DATE(LPAD(TO_CHAR(birthday),6,'0'),
> > > 'ddmmyy');
> > > EXCEPTION
> > > WHEN OTHERS THEN
> > > ret_birthday := NULL;
> > >
> > > RETURN ret_birthday;
> > > END conv_birthday;
> > >
> > > In case the conversion fails I would like to receive a NULL value, but
> > > it does not work. It returns with error code ORA-06503 and ORA-06512.
> > > Can anybody fix this piece of code?
> > >
> > > Thanx in advance,
> > > Ludwig Nörg
> >
> > And you are going to ask us look up the error message it fails with?
> >
> > Dan Morgan
>
> Ludwig,
> Please excuse Daniel -- I think he's just a grumpy, old man.
> Anyway, I think the following is what you are looking for:
>
> 1 CREATE OR REPLACE FUNCTION conv_birthday(birthday NUMBER)
> 2 RETURN DATE
> 3 IS
> 4 ret_birthday DATE;
> 5 BEGIN
> 6 BEGIN
> 7 ret_birthday := TO_DATE(LPAD(TO_CHAR(birthday),6,'0'),
> 8 'ddmmyy');
> 9 EXCEPTION
> 10 WHEN OTHERS THEN
> 11 ret_birthday := NULL;
> 12 END;
> 13 RETURN ret_birthday;
> 14* END conv_birthday;
> SQL> /
>
> Function created.
>
> SQL> select conv_birthday(11190) from dual;
>
> CONV_BIRT
> ---------
> 01-NOV-90
>
> SQL> select conv_birthday(1) from dual;
>
> CONV_BIRT
> ---------
>
> [In other words, the second query returns null.]
>
> Note that the above was tested on Oracle 8.1.7.4
> on a SUN (sparc) with Solaris 7.
>
> Hope this helps you.
>
> Good Luck,
> Avi.
Not grumpy at all. I just couldn't believe someone would include the ORA-##### and not the text that went with it.
Daniel Morgan Received on Tue Dec 17 2002 - 06:12:13 CET