NLS_PARAMS/Regional setting problem

[Robertas]

it seems that PLSQL Developer is displaying data with oracle format but posting (after edit) with windows regional settings format. This example should reproduce this behavior

/*
NLS PARAMS:
select * from nls_database_parameters
NLS_NUMERIC_CHARACTERS .,
select * from nls_instance_parameters
NLS_NUMERIC_CHARACTERS .,
select * from nls_session_parameters
NLS_NUMERIC_CHARACTERS .,
Windows regional settings
Decimal symbol: ,
Digit grouping symbol: .
PL/SQL Developer
numbers->Oracle format
*/
--STEP1
create table test_tbl1(col1 number(15,3));
insert into test_tbl1(col1)values(5.3);
--STEP2
select t.*, t.rowid from test_tbl1 t
-- grid is showing: 5.300
--STEP3
-- edit value to 5.678 (eny value) , COMMIT
--STEP4
select t.*, t.rowid from test_tbl1 t
-- grid is showing: 5678.000

 

[Marco Kalter]

By default number values are displayed and edited in accordance with the Windows Regional Settings.

This can however be overruled by the SQL Window preference "Number fields to_char", which will apply Oracle NLS settings. Furthermore there is also an "NLS Setting" preference page where you can define the number format.

 

[Robertas]

Thank you for your replay, but I get sorting problem in result grid when using "Number fields to_char"

for example ascending:
197781
1152538
1073774

any solutions?

PL/SQL Developer
Version 8.0.0.1494

 

[Marco Kalter]

Numbers are now fetched as text, so local sorting will also sort as text. Use an order by clause to get the sorted results from Oracle.

 

[Robertas]

So there is no possibility to get numbers displayed as it is set in Oracle NLS settings and have values in result grid of their original type?

 

[Marco Kalter]

If you disable the "number fields to_char" option and set the number format preference (Tools > Preferences > NLS Options), you can get any formatting you like.