[20260827]sqlplus使用绑定变量与nul字符问题.txt

--//测试绑定变量里有nul字符的情况。

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
$ cat -v a.txt
variable a varchar2(32)
exec :a := '4(^A^@'

select
 ora_hash(:a,4294967295,0) n20
,to_char(ora_hash(:a,4294967295,0),'FM0xxxxxxxxxxxxxxx')  c20
from dual;

$ cat ora_hash2.txt
select
 ora_hash(UTL_RAW.cast_to_varchar2(hextoraw('&1')),4294967295,&2) n20
,to_char(ora_hash(UTL_RAW.cast_to_varchar2(hextoraw('&1')),4294967295,&2),'FM0xxxxxxxxxxxxxxx')  c20
from dual;

SCOTT@book01p> @ ora_hash2.txt 34280100 0

                  N20 C20
--------------------- --------------------
           2945036863 00000000af89b23f

SCOTT@book01p> @ a.txt
ERROR:
ORA-01756: quoted string not properly terminated



                  N20 C20
--------------------- --------------------

--//还是遇到类似的问题，读取执行文本时遇到\x00,后面的内容被截断，导致出现ORA-01756: quoted string not properly terminated。

$ cat -v a.txt
variable a varchar2(32)
--exec :a := '4(^A^@'
exec :a := '4(^A'||chr(0)

select
 ora_hash(:a,4294967295,0) n20
,to_char(ora_hash(:a,4294967295,0),'FM0xxxxxxxxxxxxxxx')  c20
from dual;

SCOTT@book01p> @ a.txt
PL/SQL procedure successfully completed.


                  N20 C20
--------------------- --------------------
           2945036863 00000000af89b23f

--//没有什么好的解决方法，执行文本遇到\x00,选择截断。无法避开这个问题，最佳的方法不再脚本出现\x00字符。
--//与前面的情况不同，这里是截断，而前面是漏掉该字符。