Re: ORA-01483: invalid length for DATE or NUMBER bind variable :(

On May 13, 10:59 am, "pluton" <zielonad..._at_gazeta.pl> wrote:
> > 'This is a string, 2008-05-15, & not a date datatype'
>
> > When you want/need a date datatype use TO_DATE() function.- Hide quoted
> > text -
>
> So WHY it works on one database, and does not on another ?
> I know to_date function very well.
>
> > Pluton, you really need to post the actual insert or update statement
>
> Its unfortunately NOT so simple. I am inserting values using
> Oracle, ODBC and DB2/CLI Template Library, Version 4.0.101, by Sergei Kuchin
>
> > so that board readers can see exactly what you are doing.
>
>  If I had known  what I am doing  , I wouldn't have asked stupid questions
> here :)
>
> > What is the
>
> default date format setting on each database?  In each session where
> you perform the insert?  A difference in the nls_date_format database
> or OS environment variable settings could easily cause this error with
> a date value.
>
> PARAMETER VALUE
> NLS_LANGUAGE POLISH
> NLS_TERRITORY POLAND
> NLS_CURRENCY zl
> NLS_ISO_CURRENCY POLAND
> NLS_NUMERIC_CHARACTERS ,
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMAT RR/MM/DD
> NLS_DATE_LANGUAGE POLISH
> NLS_CHARACTERSET EE8ISO8859P2
> NLS_SORT POLISH
> NLS_TIME_FORMAT HH24:MI:SSXFF
> NLS_TIMESTAMP_FORMAT RR/MM/DD HH24:MI:SSXFF
> NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
> NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
> NLS_DUAL_CURRENCY zl
> NLS_NCHAR_CHARACTERSET AL16UTF16
> NLS_COMP BINARY
> NLS_LENGTH_SEMANTICS BYTE
> NLS_NCHAR_CONV_EXCP FALSE
>
> >  For a problem with a number I need to see the SQL to
>
> form any ideas.
>
> I desperately need it too :)
>
> thanks
> regards
> pluton

Suggestion, Log onto each Oracle database using the ID that the task runs with and query v$parameter for the value of nls_date_format. Compare the format returned on each instance to the other. If different that should be the problem. If the same then check the rest of the nls parameters.

Also check for any nls parameters set at the OS level.

Can you verify that the same template library (at least same version) is being used with both databases?

Do you have or can you find on the net any documentation for the lirbray routine that you are using so you can compare the documentation to the calls in your application.

Do you have other code that makes the same library calls that you can test to see if the error also happens one one db but not the other?

Look to see if at install time (compile and link) if there are any notes about picking up OS environment variable values. (I am looking to see if the date format for one of the libraries is picked up at compile time)

HTH -- Mark D Powell -- Received on Tue May 13 2008 - 10:42:10 CDT