Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Decimal point as a comma (Read 10072 times)
blucier
YaBB Newbies
*


I Love SQL*XL

Posts: 4
Decimal point as a comma
10.08.10 at 20:53:20
 
We have noticed when connecting to our Oracle DB from Excel 2007 it returns values with a comma as the decimal point instead of a period.  The Windows 7 client uses English Canada as the Regional setting so I was wondering if there is a similar setting in SQL*XL or does it use the regional setting from the client?  The Oracle 10 client is installed on the W7 PC.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Decimal point as a comma
Reply #1 - 11.08.10 at 00:06:27
 
This should be automatically be corrected in SQL*XL. There are several aspects to this problem. Firstly, it should not go wrong when a numeric data type is used (at the Oracle server). Then the value is passed as a binary value to the client and SQL*XL gets it as a binary irrespective of the formatting setup. Then putting it into Excel will use the desktop default display format. Some numeric data types are soo large (soo many decimals/numbers) that the values are sent as text. Now the language settings on the server and on the client become an issue. If they differ problems arise which should automatically be dealt with in SQL*XL. Aparently you have problems so we need to work out why it fails.
 
To start I found the following tests to be good in the past. Can you run these please?
 
What is the result of the following query?
select to_char(3/2) from dual
Is the outcome 1,5 or 1.5?
 
In Excel what is the result of the following formula:
=3/2
Is the outcome 1,5 or 1.5
 
Since the passing of values is done by a driver, please tell me which driver you use. You can find the details in SQL*XL's about the database window and the driver/provider is named in the connection dialog.
Back to top
 
« Last Edit: 11.08.10 at 00:07:25 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
blucier
YaBB Newbies
*


I Love SQL*XL

Posts: 4
Re: Decimal point as a comma
Reply #2 - 13.08.10 at 15:29:15
 
Quote from Gerrit-Jan Linker on 11.08.10 at 00:06:27:
This should be automatically be corrected in SQL*XL. There are several aspects to this problem. Firstly, it should not go wrong when a numeric data type is used (at the Oracle server). Then the value is passed as a binary value to the client and SQL*XL gets it as a binary irrespective of the formatting setup. Then putting it into Excel will use the desktop default display format. Some numeric data types are soo large (soo many decimals/numbers) that the values are sent as text. Now the language settings on the server and on the client become an issue. If they differ problems arise which should automatically be dealt with in SQL*XL. Aparently you have problems so we need to work out why it fails.

To start I found the following tests to be good in the past. Can you run these please?

What is the result of the following query?
select to_char(3/2) from dual
Is the outcome 1,5 or 1.5?

In Excel what is the result of the following formula:
=3/2
Is the outcome 1,5 or 1.5

Since the passing of values is done by a driver, please tell me which driver you use. You can find the details in SQL*XL's about the database window and the driver/provider is named in the connection dialog.

Back to top
 

About.jpg
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Decimal point as a comma
Reply #3 - 13.08.10 at 15:42:34
 
Thank you. If you could also be so kind to send the output of the following tests that would be great: Quote:

What is the result of the following query?
select to_char(3/2) from dual
Is the outcome 1,5 or 1.5?

In Excel what is the result of the following formula:
=3/2
Is the outcome 1,5 or 1.5
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
blucier
YaBB Newbies
*


I Love SQL*XL

Posts: 4
Re: Decimal point as a comma
Reply #4 - 13.08.10 at 16:00:57
 
Sorry I thought I did provide the output...
 
SQL command result is 1,5 and the Excel formula was 1.5
 
I've attached the Excel sheet...
 
Back to top
« Last Edit: 13.08.10 at 16:02:09 by blucier »  
  IP Logged
blucier
YaBB Newbies
*


I Love SQL*XL

Posts: 4
Re: Decimal point as a comma
Reply #5 - 17.11.10 at 20:00:05
 
We tried uninstalling SQL*XL and the Oracle 10 client completely.  Results are still the same.
 
Is this a possible region / language issue?
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Decimal point as a comma
Reply #6 - 17.11.10 at 22:04:53
 
I confirm there is a problem. In the past there was code that prevented this from happening. Aparently it is no longer working. I recall it was needed only for OO4O connections...
 
To reproduce the problem, do the following:
Run the following SQL statement: select 3/2 from dual.  
In the regional settings section of the control panel you can change the decimal separator and the thousands separator. By changing the values you can set it so there is a mismatch between how Oracle posts the number and how Excel receives it.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1