NLS_LIST_SEPARATOR
January 10th, 2006 By Laurent Schneider
when is this parameter used?
8 Responses to “NLS_LIST_SEPARATOR”
Leave a Reply
You must be logged in to post a comment.
OraQA
Oracle Question and Answer
- Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...
Oracle News
Comments RSS feed
January 11th, 2006 at 2:01 pm
Francois Degrelle answered this question here:
NLS_LIST_SEPARATOR specifies the character to use to separate values in
a list of values (usually , or . or ; or :). Its default value is
derived from the value of NLS_TERRITORY. For example, a list of numbers
from 1 to 5 can be expressed as 1,2,3,4,5 or 1.2.3.4.5 or 1;2;3;4;5 or
1:2:3:4:5.
The character specified must be single-byte and cannot be the same as
either the numeric or monetary decimal character, any numeric
character, or any of the following characters: plus (+), hyphen (-),
less than sign, greater than sign, period (.).
January 11th, 2006 at 11:06 pm
well, this I can read in the doc. Anyone providing an example?
PS: apologize for multiposting…
January 12th, 2006 at 12:40 am
Laurent,
Try to change the value with ALTER SESSION SET NLS_LIST_SEPARATOR=…, then use this new separator in an instruction like GREATEST for example…
January 12th, 2006 at 3:03 am
of course not !
NLS_LIST_SEPARATOR can be used only as an environment variable (like NLS_LANG) and a function accepting a variable number of arguments is not a list
SCOTT@LSC01> alter session set nls_list_separator=’.';
alter session set nls_list_separator=’.’
*
ERROR at line 1:
ORA-00922: missing or invalid option
f(a,b,c) –> a,b,c is not a “LIST”, the comma is a SQL syntax element and can never be changed!
similary to_number(’2,4′) may work, but to_char(2,4) will definitely not work.
NLS_LIST_SEPARATOR may affect the output of a string, but I wonder in which circumstances
January 12th, 2006 at 7:59 am
Well, I am not able to find anything, even on Metalink !
January 13th, 2006 at 12:43 am
it is even worst, the info on metalink is simply wrong
Note 251044.1
The parameters which can be set like this, are all the NLS session parameters:
…
NLS_LIST_SEPARATOR
!!!
January 17th, 2006 at 2:19 am
Given where this crops up in the documentation – iAS Reports – I think this is a parameter that’s only relevant (and hence changeable) in an app server install. Not having access to such a beast I am unable to confirm this.
Cheers, APC
January 17th, 2006 at 6:28 am
well, I saw that, but it is a Oracle 7.2 new feature, way before ias…