SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
DB Tools - SQL Editor - decimal character

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
DB Tools - SQL Editor - decimal character
Author Message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post DB Tools - SQL Editor - decimal character Reply with quote

The decimal character of NUMBER column isn't displayed in the resultset. The value 12.345 is displayed as 12345 in the resultset of a query. If I export the resultset to a file, then everything is fine.
If I multiply the column by 1 in the query(my temporary workaround), everything is fine too.

Wed May 10, 2006 8:18 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

What symbol do you have in DB Tools options as a decimal separator (see Tools/Options meny, Database tab)?

: The decimal character of NUMBER column isn't displayed in the resultset. The
: value 12.345 is displayed as 12345 in the resultset of a query. If I
: export the resultset to a file, then everything is fine.
: If I multiply the column by 1 in the query(my temporary workaround),
: everything is fine too.

Wed May 10, 2006 9:01 am View user's profile Send private message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

: What symbol do you have in DB Tools options as a decimal separator (see
: Tools/Options meny, Database tab)?

The decimal separator is '.'
Changing to ',' makes no different.

Wed May 10, 2006 9:15 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

What is the data type of the value that doesn't show the decimal symbol?
Do you run a custom query in SQL Editor or you get it this way in some report?
If it is a query, please post an example which I can try reproduce. I assume you use standard German Regional settings in the Windows Control Panel. Right?

: The decimal separator is '.'
: Changing to ',' makes no different.

Wed May 10, 2006 10:20 am View user's profile Send private message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

I have a table with a column defined as NUMBER(15,4) where I store prices.

Query:
SELECT price FROM trades;

A price of 35.35 is shown as 3535.

I use german regional settings under windows, right!
The oracle session parameter for NLS_NUMERIC_CHARACTERS are set to ',.'

: What is the data type of the value that doesn't show the decimal symbol?
: Do you run a custom query in SQL Editor or you get it this way in some
: report?
: If it is a query, please post an example which I can try reproduce. I assume
: you use standard German Regional settings in the Windows Control Panel.
: Right?

Thu May 11, 2006 4:07 am View user's profile Send private message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

Any news ?

: I have a table with a column defined as NUMBER(15,4) where I store prices.

: Query: SELECT price FROM trades;

: A price of 35.35 is shown as 3535.

: I use german regional settings under windows, right!
: The oracle session parameter for NLS_NUMERIC_CHARACTERS are set to ',.'

Mon Jul 24, 2006 9:31 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

Oops. Sorry I have missed your previous message.

I guess you have different NLS settings on the database server (possibly English) and on the Oracle client side (possible German). Or visa versa, German on the server, English on the client.

Do not confuse Oracle client settings with Windows settings. While both have some role I think if you set the Oracle client to use the same settings as the server it should correct the problem. The settings ".," in DB Tools options should match your settings on the client side.
Note that NLS settings on the client (nls_territory) can be changed either directly in the registry or by defining NLS environment variables as documented in Oracle docs.

: Any news ?

Mon Jul 24, 2006 10:04 am View user's profile Send private message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

Setting NLS_NUMERIC_CHARACTERS for the session to ".," does the trick. Somewhat confusing,
the result of the query is displayed with "," as the decimal character, but this is what I wanted.

Thank you.

: Oops. Sorry I have missed your previous message.

: I guess you have different NLS settings on the database server (possibly
: English) and on the Oracle client side (possible German). Or visa versa,
: German on the server, English on the client.

: Do not confuse Oracle client settings with Windows settings. While both have
: some role I think if you set the Oracle client to use the same settings as
: the server it should correct the problem. The settings ".," in
: DB Tools options should match your settings on the client side.
: Note that NLS settings on the client (nls_territory) can be changed either
: directly in the registry or by defining NLS environment variables as
: documented in Oracle docs.

Wed Jul 26, 2006 8:12 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

I think you can save this setting in the system registry in the Oracle home branch. This way it will remain permanent and you will not need to run the command manually every time you connect to db

: Setting NLS_NUMERIC_CHARACTERS for the session to ".," does the
: trick. Somewhat confusing,
: the result of the query is displayed with "," as the decimal
: character, but this is what I wanted.

: Thank you.

Wed Jul 26, 2006 9:22 am View user's profile Send private message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

But I would get "." as decimal character in all other Oracle tools I use,
sql*plus for example, wouldn't I ?

: I think you can save this setting in the system registry in the Oracle home
: branch. This way it will remain permanent and you will not need to run the
: command manually every time you connect to db

Thu Jul 27, 2006 9:28 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

Yes, it would affect all programs.

I guess the following trick could be used to limit the scope of the change to DB Tools only.
DB Tools modules are launched using db_tools.exe command with parameters. For example, to launch the editor you can run db_tools.exe /EDIT
Run db_tools /? for a list and description of supported command line parameters.

You can create a batch file that changes the environment settings for the current console session and all child processes and doesn't affect anything else. For example, create a batch file start_editor.bat with the following contents

set NLS_LANG=AMERICAN
db_tools.exe /EDIT
exit

On your desktop or in the Windows start menu create a shortcut to this batch file and use it to launch DB Tools SQL Editor.

: But I would get "." as decimal character in all other Oracle tools
: I use,
: sql*plus for example, wouldn't I ?

Thu Jul 27, 2006 9:51 am View user's profile Send private message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

Will this issue be fixed in future releases ?

Fri Jul 28, 2006 3:46 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Re: DB Tools - SQL Editor - decimal character Reply with quote

Maybe. This is not happening in DB Tools code. It is buried somewhere between the database driver and Oracle OCI, none of which are made by us. By the way, I just recalled another solution, which is using an ODBC connection.

: Will this issue be fixed in future releases ?

Fri Jul 28, 2006 9:39 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.