Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    833

    Unanswered: stange behavior numerics to varchar and nls parameter

    hi,

    my oracle version is 8.1.7.2 on sun solaris 5.8

    I get char fields as of
    TMPVAR1 2,5000000 TMPVAR2 0 TMPVAR3 3,2500000 5,75

    converting the tmpvars to_number and sum them up
    now I want to_char the sum again, but it cuts off the decimal digits or rounds up. If I set the format mask to '999.99999999' it works fine --

    I played around with the NLS_NUMERIC_CHARACTERS parameter but then the tmpvar constents cannot be transformed to a numeric ...

    how to set up the enironment to get transormed tmpvars correctly and the summed output converted into the format '999,99999999'

    thanks

    osy45
    my nls params are set as follows:
    SQL>
    SQL> select * from nls_session_parameters;

    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE GERMAN
    NLS_TERRITORY GERMANY
    NLS_CURRENCY
    NLS_ISO_CURRENCY GERMANY
    NLS_NUMERIC_CHARACTERS ,.
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD.MM.RR
    NLS_DATE_LANGUAGE GERMAN
    NLS_SORT GERMAN
    NLS_TIME_FORMAT HH24:MISXFF
    NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MISXFF

    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    NLS_TIME_TZ_FORMAT HH24:MISXFF TZH:TZM
    NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MISXFF TZH:TZM
    NLS_DUAL_CURRENCY +
    NL

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: stange behavior numerics to varchar and nls parameter

    Perhaps you should post your code - I can't see where your problem comes from:

    SQL> DECLARE
    2 TMPVAR1 VARCHAR2(20) := '2.5000000';
    3 TMPVAR2 VARCHAR2(20) := '0';
    4 TMPVAR3 VARCHAR2(20) := '3.2500000';
    5 TMPVAR4 VARCHAR2(20) := '5.75';
    6 n NUMBER;
    7 BEGIN
    8 n := tmpvar1 + tmpvar2 + tmpvar3 + tmpvar4;
    9 c := TO_CHAR(n);
    10 dbms_output.put_line(n);
    11 dbms_output.put_line(c);
    12* END;
    13 /
    11.5
    11.5

    PL/SQL procedure successfully completed.

  3. #3
    Join Date
    Dec 2003
    Location
    Frankfurt germany
    Posts
    6
    Hi,

    I beleive you get the numbers by a query ? How ever you should use use the format-expression NLS_NUMERIC_CHARACTERS as well in accessing the individual numbers.

    Doing so, you still work in your nls-environment, only the individaul numbers will be converted and the result (your sum) is a number in your nls-format.

    SELECT TO_NUMBER('2.500','9D99',' NLS_NUMERIC_CHARACTERS = ''.,') FROM DUAL;

    TO_NUMBER('2.50','9D99','NLS_NUMERIC_CHARACTERS='' .,')
    ------------------------------------------------------
    2,5

    1 Zeile wurde ausgewählt.

    now lay back and enjoy the christmas cake ! frohe Weihnachten

    Lao De

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •