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

pluton

unread,

May 13, 2008, 9:41:24 AM5/13/08

to

Hi, I have the following table :

A INTEGER (-> 19300 )
B NUMBER(5) (->1)
CD DATE (->2008-05-10 10:51:14)
E INTEGER (-> 50070 )
NOTE VARCHAR2(100) ( -> 'short text, no national characters' )

I am trying to insert values as above (indicated by -> )

On the first database operation is successfull, on another I have ORA-01483.

The only difference between these two databases is
NLS_CHARACTERSET EE8ISO8859P2 (success)
NLS_CHARACTERSET AL32UTF8 (failure)

Is NLS_CHARACTERSET possible reason of problem ?

best regards
pluton

Ana C. Dent

unread,

May 13, 2008, 10:08:17 AM5/13/08

to

"pluton" <zielo...@gazeta.pl> wrote in
news:g0c5q2$4m2$1...@inews.gazeta.pl:

Possible? yes.
Likely? no

With Oracle characters between single quote marks are STRINGS!

'This is a string, 2008-05-15, & not a date datatype'

When you want/need a date datatype use TO_DATE() function.

Mark D Powell

unread,

May 13, 2008, 10:17:38 AM5/13/08

to

On May 13, 10:08 am, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> "pluton" <zielonad...@gazeta.pl> wrote innews:g0c5q2$4m2$1...@inews.gazeta.pl:

> When you want/need a date datatype use TO_DATE() function.- Hide quoted text -
>
> - Show quoted text -

Pluton, you really need to post the actual insert or update statement
so that board readers can see exactly what you are doing. 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. For a problem with a number I need to see the SQL to
form any ideas.

HTH -- Mark D Powell --

pluton

unread,

May 13, 2008, 10:59:19 AM5/13/08

to

> '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

Mark D Powell

unread,

May 13, 2008, 11:42:10 AM5/13/08

to

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)

Mark D Powell

unread,

May 13, 2008, 11:49:04 AM5/13/08

to

> HTH -- Mark D Powell --- Hide quoted text -

>
> - Show quoted text -

PS - Can you set up an SQL trace for the failing task? This would
hopefully capture the actual SQL and give you a better idea of the
actual problem.

joel garry

unread,

May 13, 2008, 12:09:34 PM5/13/08

to

Please post your exact Oracle version and see metalink Note:465497.1
(bug 6085625) and Note:444045.1 (not a bug 5241003).

My guess is you have different drivers or patches FWIW.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20080513/news_1b13hp.html