How to Spool UTF-8 format data in Oracle database into text file

Question

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.

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 ????.

I know that this question has been asked earlier but there is no answer given for that, hence reopening that question again.

Batch program:

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*.*    

SQL 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_&_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 <100000;

SPOOL OFF
exit;

Answer 1

Whenever a client program (such as sqlplus) connects to the database, it tells the database what characterset it is using. Some environments may have a very restricted characterset and use something like US7ASCII so they don't get anything that can upset them.

As you can see in the following example, what is output by a query is dependent on the NLS_LANG setting of a client.

C:\>set NLS_LANG=.US7ASCII
C:\>sqlplus ???/???@xe

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 3 09:31:32 2010
> select chr(193) from dual;

C
-
?

> quit

C:\>set NLS_LANG=.AL32UTF8
C:\>sqlplus ???/???@xe

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 3 09:31:49 2010
> select chr(193) from dual;

C
-
┴

If your client is Windows, then try the above. If it is a unix(ish) platform, try

export NLS_LANG=.AL32UTF8

Answer 2

Maybe you are getting ???? because your text-editor does not know that the textfile is UTF-8?

A textfile does not contain information on how it is encoded. Youo should instruct your text-editor that it should interpret the data as UTF-8 and ensure that it is using a font that contains chinese characters (such as the DejaVu fonts)

Answer 3

Look at you current configuration like:

   % set | grep NLS
   NLS_DATE_FORMAT=YYYY-MM-DD-HH24:MI
   NLS_LANG=AMERICAN_AMERICA.AL32UTF8

And include the same statement at the start. You can also simplfy your spool script defining the same things at the start of the script, thus avoiding formatting for every column in the output in case of dates:

   alter session set NLS_LANG='.AL32UTF8'
   alter session set nls_date_format='DDMONRRRR HH24:MI:SS'

Also a simple way a putting headers, is using "prompt" instead of select ... from dual:

   prompt SPOT#^STATUS^APPLY DATE^MANAGER SIGNOFF^....^MSSOFTWARE

just write down the header as you want.

Answer 4

Do this characters appear properly in sql*plus output window?

Maybe you should change sql*plus character encoding? Registry parameter SQLPLUS_FONT_CHARSET at HKLM/Software/Oracle/Home0 denotes this.