title: How to Spool UTF-8 format data in Oracle database into text file
body: <p>How to spool, in UTF-8 format, data from an Oracle database into text file with all UTF-8 chars coming out properly, for example Chinese characters. </p>
  
  <p>I am trying to spool data from an Oracle database which is UTF-8 enabled and trying to spool the same data into txt or cvs. Instead of the Chinese characters I am getting <code>????</code>. </p>
  
  <p>I know that this question has been asked earlier but there is no answer given for that, hence reopening that question again.</p>
  
  <p>Batch program:</p>
  
  <pre><code>sqlplus snprn/SpotProd_07@SPOTDEV_VM.WORLD @C:\BatchJob\SPOTReport\spotreport.SQL
  
  rem sqlplus snprn/SpotProd_07@lprodspot @C:\BatchJob\SPOTReport\spotreportrecovery.SQL
  
  rem copy Spot_Item_details*.* C:\BatchJob\SPOTReport /y
  copy Spot_Item_details*.* C:\BatchJob\SPOTReport /y
  rem xcopy Spot_Item_details*.* backup /y
  rem del Spot_Item_details*.*    
  </code></pre>
  
  <p>SQL Code:</p>
  
  <pre><code>SET HEADING ON
  SET FEEDBACK    OFF
  SET ECHO    OFF
  SET LINESIZE    5000
  SET PAGESIZE    0
  SET TRIMS   ON
  SET ARRAYSIZE   5
  
  
  COLUMN extract_date NEW_VALUE _date
  SELECT TO_CHAR(SYSDATE, 'RRRRMMDD') extract_date 
  FROM   DUAL;
  
  SPOOL D:\SPOT2\BatchJob\SPOTReport\Spot_Item_details_daily_&amp;_Date.txt
  
  Select 'SPOT#'||'^'||
  'STATUS'||'^'||
  'APPLY DATE'||'^'||
  'MANAGER SIGNOFF'||'^'||
  'SNP OPS SIGNOFF'||'^'||
  'GP SIGNOFF'||'^'||
  'DIR SIGNOFF'||'^'||
  'SCM SIGNOFF'||'^'||
  'ITEM NO'||'^'||
  'ISMARTS SKU'||'^'||
  'MANUFACTURER SKU'||'^'||
  'COUNTRY'||'^'||
  'DISTRIBUTOR'||'^'||
  'DISTRIBUTOR STD PRICE EX GST'||'^'||
  'DISTRIBUTOR FINAL PRICE EX GST'||'^'||
  'DELL PRICE EX GST'||'^'||
  'QTY REQUIRED'||'^'||
  'CURRENCY'||'^'||
  'LICENSE PACKAGE'||'^'||
  'MSLICENSE'||'^'||
  'MSSOFTWARE'
  From Dual;
  
  Select distinct SSR_REFNO||'^'||
  SSR_STATUS||'^'||
  SSR_APPLY_DATE||'^'||
  TO_CHAR(SSR_MAN_DATE, 'DDMONRRRR HH24:MI:SS')||'^'||
  TO_CHAR(SSR_ORT_DATE, 'DDMONRRRR HH24:MI:SS')||'^'||
  TO_CHAR(SSR_GP_DATE, 'DDMONRRRR HH24:MI:SS')||'^'||
  TO_CHAR(SSR_DIR_DATE, 'DDMONRRRR HH24:MI:SS')||'^'||
  TO_CHAR(SSR_SCM_DATE, 'DDMONRRRR HH24:MI:SS')||'^'||
  REPLACE(SSR_ITEM_NO, chr(10), '')||'^'||
  REPLACE(SSR_ISMARTS_SKU, chr(10), '')||'^'||
  REPLACE(SSR_MANUFACTURER_SKU, chr(10), '')||'^'||
  REPLACE(SSR_COUNTRY, chr(10), '')||'^'||
  REPLACE(SSR_DISTRIBUTOR, chr(10), '')||'^'||
  REPLACE(SSR_MANF_STD_COST_EX_GST, chr(10), '')||'^'||
  REPLACE(SSR_MANF_COST_EX_GST, chr(10), '')||'^'||
  REPLACE(SSR_DELL_PRICE_EX_GST, chr(10), '')||'^'||
  REPLACE(SSR_QTY_REQUIRED, chr(10), '')||'^'||
  REPLACE(SSR_CURRENCY, chr(10), '')||'^'||
  REPLACE(SSR_LICENSE_PACKAGE, chr(10), '')||'^'||
  REPLACE(SSR_MSLICENSE, chr(10), '')||'^'||
  REPLACE(SSR_MSSOFTWARE, chr(10), '')
  From SPOT_SNP_REPORt
  Where SSR_REFNO like 'FSPOT-%' and SSR_ITEM_NO &lt;100000;
  
  SPOOL OFF
  exit;
  </code></pre>