Re: ORA 06502 error when using TO_DATE with nls parameter inside function - 9i
Date: Thu, 1 Oct 2009 12:31:59 -0700 (PDT)
On Oct 1, 9:05 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> steven acer schreef:
> > Oracle9i Enterprise Edition Release 18.104.22.168.0 - PL/SQL Release
> > 22.214.171.124.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!
> (What's in a date?)- Hide quoted text -
> - Show quoted text -
ok call the variable whatever you want, i copied the code from the internet and changed the variable names. i know i should write my own code and stuff but i am more interested in knowing why it doesn't work. Received on Thu Oct 01 2009 - 14:31:59 CDT