Oracle ODBC x64 - getting 0 when selecting a number(9) column
- by MatsL
I'm having a really weird problem with a third party web service that uses an ODBC connection to Oracle 10.2.0.3.0. I've written a .NET client that generates the same SQL as the web service so I can find out what's going on.
The web service is hosted by IIS 6 that's in x64 mode so we use Oracle x64 client.
The oracle client version is 10.2.0.1.0.
I have a table that looks like this (I've removed some columns and names):
SQL> describe tablename;
Name Null? Type
----------------------------------------- -------- ----------------------------
KOD VARCHAR2(30)
ORDNING NUMBER(5)
AVGIFT NUMBER(9)
I then in SQL*Plus issue the following statement:
SELECT KOD as kod, AVGIFT as riskPoang
FROM tablename Where upper(KODTYP) = 'OBJLIVSV_RISKVERKSAMTYP' ORDER BY ORDNING
And I get the following result:
KOD RISKPOANG
------------------------------ ----------
Hög risk 55
Mellan risk 35
Låg risk 15
Mycket låg risk 5
But when I execute the exact same SQL using the same DSN on the same machine I get this:
Values
Kod: Hög risk RiskPoäng: 0
Kod: Mellan risk RiskPoäng: 0
Kod: Låg risk RiskPoäng: 0
Kod: Mycket låg risk RiskPoäng: 0
If I first cast the number to varchar and then back again to number, like this:
SELECT KOD as kod, to_number(to_char(AVGIFT, '99'), '9999999999') as riskPoang
FROM tablename Where upper(KODTYP) = 'OBJLIVSV_RISKVERKSAMTYP' ORDER BY ORDNING
I get the correct result:
Values
Kod: Hög risk RiskPoäng: 55
Kod: Mellan risk RiskPoäng: 35
Kod: Låg risk RiskPoäng: 15
Kod: Mycket låg risk RiskPoäng: 5
Has anyone else experiences this? It's incredibly annoying and I'm completely stuck and not sure what to do next. We have a third party web service that use these tables so I must get the original SQL-statement to work since I can't modify its code.
And pointers are greatly appreciated! :-)
Best regards,
Mats
