[20250729]绑定变量的字符串长度与字符集相关问题.txt

--//前段时间别人遇到的问题当时猜测与字符集相关，我记忆里https://nenadnoveljic.com/blog/bind_variable_length/有一个演示，
--//当时在我的测试环境就没有测试出来，当时猜测对方使用的字符集与我的不同。

--//在自己的测试环境重复测试看看，加强理解：

1.环境改造：
--//先修改环境变量LANG=en_US.UTF-8。
# cat /etc/locale.conf
LANG="en_US.UTF-8"
#LANG="en_US"

2.使用putty或者kitty登录。
--//配置在window->translation,remote character set 选择UTF-8.
--//登录简单的检查在bash shell的提示输入中文看看是否显示正常。

3.启动数据库略。
--//启动前修改环境变量
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
--//重新登录oracle，启动数据库略。

SYS@book> select * from v$nls_parameters ;
PARAMETER                      VALUE                                CON_ID
------------------------------ -------------------------------- ----------
NLS_LANGUAGE                   AMERICAN                                  1
NLS_TERRITORY                  AMERICA                                   1
NLS_CURRENCY                   $                                         1
NLS_ISO_CURRENCY               AMERICA                                   1
NLS_NUMERIC_CHARACTERS         .,                                        1
NLS_CALENDAR                   GREGORIAN                                 1
NLS_DATE_FORMAT                YYYY-MM-DD HH24:MI:SS                     1
NLS_DATE_LANGUAGE              AMERICAN                                  1
NLS_CHARACTERSET               ZHS16GBK                                  1
NLS_SORT                       BINARY                                    1
NLS_TIME_FORMAT                HH.MI.SSXFF AM                            1
NLS_TIMESTAMP_FORMAT           YYYY-MM-DD HH24:MI:SS.FF                  1
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                        1
NLS_TIMESTAMP_TZ_FORMAT        YYYY-MM-DD HH24:MI:SS.FF TZH:TZM          1
NLS_DUAL_CURRENCY              $                                         1
NLS_NCHAR_CHARACTERSET         AL16UTF16                                 1
NLS_COMP                       BINARY                                    1
NLS_LENGTH_SEMANTICS           BYTE                                      1
NLS_NCHAR_CONV_EXCP            FALSE                                     1
19 rows selected.
--//NLS_NCHAR_CHARACTERSET = AL16UTF16

4.测试：
$ cat  bind_varchar_len.txt
--//用于测试PLSQL字符串分配分界点
--// create table t (a varchar2(4000));
declare
v_a varchar2(&&1);
  begin
   for i in 1..&&1 loop
     v_a := rpad('X',i,'X');
     execute immediate 'select /*+ find_me &&1 */ count(*) from t where a=:v_a' using v_a ;
   end loop;
end;
/

SELECT sql_text
      ,sql_id
      ,child_number
      ,executions
      ,parse_calls
      ,loads
      ,invalidations
  FROM v$sql
 WHERE sql_text LIKE '%find_me &&1%' AND sql_text NOT LIKE '%v$sql%' AND sql_text NOT LIKE 'declare%'

SCOTT@book01p> @ txt/bind_varchar_len.txt 1001
PL/SQL procedure successfully completed.
SQL_TEXT                                                     SQL_ID                                  CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------------------------------------------------------ --------------------------------------- ------------ ---------- ----------- ---------- -------------
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc                                      0         32           1          1             0
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc                                      1         96           0          1             0
select /*+ find_me 1001 */ count(*) from t where a=:v_a      gwm6t1hu54cuc                                      2        873           0          1             0
--//按照实际的长度分配。

5.测试sqlplus for linux。
SCOTT@book01p> variable v_b varchar2(90)
SCOTT@book01p> exec :v_b := rpad('a',90,'a');
PL/SQL procedure successfully completed.
SCOTT@book01p> select * from t where a=:v_b;
no rows selected

SCOTT@book01p> @ hashz

HASH_VALUE SQL_ID                                  CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- --------------------------------------- ------------ ---------- ---------- ------------------- -----------
3009098523 7p9qv56tpqcsv                                      0      78619  b35b331b  2025-07-29 11:23:56    16777216

SCOTT@book01p> @ bind_cap 7p9qv56tpqcsv ''
SQL_ID        CHILD_NUMBER WAS_CAPTU NAME POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING                                          INST_ID
------------- ------------ --------- ---- -------- ---------- ------------------- --------------- -------------------------------------------------- ----------
7p9qv56tpqcsv            0 YES       :V_B        1       2000 2025-07-29 11:23:56 VARCHAR2(2000)  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa          1
                                                                                                  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

--//使用DATATYPE_STRING=2000.按照以前的介绍使用128就可以。明显定义90并不是真正的保存字符串长度。
--//使用10046跟踪，首先分析表。

SCOTT@book01p> @ 10046on 12
Session altered.

SCOTT@book01p> Select * from t where a=:v_b;
no rows selected
--//注意换成大写SE，主要目的重新分析。

SCOTT@book01p> @ 10046off
Session altered.

=====================
PARSING IN CURSOR #139946379010480 len=28 dep=0 uid=109 oct=3 lid=109 tim=9911309892 hv=2040552028 ad='65bed278' sqlid='7wr0faxwu0qkw'
SElect * from t where a=:v_b
END OF STMT
PARSE #139946379010480:c=743,e=1326,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=9911309892
BINDS #139946379010480:

 Bind#0
  oacdty=01 mxl=2000(180) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=852 siz=2000 off=0
  kxsbbbfp=7f47ce3f3410  bln=2000  avl=90  flg=05
  value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
EXEC #139946379010480:c=1582,e=1464,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=9911311432
WAIT #139946379010480: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9911311489
FETCH #139946379010480:c=8,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=9911311528
STAT #139946379010480 id=1 cnt=0 pid=0 pos=1 obj=145919 op='TABLE ACCESS FULL T (cr=0 pr=0 pw=0 str=1 time=10 us cost=2 size=2002 card=1)'

*** 2025-07-29T11:32:34.162052+08:00 (BOOK01P(3))
WAIT #139946379010480: nam='SQL*Net message from client' ela= 9795519 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9921107142
CLOSE #139946379010480:c=11,e=11,dep=0,type=0,tim=9921107248
=====================
--//字符串实际长度 avl=90，mxl=2000(180)，变成90*2=180，作者的测试是*3，其python的测试是*4，不知道其数据库字符集以及环境的设置。
--//可以在plsql与sqlplus两者的不同。
--//贴上原始作者测试的情况var v varchar2(90)。
Bind#0
  oacdty=01 mxl=2000(270) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=2000 off=0
  kxsbbbfp=7f9982103b40  bln=2000  avl=04  flg=05
  value="AAAA"
--//csi不同。

6.测试sqlplus for windows。

SCOTT@book01p> variable v_b varchar2(90)
SCOTT@book01p> exec :v_b := rpad('a',90,'a');

PL/SQL procedure successfully completed.

SCOTT@book01p> SELect * from t where a=:v_b;

no rows selected

SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
1213882039 cy0aw6945nrpr            0      24247  485a5eb7  2025-07-29 11:50:54    16777216

SCOTT@book01p> @ bind_cap cy0aw6945nrpr ''
SQL_ID        CHILD_NUMBER WAS NAME   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING                                          INST_ID
------------- ------------ --- ---- ---------- ---------- ------------------- --------------- -------------------------------------------------- ----------
cy0aw6945nrpr            0 YES :V_B          1        128 2025-07-29 11:50:54 VARCHAR2(128)   aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa          1
                                                                                              aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
--//使用DATATYPE_STRING=128.

7.收尾还原。

--//环境变量LANG=en_US
# cat /etc/locale.conf
#LANG="en_US.UTF-8"
LANG="en_US"

--//修改环境变量
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK