如何将 Oracle 数据库中的 UTF-8 格式数据假脱机为文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4076439/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Spool UTF-8 format data in Oracle database into text file
提问by shaleen
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.
如何以 UTF-8 格式将 Oracle 数据库中的数据假脱机到文本文件中，并正确显示所有 UTF-8 字符，例如中文字符。
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 ????.
我正在尝试从启用了 UTF-8 的 Oracle 数据库中假脱机数据，并尝试将相同的数据假脱机到 txt 或 cvs 中。而不是我得到的汉字????。
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:
SQL 代码：
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;
回答by Gary Myers
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.
每当客户端程序（例如 sqlplus）连接到数据库时，它都会告诉数据库它正在使用什么字符集。某些环境可能具有非常有限的字符集并使用 US7ASCII 之类的东西，因此他们不会得到任何可能使他们感到不安的东西。
As you can see in the following example, what is output by a query is dependent on the NLS_LANG setting of a client.
正如您在以下示例中看到的，查询输出的内容取决于客户端的 NLS_LANG 设置。
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
如果您的客户端是 Windows，请尝试以上操作。如果它是 unix(ish) 平台，请尝试
export NLS_LANG=.AL32UTF8
回答by Tomas
Maybe you are getting ???? because your text-editor does not know that the textfile is UTF-8?
也许你得到了？？？？因为您的文本编辑器不知道该文本文件是 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)
文本文件不包含有关其编码方式的信息。您应该指示您的文本编辑器将数据解释为 UTF-8，并确保它使用包含中文字符的字体（例如 DejaVu 字体）
回答by Victor H
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:
还有一种简单的放置标题的方法是使用“提示”而不是select ... from dual：
prompt SPOT#^STATUS^APPLY DATE^MANAGER SIGNOFF^....^MSSOFTWARE
just write down the header as you want.
只需根据需要写下标题。
回答by andr
Do this characters appear properly in sql*plus output window?
这些字符在 sql*plus 输出窗口中是否正确显示？
Maybe you should change sql*plus character encoding? Registry parameter SQLPLUS_FONT_CHARSETat HKLM/Software/Oracle/Home0denotes this.
也许您应该更改 sql*plus 字符编码？注册表参数SQLPLUS_FONT_CHARSETatHKLM/Software/Oracle/Home0表示这一点。
