Home » SQL & PL/SQL » SQL & PL/SQL » SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2)
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 Go to next message
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 #414056 is a reply to message #414046] Mon, 20 July 2009 04:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I know of a few bugs with specific OS Versions / Oracle Versions regarding 64 bit architectures, but you didn't tell us either.

Check on Metalink if that could be it in your case.
Re: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414072 is a reply to message #414056] Mon, 20 July 2009 05:01 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Apologies, I keep forgetting to put that info down.

The database is Oracle 9.2.0 on Solaris

I am using an Oracle 32-bit 10.2 client driver to connect from my PC without problem.

My server is a Windows 2003 x64 Server. The driver file is actually SQORA32.dll - so i'm guessing thats a 32 bit one.
The software running the SQL is 64-bit.

Regards,
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #414076 is a reply to message #414074] Mon, 20 July 2009 05:18 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

the driver on the server is 10.02.00.01

my driver version is 8.01.07.00

those fields are all DATE datatypes

thanks for your time,
Matt
Re: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414078 is a reply to message #414072] Mon, 20 July 2009 05:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
The software running the SQL is 64-bit.


This sounds like you're trying to use 32 bit software on a 64 bit client OS

Is this actually the case?
Re: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414079 is a reply to message #414078] Mon, 20 July 2009 05:26 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

The application is 64 bit (QlikView)
the Server is 64-but.

the driver, i'm assuming from the dll filename is 32-bit.
It has been working fine, but something about that case statement is causing the error mentioned in the first post.
Other queries that QlikView runs work without problem....

very frustrating..
Re: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414135 is a reply to message #414079] Mon, 20 July 2009 08:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SQL State: S1000 looks a lot like an SQL Server error to me....
Re: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414136 is a reply to message #414135] Mon, 20 July 2009 08:14 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

well the error is definitely an Oracle one:SQL Error:[Oracle][ODBC][Ora]ORA-01406: fetched column value was truncated
SQL Scriptline:
SQL State:S1000

there are reads to SQL server in seperate areas of the script - but these are not throwing up errors.

Regards,
Matt
Re: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2) [message #414143 is a reply to message #414136] Mon, 20 July 2009 09:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is a note on Metalink (Doc id 402885.1) about Oracle ODBC Driver Release 10.2.0.3.0, and amongst the bugs fixed, they list:
Quote:
ODBC driver used to report ORA-1406 when selecting calculated number with large
precision from view. (Bug4546618).



You could try rewriting the query and forcing a lower level of precision on the numbers returned by the query?
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 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then it might also be an SQLServer bug. Razz

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, ....)
Previous Topic: Materialized view fast refresh is working slow compared to complete refresh
Next Topic: Drop Partition makes Index unstable
Goto Forum:
  


Current Time: Sat Dec 03 01:04:00 CST 2016

Total time taken to generate the page: 0.09665 seconds