Re: ORA 06502 error when using TO_DATE with nls parameter inside function - 9i
From: John Hurley <johnbhurley_at_sbcglobal.net>
Date: Thu, 1 Oct 2009 18:24:59 -0700 (PDT)
Message-ID: <9a7abc01-c9f6-45e7-b07a-b91c13f07176_at_j4g2000yqa.googlegroups.com>
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
Date: Thu, 1 Oct 2009 18:24:59 -0700 (PDT)
Message-ID: <9a7abc01-c9f6-45e7-b07a-b91c13f07176_at_j4g2000yqa.googlegroups.com>
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. Received on Thu Oct 01 2009 - 20:24:59 CDT