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

From: Avi Abrami <aabrami_at_intersystemsww.com>
Date: Tue, 17 Dec 2002 06:53:20 +0200
Message-ID: <3DFEADBF.60DE4329_at_intersystemsww.com>

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,
[Quoted] Please excuse Daniel -- I think he's just a grumpy, old man. Anyway, I think the following is what you are looking for:

[Quoted]   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.

[Quoted] SQL> select conv_birthday(11190) from dual;

CONV_BIRT

---

01-NOV-90 [Quoted] SQL> select conv_birthday(1) from dual;

CONV_BIRT

---

[Quoted] [In other words, the second query returns null.]

[Quoted] Note that the above was tested on Oracle 8.1.7.4 on a SUN (sparc) with Solaris 7.

[Quoted] Hope this helps you.

Good Luck,
Avi. Received on Tue Dec 17 2002 - 05:53:20 CET