Oracle varchar2 Chinese characters

最新推荐文章于 2021-04-06 22:25:27 发布

原创最新推荐文章于 2021-04-06 22:25:27 发布 · 773 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍如何在Oracle数据库的VARCHAR2类型字段中插入中文字符。首先检查数据库字符集是否支持中文，然后设置NLS_LANG环境变量，并演示了两种插入中文的方法。

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