Home » RDBMS Server » Server Administration » nls_numeric_characters doesn't change after reboot (Oracle 11g , windows8)
nls_numeric_characters doesn't change after reboot [message #673723] Thu, 06 December 2018 13:30 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #673827 is a reply to message #673826] Tue, 11 December 2018 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
SQL> conn / as sysdba
Connected.
...
SQL> show parameter nls_num
As far as I can see you are still SYS.

Re: nls_numeric_characters doesn't change after reboot [message #673828 is a reply to message #673827] Tue, 11 December 2018 10:24 Go to previous messageGo to next message
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 #673829 is a reply to message #673828] Tue, 11 December 2018 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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".

Re: nls_numeric_characters doesn't change after reboot [message #673830 is a reply to message #673829] Tue, 11 December 2018 10:56 Go to previous messageGo to next message
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 #673831 is a reply to message #673830] Tue, 11 December 2018 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post complete version number.

Re: nls_numeric_characters doesn't change after reboot [message #673832 is a reply to message #673831] Tue, 11 December 2018 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Check environment parameters if nls_numeric_characters is not set.

Re: nls_numeric_characters doesn't change after reboot [message #673833 is a reply to message #673831] Tue, 11 December 2018 11:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #673835 is a reply to message #673834] Tue, 11 December 2018 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No I meant at OS level(SET, computer environment, registry).

Re: nls_numeric_characters doesn't change after reboot [message #673836 is a reply to message #673835] Tue, 11 December 2018 11:39 Go to previous messageGo to next message
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 #673837 is a reply to message #673836] Tue, 11 December 2018 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

v$parameter (and show parameter) gives the session parameters.
v$system_parameter gives the instance (or system) parameters.
Check this later one.
Also check nls_database_parameters (nls_instance_parameters is the same as v$system_parameter).

(I assume you altered the parameter, restarted and checked in the same instance if you are in RAC.)

Re: nls_numeric_characters doesn't change after reboot [message #673838 is a reply to message #673836] Tue, 11 December 2018 12:59 Go to previous messageGo to next message
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 #673839 is a reply to message #673838] Tue, 11 December 2018 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed, we should first read the documentation. Laughing

Re: nls_numeric_characters doesn't change after reboot [message #673849 is a reply to message #673839] Wed, 12 December 2018 07:39 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I am always happy when I remember something. It reassures me that I am not "losing it." I fear that day is coming sooner rather than later.
However, I don't remember WHERE or WHENit was that I remember it from.
Re: nls_numeric_characters doesn't change after reboot [message #673897 is a reply to message #673839] Sat, 15 December 2018 15:23 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Tue, 11 December 2018 21:12

Indeed, we should first read the documentation. Laughing


Sorry, but after (re)reading this bit of documentation I still don't understand what am I doing wrong, and why my logic of expecting to be able to change it on a SPFILE level ( so that it will be by default propagated to each new session opened) doesn't make sense... Can anyone explain it to me ?
Re: nls_numeric_characters doesn't change after reboot [message #673901 is a reply to message #673897] Sun, 16 December 2018 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

Re: nls_numeric_characters doesn't change after reboot [message #673918 is a reply to message #673901] Mon, 17 December 2018 15:41 Go to previous messageGo to next message
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 #673922 is a reply to message #673918] Tue, 18 December 2018 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

Re: nls_numeric_characters doesn't change after reboot [message #674100 is a reply to message #673922] Thu, 03 January 2019 06:08 Go to previous message
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
Previous Topic: Table Partitioning
Next Topic: Service Name
Goto Forum:
  


Current Time: Thu Mar 28 15:07:32 CDT 2024