SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414046] |
Mon, 20 July 2009 03:44  |
matpj
Messages: 115 Registered: March 2006 Location: London, UK
|
Senior Member |
|
|
Hi all,
I am receiving the error:
ORA-01406: fetched column value was truncated
SQL Scriptline:
SQL State:S1000
when I try to run my SQL on a server that has a 64-bit Oracle driver.
The problem seems to be in this statement:
case
when substr(cst.dsti_proj_ref, 7, 1) = 'S'
then ((to_date(cst.dsti_est_ds_delivery, 'dd/mm/yy') - to_date(cst.dsti_des_del_date, 'dd/mm/yy'))/(to_date(cst.dsti_des_del_date, 'dd/mm/yy') - to_date(nbi.start_date, 'dd/mm/yy') +1))
when
substr(cst.dsti_proj_ref, 7, 1) = 'D'
then
decode((to_date(cst.dsti_client_del_date, 'dd/mm/yy') - to_date(nbi.start_date, 'dd/mm/yy') +1),0,0,
((to_date(cst.dsti_est_dv_delivery, 'dd/mm/yy') - to_date(cst.dsti_client_del_date, 'dd/mm/yy'))/
(to_date(cst.dsti_client_del_date, 'dd/mm/yy') - to_date(nbi.start_date, 'dd/mm/yy') +1)))
end as Schedule_Variance,
as i have had a similar problem before with dates - but got around it using to_date to dormat all dates in the same way.
I can't seem to work out what the problem is here though.
Can anybody suggest anything?
thanks,
Matt
|
|
|
|
|
Re: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414074 is a reply to message #414072] |
Mon, 20 July 2009 05:13   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Those are still not the exact 4-digit version numbers.
I know of one specific bug, that *Applications* on 64-bit Windows can only connect to Oracle 9.0.2.X on the Server if the Versions is patched to 9.2.0.8, because of some bug that the Excecutable can not connect when it's in a directory with a "(" or ")". So you might have to patch the Server to .8
What data type are "cst.dsti_est_ds_delivery", "cst.dsti_des_del_date" by the way? Varchar2 or Date?
|
|
|
|
|
|
|
|
|
Re: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414144 is a reply to message #414136] |
Mon, 20 July 2009 09:05  |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Then it might also be an SQLServer bug. 
Or a SQLNet bug. Or a Oracle server Bug. Or a OLEDB driver bug.
We had a problem in that direction once, where neither the Oracle nor the Microsoft OLEDB driver for Oracle were working.
We actually ended up using basically the Microsoft driver, but replacing some DLLs of that with the Oracle ones. (With much to-and-fro between both Oracle and Microsoft support)
The first thing to do would definetely be trying to figure out which component exactly is creating the problem.
e.g. : Run the query on the server in SQL plus, Run in in an ODBC client, run in in an OLEDB client, ....)
If you find what application level is causing the problem, break the query down into parts, so that you find the exact statement that is causing the problem. (is it the case itself, or the substring, or the to_date, ....)
|
|
|