How to sync UTF-8 data in an Oracle database to a text file

Question

How to bind in UTF-8 format data from an Oracle database to a text file with all the correct UTF-8 characters, for example, Chinese characters.

I am trying to confuse data from an Oracle database, which is included in UTF-8, and trying to confuse the same data in txt or cvs. Instead of Chinese characters, I get ???? .

I know that this question was asked earlier, but there is no answer to it, so this question is resumed 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 (e.g. sqlplus) connects to a database, it tells the database which character set it uses. In some environments, there may be a very limited character set and use something like US7ASCII so that they don't get anything that could upset them.

As you can see in the following example, what is displayed by the request depends on the NLS_LANG client setting.

 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, try the above. If it's a unix (ish) platform, try

 export NLS_LANG=.AL32UTF8 

Answer 2

Maybe you get ???? because your text editor doesn’t know what the text file is UTF-8?

The text file does not contain information about how it is encoded. Youo should tell your text editor that it should interpret the data as UTF-8 and make sure that it uses a font containing Chinese characters (e.g. DejaVu fonts)

Answer 3

Are these characters displayed correctly in the sql * plus output window?

Perhaps you should change the character encoding of sql * plus? The SQLPLUS_FONT_CHARSET registry SQLPLUS_FONT_CHARSET in HKLM/Software/Oracle/Home0 indicates this.

Answer 4

Look at the current configuration, for example:

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

And include the same statement at the beginning. You can also simplify the configuration of the spool script by defining the same things at the beginning of the script, thereby avoiding formatting for each output column in the case of dates:

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

Also, an easy way to create headers uses "prompt" instead of select ... from dual :

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

just write the title as you want.