Oracle Spool unable to display special characters

Question

I am generateing a txt file using the below code. The code works fine, but there are issues with some characters. I am using Oracle. Characters such as é, á etc. are unable to be displayed.

set echo off
set verify off
set termout on
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 1000
set serveroutput off
set pagesize 0
set trimout on
set trimspool on


spool Weekly_Members_Vendors.txt

select   ''||Unique_ID||''||Name||''||Alt_Name||''|| Entity_Type||''||Party_Type||''||Reference_#||''||Addr1||''||Addr2||''||
     Addr3||''||Town||''||County||''||Postcode||''||Country||''||ALT_ADDRESS1||''||ALT_ADDRESS2||''||
     ALT_ADDRESS3||''||ALT_TOWN||''||ALT_COUNTY||''||ALT_POST_CODE||''||ALT_COUNTRY||''||NATIONALITY||''||
     DOB||''||INDIVIDUAL_ID||''||INDIVIDUAL_ID_TYPE||''||COUNTRY_OF_REGISTRATION||''||COMPANY_ID||''||COMPANY_ID_TYPE||''||
     SOURCE_COUNTRY||''||SOURCE_SYSTEM||''||TRANSACTION_TYPE
from     dbo.Temp_Weekly_Export_File;



spool off;

exit

score 2 · Accepted Answer · edited May 23 '17 at 11:45

I assume you run this in SQL*Plus on Windows.

In this case, check code page of command line windows by chcp

Usually it should be CP437 or CP850 (depending if you are in Europe or US).

Then before you start SQL*Plus set your NLS_LANG value accordingly, either as Environment Variable (e.g. set NLS_LANG=WE8PC850)

or

in Registry (HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG for 32-bit, resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG for 64-bit)

You can also change the code page of command line window, e.g. chcp 1252

Oracle Spool unable to display special characters

1 Answers1