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