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 11:48:16 -0700 (PDT)
Message-ID: <61c77788-f07a-4f71-8bff-6f07cd46b1f9_at_m7g2000prd.googlegroups.com>

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 Received on Thu Oct 01 2009 - 13:48:16 CDT