Re: Catching date conversion exceptions in PL/SQL ...

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<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

Thanx for your appreciation, I forgot it in my despiration ... nice guy Daniel.

But I found the solution for myself, putting the call into an own exception begin ... end block.

Thank you guys,
Ludwig Received on Tue Dec 17 2002 - 15:07:12 CET