Re: ORA 06502 error when using TO_DATE with nls parameter inside function - 9i
From: steven acer <dudesterr_at_gmail.com>
Date: Thu, 1 Oct 2009 23:58:22 -0700 (PDT)
Message-ID: <ce5d3a02-87d6-4219-afdb-819e50f2cc2d_at_l2g2000yqd.googlegroups.com>
On Oct 2, 3:24 am, John Hurley <johnbhur..._at_sbcglobal.net> wrote:
> On Oct 1, 2:48 pm, steven acer <dudest..._at_gmail.com> wrote:
>
>
>
> > Oracle9i Enterprise Edition Release 9.2.0.1.0 - PL/SQL Release
> > 9.2.0.1.0 .
> > Hi, i wrote the following function in oracle 9i :
>
> > create or replace function isDate(inputStr VARCHAR2) RETURN NUMBER
> > IS
> > date DATE;
> > BEGIN
> > date:=TO_DATE(inputStr,'YYYY/MM/DD','NLS_CALENDAR=GREGORIAN');
> > RETURN 1;
> > END ISDATE;
>
> > SQL>function created
>
> > i removed the exception block to show the exception being thrown .when
> > i try to run it i get
>
> > SQL>SELECT ISDATE('2001/01/01') from dual;
>
> > ERROR at line 1:
> > ORA-06502: PL/SQL: numeric or value error
> > ORA-06512: at "CODSYS.ISDATE", line 5
>
> > eventhough the value passed to the function is a valid gregorian
> > date,the call to TO_DATE inside the function seems to spit an error,
> > however if i use TO_DATE directly on the SQL command line it executes
> > normally:
> > SQL> SELECT TO_DATE('2001/01/01','YYYY/MM/
> > DD','NLS_CALENDAR=GREGORIAN') from dual;
>
> > TO_DATE('
> > ---------
> > 01-JAN-01
>
> > and if i remove the nls parameter from the call,
> > NLS_CALENDAR=GREGORIAN and set the session's NLS_CALENDAR to GREGORIAN
> > the function works as well.
>
> > also on 10gR2(10.2.0.1.0 ), the same function runs normally and
> > returns the correct value.
> > i have the same nls session parameters on 9i and on 10g so i don't
> > know what could the problem be, is it a difference in the way 10g and
> > 9i handle it.
>
> > thanks
>
> It is hard to understand exactly what you are doing since the code you
> posted is not evidently the code you are actually testing with.
>
> It sounds like both the releases you are using 10.2.0.1.0 and
> 9.2.0.1.0 are base releases ... not patched with available maintenance
> from oracle ( well available if you have a support contract ).
> Whenever you start testing strange things and getting strange errors
> on base releases you may run into bugs that get patched later.
>
> That could explain that it might have been fixed by 10.2.0.1.0 but
> could have been a bug in 9.2.0.1.0 ... try patching to 9.2.0.8 and see
> if your "bug" goes away.
Date: Thu, 1 Oct 2009 23:58:22 -0700 (PDT)
Message-ID: <ce5d3a02-87d6-4219-afdb-819e50f2cc2d_at_l2g2000yqd.googlegroups.com>
On Oct 2, 3:24 am, John Hurley <johnbhur..._at_sbcglobal.net> wrote:
> On Oct 1, 2:48 pm, steven acer <dudest..._at_gmail.com> wrote:
>
>
>
> > Oracle9i Enterprise Edition Release 9.2.0.1.0 - PL/SQL Release
> > 9.2.0.1.0 .
> > Hi, i wrote the following function in oracle 9i :
>
> > create or replace function isDate(inputStr VARCHAR2) RETURN NUMBER
> > IS
> > date DATE;
> > BEGIN
> > date:=TO_DATE(inputStr,'YYYY/MM/DD','NLS_CALENDAR=GREGORIAN');
> > RETURN 1;
> > END ISDATE;
>
> > SQL>function created
>
> > i removed the exception block to show the exception being thrown .when
> > i try to run it i get
>
> > SQL>SELECT ISDATE('2001/01/01') from dual;
>
> > ERROR at line 1:
> > ORA-06502: PL/SQL: numeric or value error
> > ORA-06512: at "CODSYS.ISDATE", line 5
>
> > eventhough the value passed to the function is a valid gregorian
> > date,the call to TO_DATE inside the function seems to spit an error,
> > however if i use TO_DATE directly on the SQL command line it executes
> > normally:
> > SQL> SELECT TO_DATE('2001/01/01','YYYY/MM/
> > DD','NLS_CALENDAR=GREGORIAN') from dual;
>
> > TO_DATE('
> > ---------
> > 01-JAN-01
>
> > and if i remove the nls parameter from the call,
> > NLS_CALENDAR=GREGORIAN and set the session's NLS_CALENDAR to GREGORIAN
> > the function works as well.
>
> > also on 10gR2(10.2.0.1.0 ), the same function runs normally and
> > returns the correct value.
> > i have the same nls session parameters on 9i and on 10g so i don't
> > know what could the problem be, is it a difference in the way 10g and
> > 9i handle it.
>
> > thanks
>
> It is hard to understand exactly what you are doing since the code you
> posted is not evidently the code you are actually testing with.
>
> It sounds like both the releases you are using 10.2.0.1.0 and
> 9.2.0.1.0 are base releases ... not patched with available maintenance
> from oracle ( well available if you have a support contract ).
> Whenever you start testing strange things and getting strange errors
> on base releases you may run into bugs that get patched later.
>
> That could explain that it might have been fixed by 10.2.0.1.0 but
> could have been a bug in 9.2.0.1.0 ... try patching to 9.2.0.8 and see
> if your "bug" goes away.
here's the exact code i am using :
CREATE OR REPLACE FUNCTION isdate (inputString VARCHAR2)
RETURN NUMBER
IS
dateVar DATE;
BEGIN
IF (inputString IS NULL) THEN
RETURN 1;
ELSIF (LENGTH(inputString)<>10) THEN
RETURN 0;
END IF;
dateVar := TO_DATE (inputString, 'YYYY/MM/DD',
'NLS_CALENDAR=GREGORIAN');
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
I don't have a support contract with oracle though.
Received on Fri Oct 02 2009 - 01:58:22 CDT