Oracle varchar2 Chinese characters

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,Advanced Analytics and Real Application Testing options

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 你好

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!