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 12:31:59 -0700 (PDT)
Message-ID: <8b77198d-f8b2-42cf-9641-d0044c405c13_at_h13g2000yqk.googlegroups.com>

On Oct 1, 9:05 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> 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?)- 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