Home » SQL & PL/SQL » Client Tools » Issue with SPOOL and decimal datatypes (oracle 10.2)
Issue with SPOOL and decimal datatypes [message #589270] Wed, 03 July 2013 22:57 Go to next message
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.
Re: Issue with SPOOL and decimal datatypes [message #589272 is a reply to message #589270] Thu, 04 July 2013 00:04 Go to previous messageGo to next message
Littlefoot
Messages: 19508
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>
Re: Issue with SPOOL and decimal datatypes [message #589274 is a reply to message #589270] Thu, 04 July 2013 00:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58909
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Issue with SPOOL and decimal datatypes [message #589286 is a reply to message #589274] Thu, 04 July 2013 01:37 Go to previous messageGo to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
Thanks that's a good solution and it is working like a charm...Smile
Re: Issue with SPOOL and decimal datatypes [message #589381 is a reply to message #589286] Fri, 05 July 2013 01:02 Go to previous messageGo to next message
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?
Re: Issue with SPOOL and decimal datatypes [message #589382 is a reply to message #589381] Fri, 05 July 2013 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58909
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set numwidth 20

Regards
Michel
Re: Issue with SPOOL and decimal datatypes [message #589383 is a reply to message #589382] Fri, 05 July 2013 01:53 Go to previous messageGo to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
Working like charm Your Awesome!!

Thanks,Eshwar.
Re: Issue with SPOOL and decimal datatypes [message #589385 is a reply to message #589383] Fri, 05 July 2013 02:38 Go to previous message
Michel Cadot
Messages: 58909
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not really I just read the documentation and make many exercises... Smile
SQL*PlusĀ® User's Guide and Reference

Regards
Michel
Previous Topic: TOAD Not Finding Oracle 11g Client
Next Topic: PlSql Development IDE
Goto Forum:
  


Current Time: Wed Aug 27 12:56:15 CDT 2014

Total time taken to generate the page: 0.11275 seconds