OraFAQ Forum: Client Tools » Issue with SPOOL and decimal datatypes

Home » Other » Client Tools » Issue with SPOOL and decimal datatypes (oracle 10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issue with SPOOL and decimal datatypes [message #589270]

Wed, 03 July 2013 22:57

eshwar401
Messages: 17
Registered: June 2013
Location: India

Junior Member

set term off
set Linesize 32760
set pagesize 0
set echo off
set heading off
set feedback off
set verify off
set colsep '|'
set TRIMSPOOL ON

spool c:\Test.csv
select * from Test;
spool off

This is working fine but only issue is the database collation is in norweign so when i see the generated file the decimal datatype column has 100,20 instead of 100.20. the decimal number are returned with comma.

I am doing it in windows server 2003 on SQLPLUS. I heard there is command to set the NLS_LANG as American and generate the output with decimal place instead of comma.

How to do this?

I have do it in Windows as well as in unix.

Thanks,Eshwar.

Report message to a moderator

Re: Issue with SPOOL and decimal datatypes [message #589272 is a reply to message #589270]

Thu, 04 July 2013 00:04

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

One option is to alter session in a following manner:

SQL> select 625/100 col from dual;

       COL
----------
      6,25

SQL> alter session set nls_numeric_characters = '.,';

Session altered.

SQL> select 625/100 col from dual;

       COL
----------
      6.25

SQL>

Report message to a moderator

Re: Issue with SPOOL and decimal datatypes [message #589274 is a reply to message #589270]

Thu, 04 July 2013 00:05

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

alter session set NLS_NUMERIC_CHARACTERS=',.';

or put the same parameter in your environment.
You don't need to change NLS_LANG.

Regards
Michel

Report message to a moderator

Re: Issue with SPOOL and decimal datatypes [message #589286 is a reply to message #589274]

Thu, 04 July 2013 01:37

eshwar401
Messages: 17
Registered: June 2013
Location: India

Junior Member

Thanks that's a good solution and it is working like a charm... Smile

Report message to a moderator

Re: Issue with SPOOL and decimal datatypes [message #589381 is a reply to message #589286]

Fri, 05 July 2013 01:02

eshwar401
Messages: 17
Registered: June 2013
Location: India

Junior Member

I have some issue the output for large values are generated like 1.0000E+15 instead of whole value.

How to resolve is there any setting which does this?

Report message to a moderator