Re: Problem with DATE fields in Oracle 8.05

What is the NLS_DATE_FORMAT in the database ? It must match the format you use to load the data, in your case the NLS_DATE_FORMAT must be 'DD-MON-YY'....If not, the load will fail...

Hope it helps,
John Greco

"Arati Vijay" <vijayj_at_singnet.com.sg> wrote:

>Hi,
>
>Am testing Oracle 8.05 enterprise edition on AIX 4.3.1
>The following query works fine on Oracle 7.3, but gives an
>error :ORA-01858 on Oracle 8.05
>
>It cribs on the date field, saying a non-numeric character
>was found where a numeric was expected.
>----------------------------------------------------------------------------
>-----------------
>
>SQL> desc temp
> Name Null? Type
> ------------------------------- -------- ----
> BR_CODE NOT NULL VARCHAR2(4)
> CCY_CODE NOT NULL VARCHAR2(4)
> DESCRIP VARCHAR2(10)
> TEMP1_DT DATE
>
>SQL> INSERT INTO TEMP VALUES(
> '1212',
> '2121',
> 'AAAAAA',
> '31-DEC-99');
>
>
>'31-DEC-99')
>*
>ERROR at line 1:
>ORA-01858: a non-numeric character was found where a numeric was expected
>----------------------------------------------------------------------------
>------------
>
>Auto conversion from char to date is not happening.
>This works fine when i use the TO_DATE function for
>testing.
>
>However, am not in a position to use the TO_DATE
>function, as the update to the database will be done by
>a third party tool, that relies on Oracle to auto-convert a
>date supplied in the DD-MON-YY format.
>
>Am facing a similar problem on the SQL*Loader too.
>
>Any clues?...
>
>Thanks,
>VJ
>
>
>
>

  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------    http://www.newsfeeds.com The Largest Usenet Servers in the World! ------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==----- Received on Thu Sep 02 1999 - 11:41:40 CDT