How to insert Chinese into Oracle DB on Linux?

 

OS: Oracle Enterprise Linux 5.5

DB: Oracle 11g

 

There are some key points you need to be aware of:

1. When you install the OS, make sure you choose “Chinese Installation“, then it will automatically installed the Chinese character set for you.

2. When you create the DB, make sure you choose the right character set “Use Unicode (AL32UTF8)”.

 

So, after you get everything up and running, follow the procedures below:

1. Be sure you start your OS log-in session as Chinese, so you can input Chinese texts;

2. After log-in, open a terminal, connect to sql*Plus, run the following commands to change the session language settings. 

SQL> ALTER SESSION SET NLS_LANGUAGE=’TRADITIONAL CHINESE’;

???????.

SQL> ALTER SESSION SET NLS_TERRITORY=’CHINA’;

???????.

SQL> SELECT * FROM V$NLS_PARAMETERS;

PARAMETER
—————————————————————-
VALUE
—————————————————————-
NLS_LANGUAGE
TRADITIONAL CHINESE

NLS_TERRITORY
CHINA

NLS_CHARACTERSET
AL32UTF8

SQL> exit

The reason why it shows ???? is because your OS session is not ready to see DB server returning yet. Don’t worry!

3. Setup the environment for the next session as:

[oracle@gemini-oel ~]$ export NLS_LANG=”TRADITIONAL CHINESE_CHINA.AL32UTF8″
I have tried adding at the log-in script (.bash_profile), however I kept getting this error while starting sql*Plus…

[root@geminioel ~]# sqlplus olap_user1/olap_pwd

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 18 14:50:12 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

4. Connect to SQL*Plus again, now your interface will be in Chinese.

[oracle@gemini-oel ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 25 10:45:11 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> connect olap_user1/olap_pwd

已連線.

SQL> describe Dim_Name;
名稱                                     空值?  類型
—————————————– ——– —————————-
ID                                        NOT NULL NUMBER(5)
LAST_NAME                                 NOT NULL VARCHAR2(25)
FIRST_NAME                                NOT NULL VARCHAR2(25)
C_LAST_NAME                                        NVARCHAR2(12)
C_FIRST_NAME                                       NVARCHAR2(12)

SQL> INSERT INTO Dim_Name VALUES (3, ‘Chan’, ‘David’, ‘陳’, ‘大衛’);

已建立 1 個資料列.

SQL> SELECT * FROM Dim_Name;

ID LAST_NAME                 FIRST_NAME
———- ————————- ————————-
C_LAST_NAME
————————————————
C_FIRST_NAME
————————————————
3 Chan                      David

大衛
It’s all working fine now!!!

Actally Oracle comes with a little utility to check an ESCII file for it’s locale.

[oracle@gemini-oel ~]$ more chinese.txt
你好嗎?
呢個你中文text
[oracle@gemini-oel ~]$ lcsscan file=chinese.txt

Language and Character Set File Scanner v2.1

(c) Copyright 2003, 2004 Oracle Corporation.  All rights reserved.

chinese.txt:    TRADITIONAL CHINESE  UTF8;

Advertisements

2 responses to “How to insert Chinese into Oracle DB on Linux?

  1. Thanks a ton….This command really saved me from a launch I had export NLS_LANG=”TRADITIONAL CHINESE_CHINA.AL32UTF8″

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s