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

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.

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