NLS NUMERIC CHARACTERS setting for decimal

Question

I have one db setup in a test machine and second in production machine  When I run   select to number  100 12   from dual    Then it gives error in test machine  However  this statement works quite fine in production machine   Now  when I check for NLS NUMERIC CHARACTERS then I see      comma  in both machine  Is there anywhere else I should be looking for the decimal setting   Cheers

User · Answer

Jaanna  the session parameters in Oracle SQL Developer are dependent on your client computer  while the NLS parameters on PL SQL is from server   For example the NLS NUMERIC CHARACTERS on client computer can be      while it s      on server    So when you run script from PL SQL and Oracle SQL Developer the decimal separator can be completely different for the same script  unless you alter session with your expected NLS NUMERIC CHARACTERS in the script    One way to easily test your session parameter is to do   select to number 5 2  from dual

User · Answer

Best way is   SELECT to number replace  Str         100  --into num2  FROM dual

User · Answer

To know SESSION decimal separator  you can use following SQL command   ALTER SESSION SET NLS NUMERIC CHARACTERS          select SUBSTR value 1 1  as  SEPARATOR          using NLS-PARAMETER  as  Explanation    from nls session parameters   where parameter    NLS NUMERIC CHARACTERS   UNION ALL  select SUBSTR 0 5 1 1  as  SEPARATOR           using NUMBER IMPLICIT CASTING  as  Explanation    from DUAL    The first SELECT command find NLS Parameter defined in NLS SESSION PARAMETERS table  The decimal separator is the first character of the returned value   The second SELECT command convert IMPLICITELY the 0 5 rational number into a String using  by default  NLS NUMERIC CHARACTERS defined at session level   The both command return same value   I have already tested the same SQL command in PL SQL script and this is always the same value COMMA or POINT that is displayed  Decimal Separator displayed in PL SQL script is equal to what is displayed in SQL   To test what I say  I have used following SQL commands   ALTER SESSION SET NLS NUMERIC CHARACTERS          select  DECIMAL-SEPARATOR on CLIENT        TO CHAR  5          from dual   DECLARE     S VARCHAR2 10          BEGIN      select  5 INTO S from dual       DBMS OUTPUT PUT LINE  DECIMAL-SEPARATOR in PL SQL        S          END      The shorter command to know decimal separator is   SELECT  5 FROM DUAL    That return 0 5 if decimal separator is a COMMA and 0 5 if decimal separator is a POINT

User · Answer

You can see your current session settings by querying nls session parameters   select value from nls session parameters where parameter    NLS NUMERIC CHARACTERS    VALUE                                   ----------------------------------------                                             That may differ from the database defaults  which you can see in nls database parameters   In this session your query errors   select to number  100 12   from dual   Error report - SQL Error  ORA-01722  invalid number 01722  00000 -   invalid number    I could alter my session  either directly with alter session or by ensuring my client is configured in a way that leads to the setting the string needs  it may be inherited from a operating system or Java locale  for example    alter session set NLS NUMERIC CHARACTERS         select to number  100 12   from dual   TO NUMBER  100 12   -------------------              100 12    In SQL Developer you can set your preferred value in Tool- Preferences- Database- NLS   But I can also override that session setting as part of the query  with the optional third nlsparam parameter to to number    though that makes the optional second fmt parameter necessary as well  so you d need to be able pick a suitable format   alter session set NLS NUMERIC CHARACTERS         select to number  100 12    99999D99    NLS NUMERIC CHARACTERS          from dual   TO NUMBER  100 12   99999D99   NLS NUMERIC CHARACTERS          --------------------------------------------------------------                                                         100 12    By default the result is still displayed with my session settings  so the decimal separator is still a period

[oracle] NLS_NUMERIC_CHARACTERS setting for decimal

Examples related to oracle

Examples related to oracle11g

Examples related to oracle-sqldeveloper