[20260715]sqlplus定义绑定变量名长度.txt

--//测试需要了解定义绑定变量名长度，通过例子说明问题：

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.测试：
--//定义变量长度超过128不报错，实际长度129.
SCOTT@book01p> variable B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAM number;

SCOTT@book01p> print
SP2-0552: Bind variable "B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" not declared.
--//执行print 报错。

SCOTT@book01p> exec :B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAM :=1;
SP2-0552: Bind variable "B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" not declared.

--//定义变量长度超过128不报错。
SCOTT@book01p> exec :B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA :=1;
SP2-0552: Bind variable "B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" not declared.

--//定义变量长度=128。
SCOTT@book01p> variable B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA number;
SCOTT@book01p> exec :B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA :=1;
PL/SQL procedure successfully completed.

--//很容易确定sqlplus下定义绑定变量名的最大长度是128字节，估计11g是30字节。

3.在11g下重复验证：
SYS@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> variable a12345678912345678901234567890 number;
SYS@book> variable a123456789123456789012345678901 number;
SP2-0553: Illegal variable name "a123456789123456789012345678901".
--//对比2者提示不一样。

4.如果使用21c的sqlplus客户端连接11g数据库呢？

SCOTT@192.168.56.102:1521/book> variable B19895AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA number;
SCOTT@192.168.56.102:1521/book> print
ERROR:
ORA-00972: identifier is too long

SCOTT@192.168.56.102:1521/book> variable a123456789123456789012345678901 number;
SCOTT@192.168.56.102:1521/book> exec :a123456789123456789012345678901 := 1;
PL/SQL procedure successfully completed.
--//没有报错。

SCOTT@192.168.56.102:1521/book> print  a123456789123456789012345678901 ;
ERROR:
ORA-00972: identifier is too long
--//实际上11g限制sqlplus绑定变量名最大30个字符。

SCOTT@192.168.56.102:1521/book> print  a12345678912345678901234567890 ;
SP2-0552: Bind variable "A12345678912345678901234567890" not declared.
--//取前30个字符，变成没有declared.

SCOTT@192.168.56.102:1521/book> variable a12345678912345678901234567890 number;
SCOTT@192.168.56.102:1521/book> exec :a12345678912345678901234567890 := 2;
PL/SQL procedure successfully completed.

SCOTT@192.168.56.102:1521/book> print  a12345678912345678901234567890 ;
A12345678912345678901234567890
------------------------------
                             2