| nls_numeric_characters doesn't change after reboot [message #673723] |
Thu, 06 December 2018 13:30  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I have this database on my laptop, in which I am testing the nls_numeric_characters param:
SQL> show user
USER is "SYS"
SQL>
SQL> show parameter nls_numeric_character
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters string .,
SQL>
SQL> select 1+'0,1' from dual;
select 1+'0,1' from dual
*
ERROR at line 1:
ORA-01722: invalid number
So I try to change it with alter system :
SQL> alter system set nls_numeric_characters=',.' scope=both;
alter system set nls_numeric_characters=',.' scope=both
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SQL>
got an error..
So ok, I am just changing in spfile:
SQL> alter system set nls_numeric_characters=',.' scope=spfile;
System altered.
SQL>
Then I reboot and try to see if it changed:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1837244416 bytes
Fixed Size 2256184 bytes
Variable Size 1157628616 bytes
Database Buffers 671088640 bytes
Redo Buffers 6270976 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select 1+'0,1' from dual;
select 1+'0,1' from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL>
SQL> show parameter nls_numeric_characters
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters string .,
SQL>
SQL>
SQL>
It haven't.
Anybody can explain why ??
Thanks in advance.
Andrey
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673726 is a reply to message #673723] |
Thu, 06 December 2018 14:05  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:SQL> show user
USER is "SYS"
Read SYS is special.
SQL> show user
USER is "MICHEL"
SQL> alter session set NLS_NUMERIC_CHARACTERS='.,';
Session altered.
SQL> select 1+'0,1' from dual;
select 1+'0,1' from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';
Session altered.
SQL> select 1+'0,1' from dual;
1+'0,1'
----------
1,1
1 row selected. |
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673826 is a reply to message #673726] |
Tue, 11 December 2018 08:55 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 06 December 2018 22:05
Quote:SQL> show user
USER is "SYS"
Read SYS is special.
SQL> show user
USER is "MICHEL"
SQL> alter session set NLS_NUMERIC_CHARACTERS='.,';
Session altered.
SQL> select 1+'0,1' from dual;
select 1+'0,1' from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';
Session altered.
SQL> select 1+'0,1' from dual;
1+'0,1'
----------
1,1
1 row selected.
I tried the same with a regular user.. didn't work:
SQL> show user
USER is "SYS"
SQL>
SQL>
SQL> create user a identified by a;
User created.
SQL> grant dba to a;
Grant succeeded.
SQL>
SQL>
SQL> conn a/a
Connected.
SQL>
SQL>
SQL> show parameter nls_num
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters string .,
SQL>
SQL>
SQL> alter system set nls_numeric_characters=',.' scope=spfile;
System altered.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\ORACLE\APP\ORACLE\PRODUCT\1
1.2.0\DBHOME_1\DATABASE\SPFILE
MAGIC.ORA
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1837244416 bytes
Fixed Size 2256184 bytes
Variable Size 1157628616 bytes
Database Buffers 671088640 bytes
Redo Buffers 6270976 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter nls_num
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters string .,
SQL>
|
|
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673828 is a reply to message #673827] |
Tue, 11 December 2018 10:24 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 11 December 2018 18:05
Quote:SQL> conn / as sysdba
Connected.
...
SQL> show parameter nls_num
As far as I can see you are still SYS.
I had to switch to SYS in order to reboot the database.
I did the action of parameter change with user A:
SQL> conn a/a
Connected.
SQL>
SQL>
SQL> show parameter nls_num
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters string .,
SQL>
SQL>
SQL> alter system set nls_numeric_characters=',.' scope=spfile;
System altered.
|
|
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673830 is a reply to message #673829] |
Tue, 11 December 2018 10:56 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 11 December 2018 18:29
Quote:I had to switch to SYS in order to reboot the database.
I understand that but you still did not validate that this "does not work", switch then to user A to do "show parameter".
Please see below:
SQL> conn a/a
Connected.
SQL>
SQL>
SQL> show user
USER is "A"
SQL>
SQL>
SQL> show parameter nls_numeric
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters string .,
SQL>
|
|
|
|
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673833 is a reply to message #673831] |
Tue, 11 December 2018 11:01 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 11 December 2018 18:58
Post complete version number.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673834 is a reply to message #673832] |
Tue, 11 December 2018 11:02 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 11 December 2018 18:59
Check environment parameters if nls_numeric_characters is not set.
You mean, like this ?
SQL> select value
2 from nls_session_parameters
3 where parameter = 'NLS_NUMERIC_CHARACTERS';
VALUE
----------------------------------------
.,
|
|
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673836 is a reply to message #673835] |
Tue, 11 December 2018 11:39 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 11 December 2018 19:08
No I meant at OS level(SET, computer environment, registry).
- I searched for "nls_numeric_characters" in the registry - found nothing
- No set explicitly specified nor set up before the sqlplus execution - opened CMD and typed sqlplus...
- Checked environment variables - found nothing related to this param..
Any other suggestions?
|
|
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673838 is a reply to message #673836] |
Tue, 11 December 2018 12:59 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I think I remember seeing once that this only uneatable for the session and not instance. It's like the characterset. You cannot change it for the database. You have to create a new database. "I think"
SCOTT@ORCLSQL> l
1 selecT * from v$system_parameter
2 where NAME like 'nls%'
3* and num=283
NUM NAME TYPE VALUE
---------------- ------------------------------ ---------------- ------------
DISPLAY_VALUE
---------------------------------------------------------------------------------------------------
DEFAULT_VALUE
---------------------------------------------------------------------------------------------------
ISDEFAULT ISSES ISSYS_MOD ISPDB ISINS ISMODIFI ISADJ ISDEP ISBAS DESCRIPT
--------- ----- --------- ----- ----- -------- ----- ----- ----- --------
UPDATE_COMMENT
---------------------------------------------------------------------------------------------------
HASH CON_ID
---------------- ----------------
283 nls_numeric_characters 2
TRUE TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE NLS nume
1350504691 0
ISSYS_MODIFIABLE = FALSE
ISINSTANCE_MODIFIABLE = FALSE
ISMODIFIED = FALSE
ISADJUSTED = FALSE
ISDEPRECATED = FALSE
ISBASIC = FALSE
[Updated on: Tue, 11 December 2018 13:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #673918 is a reply to message #673901] |
Mon, 17 December 2018 15:41 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 16 December 2018 09:59
The documentation states that you can modify the parameter ONLY with ALTER SESSION and not with ALTER SYSTEM.
This is also what Joy's query shows.
So if my database is in Germany - I need to set it for every new session ?
Or am I supposed to change the locale of the OS hosting the database ?
I mean.. how do I change and control the default value ?
|
|
|
|
|
|
| Re: nls_numeric_characters doesn't change after reboot [message #674100 is a reply to message #673922] |
Thu, 03 January 2019 06:08 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 18 December 2018 08:43
All NLS parameters are in the hands of the clients, they are the ones who choose how they want to see the data and the language they use, and the default value depends on their location, if you need to set them at server level then your design is not made for globalization.
That said, you can create a logon trigger to set them and you can most often set them at the statement level.
Thank you and everyone who replied all for your input.
Much appreciated!
I will update if I have any related further questions.
Andrey
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
