This doc describe how to insert a Chinese character into Oracle varchar2 datatype column
1. Check character set is correct in database
$ sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 22 19:31:42 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from V$NLS_PARAMETERS where PARAMETER in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');
PARAMETER
----------------------------------------------------------------
VALUE CON_ID
---------------------------------------------------------------- ----------
NLS_LANGUAGE
AMERICAN 0
NLS_TERRITORY
AMERICA 0
NLS_CHARACTERSET
AL32UTF8 0
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
2. Set NLS_LANG value, which is in the format [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
3. Insert data
$ sqlplus scott/tiger@orcl
SQL> CREATE TABLE TESTTAB(A DECIMAL(2, 0), C VARCHAR2(20));
Table created.
SQL> INSERT INTO(A, C) TESTTAB VALUES(1, '你好');
1 row created.
SQL> SELECT * FROM TESTTAB;
A C
---------- --------------------
1 你好
SQL> SELECT RAWTOHEX(C) FROM TESTTAB;
RAWTOHEX(C)
----------------------------------------
E4BDA0E5A5BD
SQL> INSERT INTO TESTTAB(A, C) VALUES(2, UTL_RAW.CAST_TO_VARCHAR2 (HEXTORAW('E4BDA0E5A5BD')));
1 row created.
SQL> SELECT * FROM TESTTAB;
SQL> SELECT * FROM TESTTAB;
A C
---------- --------------------
1 你好
2 你好