[20250418]绑定变量太多的限制.txt

--//看看最多1条sql语句能有多少绑定变量。

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.建立测试环境：

create table t as select * from all_objects;
create unique index i_t_object_id on t(object_id);
--//分析略。

$ cat a.txt
set pagesize 0
set head off
set feedback off
set verify off
set timing off
spool b1.txt
select 'set termout off'  from dual;
select 'variable b'||to_char(level)||' number;'  from dual connect by level<=&1;
select 'begin'  from dual;
select ':b'||to_char(level)||' :='|| to_char(level)||';'  from dual connect by level<=&1;
select 'end;'  from dual;
select '/' from dual ;
select 'set termout on' txt from dual;
spool off

spool b2.txt
--select 'set timing on' txt from dual;
select 'select count(data_object_id) from t where (1,object_id) in (' txt from dual ;
select '(1,:b'||to_char(level)||'),' txt from dual connect by level<=&&1 -1 ;
select '(1,:b'||to_char(&&1)||'));' txt from dual ;
--select 'set timing off' txt from dual;
spool off

set pagesize 9999
set head on
set feedback on

3.测试1,begin与end之间只能赋值32767个变量：
SCOTT@book01p> @ a.txt 32768
--//输出略。
--//编辑b1.txt，删除开头的set termout off， set termout on。

SCOTT@book01p> @ b1.txt
:b829 :=829;
        *
ERROR at line 830:
ORA-06550: line 32770, column 1:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PLS-00123: program too large (codegen operands)

$ sed -n "32770p" b1.txt
:b1 :=1;

--//删除:b32768 :=32768;一行，再测试。

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

--//说明再begin..end之间可以定义32767个绑定变量。

4.测试2,一条sql语句能出现多少绑定变量：

SCOTT@book01p> @ a.txt 65536
--//输出略。
--//编辑b1.txt，删除开头的set termout off， set termout on，并且适当编辑满足前面begin..end之间可以定义32767个绑定变量的
--//限制。

$ egrep -C 1 --no-group-separator "begin|end" b1.txt
variable b65536 number;
begin
:b1 :=1;
:b32767 :=32767;
end;
/
begin
:b32768 :=32768;
:b65534 :=65534;
end;
/
begin
:b65535 :=65535;
:b65536 :=65536;
end;
/


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


SCOTT@book01p> @ b2.txt
select count(data_object_id) from t where (1,object_id) in (
                                                           *
ERROR at line 1:
ORA-00920: invalid relational operator

$ oerr ora 00920
00920, 00000, "invalid relational operator"
// *Cause:
// *Action:

--//不知道是否超过集合的限制.

--//编辑b2.txt 注解其中一行,看看情况的变化：

$ head -2 b2.txt ;tail -3 b2.txt
select count(data_object_id) from t where (1,object_id) in (
(1,:b1),
(1,:b65534),
--(1,:b65535),
(1,:b65536));

SCOTT@book01p> @ b2.txt
COUNT(DATA_OBJECT_ID)
---------------------
                 3970
--//可以发现sql语句里面存在65535个绑定变量，超过65536报错。
--//编辑b2.txt,再测试看看：

$ head -2 b2.txt ;tail -5 b2.txt
select count(data_object_id) from t where (1,object_id) in (
(1,:b1),
(1,:b65534))
or
(1,object_id) in (
(1,:b65535),
(1,:b65536));

SCOTT@book01p> @ b2.txt
select count(data_object_id) from t where (1,object_id) in (
*
ERROR at line 1:
ORA-00920: invalid relational operator
--//说明不是超过集合的限制.

--//可以看出一条sql语句里面存在65535个绑定变量。