Re: ORA 06502 error when using TO_DATE with nls parameter inside function - 9i
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 01 Oct 2009 21:05:11 +0200
Message-ID: <4ac4fd6d$0$83251$e4fe514c_at_news.xs4all.nl>
steven acer schreef:
> 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, 01 Oct 2009 21:05:11 +0200
Message-ID: <4ac4fd6d$0$83251$e4fe514c_at_news.xs4all.nl>
steven acer schreef:
> 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
I would not call a variable of type DATE date.... In my database, this function does not compile. So please post your real code here!
Shakespeare
(What's in a date?)
Received on Thu Oct 01 2009 - 14:05:11 CDT