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字符集