How to avoid getting commas(,) for decimal values in sqlplus?

Question

We are using sqlplus to read data from oracle and write into csv, but in the report we observed that the decimal values are separated by comma(,) instead of point(.). we have built a query like below :

select
        DATE_MONTH||
    '|'||A||
    '|'||B||
    '|'||C||---decimal column 
    '|'||D||---decimal column
    '|'||E||
    '|'||F||
    '|'||G||---decimal column
    '|'||H||---decimal column
    '|'||I||---decimal column
    '|'||J||
    '|'||K||
    '|'||H||
    '|'||'"'||REPLACE(L, '"', '\"' )|| '"'||
    '|'||M||
    '|'||'"' || REPLACE(N, '"', '\"' )|| '"'||
    '|' ||O||
    '|'||TO_DATE(sysdate, 'YYYY-MM-DD')||
    '|'||INFO_IS_DELETED||
    '|'||'"' || REPLACE(P, '"', '\"' )|| '"'
     from xxxxxxx where ROWNUM<=1000;

Please have also a look at the CSV feature in SQLDeveloper and it's command line version SQLcl: https://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer — wolφi, Jun 21 '18 at 06:45

score 0 · Answer 1 · answered Jun 21 '18 at 06:27

0

You can alter nls settings in sqlplus.

alter session set NLS_NUMERIC_CHARACTERS = '. ';

This will set decimal separator to point.

answered Jun 21 '18 at 06:27

Maxim Borunov

901
5
9

How to avoid getting commas(,) for decimal values in sqlplus?

1 Answers1