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
Numbers returned from SQLFunction (Read 2668 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Numbers returned from SQLFunction
31.01.09 at 22:47:41
 
Numbers returned from SQLFunction
 
Quote:
Numbers returned from a function do not display correctly in Excel. We don't want to use the =Value() function around the =SQLFunction() function all the time. The problem stands in the decimal separator from a number. I am using the following function for testing:

create or replace FUNCTION MYVALUE
RETURN NUMBER AS
BEGIN
 RETURN 0.5;
END MYVALUE;

If I call this function from Excel using =sqlfunction("myvalue") I will obtain 0.5 or 0,5 in text format depending on the NLS_NUMERIC_CHARACTERS setting.

 
This did indeed not work correctly especially when you run the client with Excel with one decimal separator and when the database runs using a different one.
 
I had already fixed this for the OO4O driver but not for other connections. Now, when connecting through any driver/provider to Oracle an alter session will be executed to fix the decimal separator problem is the database runs with a different setting as the desktop.
Back to top
 
« Last Edit: 31.01.09 at 22:58:38 by Gerrit-Jan Linker »  

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




Posts: 75
Re: Numbers returned from SQLFunction
Reply #1 - 31.01.09 at 23:00:37
 
Examples:
 
Code:
create or replace function f_one return number
as
begin    return 1.5;
end;
 


 
In an Excel cell, type the formula:
=sqlfunction("f_one")
 
Or execute the following SQL:
:D10 := f_one;
 
The same can be done for dates:
 
Code:
create or replace function f_sysdate return date
as
begin    return sysdate;
end;
 


 
In an Excel cell, type the formula:
=sqlfunction("f_sysdate")
 
Or execute the following SQL:
:D10 := f_sysdate;
Back to top
 
« Last Edit: 31.01.09 at 23:02:08 by Gerrit-Jan Linker »  

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