Oracle nvarchar2存储特殊字符乱码问题

这个问题研究了一天多，终于搞定了。

起因是业务需要存特殊字符'ø'到varchar2的字段中出现乱码，因为数据库字符集是ZHS16GBK。

简单测试了下，像'ø'之类的特殊。由于国家字符集是AL16UTF16，准备用nvarchar2（nvarchar2用的是国家字符集）存储特殊字符。

但是测试环境测试结果是就算用nvarchar2存，还是有乱码的情况。

重现如下：

[oracle@zkm ~]$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
[oracle@zkm ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

11:22:28 SYS@zkm(451)> select userenv('language') from dual;

USERENV('LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

Elapsed: 00:00:00.01
11:22:06 SYS@zkm(451)> create table zkm ( name1 varchar2(20),name2 nvarchar2(20));

Table created.

Elapsed: 00:00:01.39

11:30:12 SYS@zkm(451)> select * from NLS_DATABASE_PARAMETERS;

PARAMETER                                          VALUE
-------------------------------------------------- --------------------------------------------------
NLS_LANGUAGE                                       AMERICAN
NLS_TERRITORY                                      AMERICA
NLS_CURRENCY                                       $
NLS_ISO_CURRENCY                                   AMERICA
NLS_NUMERIC_CHARACTERS                             .,
NLS_CHARACTERSET                                   ZHS16GBK
NLS_CALENDAR                                       GREGORIAN
NLS_DATE_FORMAT                                    DD-MON-RR
NLS_DATE_LANGUAGE                                  AMERICAN
NLS_SORT                                           BINARY
NLS_TIME_FORMAT                                    HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                               DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                 HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                            DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                  $
NLS_COMP                                           BINARY
NLS_LENGTH_SEMANTICS                               BYTE
NLS_NCHAR_CONV_EXCP                                FALSE
NLS_NCHAR_CHARACTERSET                             AL16UTF16
NLS_RDBMS_VERSION                                  11.2.0.4.0

20 rows selected.

Elapsed: 00:00:00.00
11:31:18 SYS@zkm(451)> insert into zkm values ('ø','ø');

1 row created.

Elapsed: 00:00:00.00
11:31:21 SYS@zkm(451)> commit;

Commit complete.

Elapsed: 00:00:00.00
11:31:26 SYS@zkm(451)> select * from zkm;

NAME1      NAME2
---------- ----------
？         ？           （这里是中文问号）

Elapsed: 00:00:00.00

比对中文问号和英文问号：

14:19:01 SYS@zkm(451)> select dump('?',1016) from dual union all select dump('？',1016) from dual union all select dump('ø',1016) from dual;

DUMP('?',1016)
---------------------------------------------------------------------------------------------------------------------------
Typ=96 Len=1 CharacterSet=ZHS16GBK: 3f
Typ=96 Len=2 CharacterSet=ZHS16GBK: a3,bf
Typ=96 Len=2 CharacterSet=ZHS16GBK: a3,bf

Elapsed: 00:00:00.01

也就是说，在ZHS16GBK下，中文"？"和"ø"最后成了一样的效果，也就是"ø"乱码了。

这个很好理解，Linux OS将UTF8类型的"ø"通过sqlplus这个客户端送进去数据库中，然后数据库通过NLS_LANG环境变量了解到进来的"ø"是UTF8编码的，于是通过比对UTF8编码表和GBK编码表对应的"ø"，将UTF8编码的"ø"转换成GBK的"ø"。

由于GBK不支持特殊字符"ø"，在GBK编码表中不存在对应的编码，于是使用GBK编码表中的中文"？"编码替代，通过上边的dump可知为a3bf，这个就是乱码的原因。

我们可以dump表zkm的name1，由于name2字段为nvarchar类型，该类型不使用数据库字符集ZHS16GBK，而是使用国家字符集AL16UTF16，因此有如下结果：

14:38:24 SYS@zkm(87)> select dump(name1,1016) name1,dump(name2,1016) name2 from zkm;

NAME1                                              NAME2
-------------------------------------------------- --------------------------------------------------
Typ=1 Len=2 CharacterSet=ZHS16GBK: a3,bf           Typ=1 Len=2 CharacterSet=AL16UTF16: ff,1f

Elapsed: 00:00:00.00

可以知道，将"ø"insert进去表的name1，name2字段，确实变成了中文的"？" ，都是'a3bf'。

不过问题是nvarchar类型用的是AL16UTF16,为啥存不了"ø"？？

网上找资料才发现，要通过加N告诉这个特殊字符是Unicode字符才行。

用法参考：

http://www.orafaq.com/wiki/NVARCHAR2

http://www.orafaq.com/wiki/NCHAR

于是：

15:35:30 SYS@zkm(1398)> select dump(N'?',1016) from dual union all select dump(N'？',1016) from dual union all select dump(N'ø',1016) from dual;

DUMP(N'?',1016)
--------------------------------------------------------------------------------
Typ=96 Len=2 CharacterSet=AL16UTF16: 0,3f
Typ=96 Len=2 CharacterSet=AL16UTF16: ff,1f
Typ=96 Len=2 CharacterSet=AL16UTF16: ff,1f

Elapsed: 00:00:00.00
15:35:39 SYS@zkm(1398)> delete zkm;

1 row deleted.

Elapsed: 00:00:00.01
15:35:44 SYS@zkm(1398)> insert into zkm values ('ø',N'ø');

1 row created.

Elapsed: 00:00:00.00
15:36:05 SYS@zkm(1398)> commit;

Commit complete.

Elapsed: 00:00:00.00
15:36:44 SYS@zkm(1398)> select * from zkm; 

NAME1      NAME2
---------- ----------
？         ？

Elapsed: 00:00:00.00

加了N后还是发现，AL16UTF16下，中文"？"和"ø"还是一样的。

难道AL16UTF16不支持字符"ø"？？不可能啊。

于是在新建了一个字符集为AL32UTF8的库，做上边同样建表的操作，然后插入数据，无论是varchar或者nvarchar都不会乱码啊，部分如下操作：

SQL> select dump(N'?',1016) from dual union all select dump(N'？',1016) from dual union all select dump(N'ø',1016) from dual;

DUMP(N'?',1016)
------------------------------------------
Typ=96 Len=2 CharacterSet=AL16UTF16: 0,3f
Typ=96 Len=2 CharacterSet=AL16UTF16: ff,1f
Typ=96 Len=2 CharacterSet=AL16UTF16: 0,f8

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL> select dump(N'?',1016) from dual union all select dump(N'？',1016) from dual union all select dump(N'ø',1016) from dual;

DUMP(N'?',1016)
------------------------------------------
Typ=96 Len=2 CharacterSet=AL16UTF16: 0,3f
Typ=96 Len=2 CharacterSet=AL16UTF16: ff,1f
Typ=96 Len=2 CharacterSet=AL16UTF16: 0,f8

SQL> select unistr('\00F8') from dual;

UNIS
----
ø

从这个实验基本能够确定，Oracle将特殊字符"ø"存入数据库的时候，是先通过存为数据库字符集ZHS16GBK，然后再通过ZHS16GBK转为国家字符集AL16UTF16。

由于转ZHS16GBK的时候已经乱码，再次转AL16UTF16的时候就有问题了。

新库已经证明了国家字符集可以存该特殊字符了。

那么到底如何能够将特殊字符存进去nvarchar而不乱码，百度找不到于是只能MOS搜了，果然找到了..
实际上，环境变量ORA_NCHAR_LITERAL_REPLACE默认值为false，该变量表示任何从客户端传过来的NCHAR类型的字符先转换为数据库字符集，再转换为国家数据库字符集，而把该参数设为true后，从客户端传过来的NCHAR类型的字符直接转换为国家字符集存储，因此要想正确存储NCHAR字符集，最好得把该参数设为TRUE。

[oracle@zkm ~]$ export ORA_NCHAR_LITERAL_REPLACE=true
15:37:07 SYS@zkm(99)> delete from zkm;

1 row deleted.

Elapsed: 00:00:00.00
15:37:14 SYS@zkm(99)> insert into zkm values ('ø',N'ø');

1 row created.

Elapsed: 00:00:00.00
15:37:19 SYS@zkm(99)> commit;

Commit complete.

Elapsed: 00:00:00.00
15:37:22 SYS@zkm(99)> col name1 for a10
15:37:27 SYS@zkm(99)> col name2 for a10
15:37:27 SYS@zkm(99)> select * from zkm;

NAME1      NAME2
---------- ----------
？         ø

Elapsed: 00:00:00.00

由于name1为varchar类型本身不支持特殊字符乱码是正常的，而name2为nvarchar现在已经正常存取了。

开启会话，做10046。

执行sql，对应trace信息：

insert into zkm values ('ø',N'ø');

对应的trc信息：

*** 2021-08-18 16:03:01.812
WAIT #140175898804800: nam='SQL*Net message from client' ela= 24006178 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273781812848
CLOSE #140175898804800:c=9,e=9,dep=0,type=1,tim=1629273781812992
=====================
PARSING IN CURSOR #140175898795576 len=38 dep=0 uid=0 oct=2 lid=0 tim=1629273781814001 hv=1357752502 ad='ce798418' sqlid='fj1m2tx8fva5q'
insert into zkm values ('U'\00F8')
END OF STMT
PARSE #140175898795576:c=941,e=950,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1629273781813995
EXEC #140175898795576:c=256,e=256,p=0,cr=1,cu=2,mis=0,r=1,dep=0,og=1,plh=0,tim=1629273781814387
STAT #140175898795576 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=195 us)'
WAIT #140175898795576: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273781814475

*** 2021-08-18 16:04:06.396
WAIT #140175898795576: nam='SQL*Net message from client' ela= 64582323 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273846396824
CLOSE #140175898795576:c=9,e=9,dep=0,type=0,tim=1629273846397035

select N'ø' from dual;

对应的trc信息：

PARSING IN CURSOR #140175898795576 len=25 dep=0 uid=0 oct=3 lid=0 tim=1629273846398341 hv=1602785773 ad='f1c07ee8' sqlid='9rgvrkjgsj4gd'
select U'\00F8' from dual
END OF STMT
PARSE #140175898795576:c=1197,e=1208,p=0,cr=0,cu=2,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1629273846398339
EXEC #140175898795576:c=28,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1629273846398525
WAIT #140175898795576: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273846398588
FETCH #140175898795576:c=13,e=13,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1629273846398649
STAT #140175898795576 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)'
WAIT #140175898795576: nam='SQL*Net message from client' ela= 384 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273846399149
FETCH #140175898795576:c=9,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=1629273846399258
WAIT #140175898795576: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273846399369

注意，正常存取需要满足3个条件

客户端的字符集需要支持目标字符
正确设置环境变量NLS_LANG
正确设置环境变量ORA_NCHAR_LITERAL_REPLACE

在这个案例中，sqlplus是在服务器Linux上的，因此sqlplus本身没有字符集所以依托Linux字符集为UTF8，而UTF8满足第一个条件。

另外，NLS_LANG=AMERICAN_AMERICA.AL32UTF8和Linux的UTF8一致是正确的。

最后ORA_NCHAR_LITERAL_REPLACE=TRUE控制了Oracle存Unicode行为。

需要注意的另外一点，由于我的Linux是远程通过SecureCRT工具连接的，所以对应CRT的会话的字符集要设置和Linux字符集一样：

也就是说如果使用windows上边的sqlplus客户端，该客户端用的是936也就是简体中文字符集。

那么即使NLS_LANG和ORA_NCHAR_LITERAL_REPLACE设置正确也会有问题，因为客户端使用936(简体中文)送进去特殊字符"ø"的时候就已经出问题了，936不支持"ø"。

C:\Users\admin>chcp
活动代码页: 936

C:\Users\admin>set nls_lang
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

C:\Users\admin>set ORA_NCHAR_LITERAL_REPLACE
ORA_NCHAR_LITERAL_REPLACE=TRUE

C:\Users\admin>sqlplus sys/zkm@192.168.1.152/zkm as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 18 17:07:28 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

SQL> delete zkm;

2 rows deleted.

SQL> insert into zkm values ('ø',N'ø');

1 row created.

SQL> commit;

Commit complete.

SQL> col name1 for a10
SQL> col name2 for a10
SQL> select * from zkm;

NAME1      NAME2
---------- ----------
?          ?

另外，plsqldev这个工具，对于N'ø'这类特殊字符存储进去nchar或者nvarchar2字段的时候，不需要设置ORA_NCHAR_LITERAL_REPLACE也可以成功正常存取。

对plsqdev的会话开启10046，然后执行sql可以看出区别：

查会话sid：

然后oracle使用sys登录对该会话做10046：

17:26:10 SYS@zkm(1264)> select a.sid,a.serial#,b.pid,b.spid from v$session a ,v$process b where a.sid=&sid and b.addr=a.paddr;
Enter value for sid: 904
old   1: select a.sid,a.serial#,b.pid,b.spid from v$session a ,v$process b where a.sid=&sid and b.addr=a.paddr
new   1: select a.sid,a.serial#,b.pid,b.spid from v$session a ,v$process b where a.sid=904 and b.addr=a.paddr

       SID    SERIAL#        PID SPID
---------- ---------- ---------- ------------------------------------------------------------------------
       904      19623        566 31314

Elapsed: 00:00:00.00
17:26:18 SYS@zkm(1264)> oradebug setospid 31314
Oracle pid: 566, Unix process pid: 31314, image: oracle@zkm
17:26:26 SYS@zkm(1264)> oradebug unlimit
Statement processed.
17:26:34 SYS@zkm(1264)> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/zkm/zkm/trace/zkm_ora_31314.trc
17:27:08 SYS@zkm(1264)> oradebug event 10046 trace name context forever,level 12
Statement processed.

执行sql以及对应的trc信息：

select N'ø' from dual;

对应trc信息：

*** 2021-08-18 17:29:08.991
WAIT #140112422498592: nam='SQL*Net message from client' ela= 3971881 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278948991660
CLOSE #140112422498592:c=35,e=35,dep=0,type=3,tim=1629278948991949
PARSE #140112422437616:c=42,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1629278948992074
EXEC #140112422437616:c=31,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1629278948992156
WAIT #140112422437616: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278948992214
WAIT #140112422437616: nam='SQL*Net message from client' ela= 432 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278948992721
WAIT #140112422437616: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278948992806
FETCH #140112422437616:c=75,e=75,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1629278948992867
WAIT #140112422437616: nam='SQL*Net message from client' ela= 834 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278948993770
CLOSE #140112422437616:c=0,e=5,dep=0,type=3,tim=1629278948993881
PARSE #140112422396376:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1629278948993983
BINDS #140112422396376:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f6e770e3dd8  bln=22  avl=02  flg=05
  value=1
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f6e770e3df0  bln=22  avl=02  flg=01
  value=10000
WAIT #140112422396376: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278948994352
EXEC #140112422396376:c=374,e=352,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1629278948994401

*** 2021-08-18 17:29:09.050
WAIT #140112422396376: nam='SQL*Net message from client' ela= 55803 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949050287
CLOSE #140112422396376:c=71,e=71,dep=0,type=3,tim=1629278949050553
PARSE #140112422498592:c=57,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1629278949050700
BINDS #140112422498592:
 Bind#0
  oacdty=01 mxl=4000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=4000 off=0
  kxsbbbfp=7f6e770e2698  bln=4000  avl=00  flg=05
WAIT #140112422498592: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949051042
EXEC #140112422498592:c=343,e=342,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1629278949051110
WAIT #140112422498592: nam='SQL*Net message from client' ela= 600 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949051801
CLOSE #140112422498592:c=19,e=19,dep=0,type=3,tim=1629278949051952
=====================
PARSING IN CURSOR #140112422489040 len=33 dep=0 uid=0 oct=3 lid=0 tim=1629278949052107 hv=102554608 ad='a8c18128' sqlid='awymr8831tqzh'
select unistr('\00F8') from dual
END OF STMT
PARSE #140112422489040:c=81,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1629278949052105
EXEC #140112422489040:c=24,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1629278949052252
WAIT #140112422489040: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949052311
WAIT #140112422489040: nam='SQL*Net message from client' ela= 341 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949052710
WAIT #140112422489040: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949052794
FETCH #140112422489040:c=65,e=65,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1629278949052846
STAT #140112422489040 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)'
WAIT #140112422489040: nam='SQL*Net message from client' ela= 13438 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949066415
PARSE #140112420550224:c=37,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=272002086,tim=1629278949066577
WAIT #140112420550224: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949066639
WAIT #140112420550224: nam='SQL*Net message from client' ela= 8714 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949075423
CLOSE #140112422489040:c=11,e=11,dep=0,type=0,tim=1629278949075532
CLOSE #140112420550224:c=7,e=8,dep=0,type=3,tim=1629278949075599
PARSE #140112422498592:c=38,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1629278949075705
BINDS #140112422498592:
 Bind#0
  oacdty=01 mxl=4000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=4000 off=0
  kxsbbbfp=7f6e770e2698  bln=4000  avl=00  flg=05
WAIT #140112422498592: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949076087
EXEC #140112422498592:c=386,e=386,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1629278949076150
WAIT #140112422498592: nam='SQL*Net message from client' ela= 23076 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949099319
CLOSE #140112422498592:c=39,e=39,dep=0,type=3,tim=1629278949099542
PARSE #140112422394472:c=54,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1629278949099686
BINDS #140112422394472:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f6e770e3620  bln=22  avl=00  flg=05
 Bind#1
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77105000  bln=32767  avl=00  flg=05
 Bind#2
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77115000  bln=32767  avl=00  flg=05
 Bind#3
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77125000  bln=32767  avl=00  flg=05
 Bind#4
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77135000  bln=32767  avl=00  flg=05
 Bind#5
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e76fc5000  bln=32767  avl=00  flg=05
 Bind#6
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e76fd5000  bln=32767  avl=00  flg=05
 Bind#7
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e76fe5000  bln=32767  avl=00  flg=05
 Bind#8
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e76ff5000  bln=32767  avl=00  flg=05
 Bind#9
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77005000  bln=32767  avl=00  flg=05
 Bind#10
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77015000  bln=32767  avl=00  flg=05
WAIT #140112422394472: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629278949100916
EXEC #140112422394472:c=1176,e=1219,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1629278949100977

insert into zkm values ('ø',N'ø');

trc对应信息：

*** 2021-08-18 17:30:47.999
WAIT #140112422498592: nam='SQL*Net message from client' ela= 52681224 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279047999859
CLOSE #140112422498592:c=38,e=37,dep=0,type=3,tim=1629279048000109
PARSE #140112422437616:c=44,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1629279048000238
EXEC #140112422437616:c=33,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1629279048000327
WAIT #140112422437616: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048000392
WAIT #140112422437616: nam='SQL*Net message from client' ela= 387 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048000851
WAIT #140112422437616: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048000949
FETCH #140112422437616:c=51,e=51,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1629279048000987
WAIT #140112422437616: nam='SQL*Net message from client' ela= 556 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048001607
CLOSE #140112422437616:c=0,e=6,dep=0,type=3,tim=1629279048001681
PARSE #140112422396376:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1629279048001789
BINDS #140112422396376:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f6e770e3dd8  bln=22  avl=02  flg=05
  value=1
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f6e770e3df0  bln=22  avl=02  flg=01
  value=10000
WAIT #140112422396376: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048002186
EXEC #140112422396376:c=323,e=374,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1629279048002241
WAIT #140112422396376: nam='SQL*Net message from client' ela= 64591 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048066901
CLOSE #140112422396376:c=38,e=39,dep=0,type=3,tim=1629279048067161
PARSE #140112422498592:c=64,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1629279048067343
BINDS #140112422498592:
 Bind#0
  oacdty=01 mxl=4000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=4000 off=0
  kxsbbbfp=7f6e770e2698  bln=4000  avl=00  flg=05
WAIT #140112422498592: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048067765
EXEC #140112422498592:c=440,e=439,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1629279048067887
WAIT #140112422498592: nam='SQL*Net message from client' ela= 449 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048068474
CLOSE #140112422498592:c=13,e=14,dep=0,type=3,tim=1629279048068573
=====================
PARSING IN CURSOR #140112422489040 len=45 dep=0 uid=0 oct=2 lid=0 tim=1629279048069996 hv=1995508800 ad='aa58b168' sqlid='487qtctvg2320'
insert into zkm values ('?',unistr('\00F8'))
END OF STMT
PARSE #140112422489040:c=1310,e=1363,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1629279048069995
EXEC #140112422489040:c=364,e=364,p=0,cr=1,cu=2,mis=0,r=1,dep=0,og=1,plh=0,tim=1629279048070521
STAT #140112422489040 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=294 us)'
WAIT #140112422489040: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048070653

*** 2021-08-18 17:30:48.083
WAIT #140112422489040: nam='SQL*Net message from client' ela= 13060 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048083757
CLOSE #140112422489040:c=9,e=9,dep=0,type=0,tim=1629279048083949
PARSE #140112422498592:c=46,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1629279048084075
BINDS #140112422498592:
 Bind#0
  oacdty=01 mxl=4000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=4000 off=0
  kxsbbbfp=7f6e770e2698  bln=4000  avl=00  flg=05
WAIT #140112422498592: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048084343
EXEC #140112422498592:c=257,e=257,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1629279048084389
WAIT #140112422498592: nam='SQL*Net message from client' ela= 18285 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048102755
CLOSE #140112422498592:c=32,e=32,dep=0,type=3,tim=1629279048102955
PARSE #140112422394472:c=44,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1629279048103082
BINDS #140112422394472:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f6e770e3620  bln=22  avl=00  flg=05
 Bind#1
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77105000  bln=32767  avl=00  flg=05
 Bind#2
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77115000  bln=32767  avl=00  flg=05
 Bind#3
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77125000  bln=32767  avl=00  flg=05
 Bind#4
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77135000  bln=32767  avl=00  flg=05
 Bind#5
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e76fc5000  bln=32767  avl=00  flg=05
 Bind#6
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e76fd5000  bln=32767  avl=00  flg=05
 Bind#7
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e76fe5000  bln=32767  avl=00  flg=05
 Bind#8
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e76ff5000  bln=32767  avl=00  flg=05
 Bind#9
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77005000  bln=32767  avl=00  flg=05
 Bind#10
  oacdty=01 mxl=32767(32512) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=852 siz=32767 off=0
  kxsbbbfp=7f6e77015000  bln=32767  avl=00  flg=05
WAIT #140112422394472: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1629279048104189
EXEC #140112422394472:c=1067,e=1126,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1629279048104261

至此。

参考文档

Storing And Retrieving Data Into NVARCHAR2 Columns Explained With Test Case (文档 ID 2311604.1)
Writing Buffer Data With DBMS_OUTPUT.PUT_LINE Results in an Upside Down Question Mark with Multibyte Data Stored in an NVARCHAR2 Column Type (文档 ID 1538753.1)
The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g , 11g and 12c (文档 ID 276914.1)
再论Oracle字符集

http://www.orafaq.com/wiki/NVARCHAR2

http://www.orafaq.com/wiki/NCHAR

posted @ 2021-08-18 16:43 PiscesCanon 阅读(2649) 评论(0) 编辑收藏举报

刷新页面返回顶部

PiscesCanon

Oracle nvarchar2存储特殊字符乱码问题

Oracle nvarchar2存储特殊字符乱码问题

参考文档

公告