Dynamic SQL Supports Statements Length Characters Limit (execute immediate sql长度限制)

有时需要在plsql 写一些动态sql,用execute immediate 或dbms_sql 调 用，但是execute immediate 后动态sql 文本的长度限制是多少呢？

DECLARE
   l_sql   VARCHAR2 (32767);
   l_val   VARCHAR2 (32767);
BEGIN
   FOR i IN 1..40
   LOOP
      l_val := LPAD ('x', i*1000, 'x');
      l_sql:='begin '
      l_sql:=l_sql ||' insert into test values('''||l_val||''');';
      l_sql:=l_sql ||' insert into test values('''||l_val||''');';
      l_sql:=l_sql || end;';
      --DBMS_OUTPUT.put_line ('sql text:' || l_sql);
      DBMS_OUTPUT.put_line ('length bytes:' || LENGTHB (l_sql));
    EXECUTE IMMEDIATE l_sql||l_sql ;
   END LOOP;
END;
/

anbob@ANBOB>DECLARE
  2     l_sql   VARCHAR2 (32767);
  3     l_val   VARCHAR2 (32767);
  4  
  5  BEGIN
  6  
  7     FOR i IN 1..5
  8     LOOP
  9        l_val := LPAD ('x', i*1000, 'x');
 10        l_sql:='insert into test values('''||l_val||''')';
 11        --DBMS_OUTPUT.put_line ('sql text:' || l_sql);
 12        DBMS_OUTPUT.put_line ('length bytes:' || LENGTHB (l_sql));
 13      EXECUTE IMMEDIATE l_sql ;
 14     END LOOP;
 15  END;
 16  /
length bytes:1027
length bytes:2027
length bytes:3027
length bytes:4027
length bytes:5027
DECLARE
*
ERROR at line 1:
ORA-01704: string literal too long
ORA-06512: at line 13

anbob@ANBOB>ho oerr ora 1704
01704, 00000, "string literal too long"
// *Cause: The string literal is longer than 4000 characters.
// *Action: Use a string literal of at most 4000 characters.
// Longer values may only be entered using bind variables.

那execute immediate 后sql 变量就最大长度4000字符么?不是的

anbob@ANBOB>DECLARE
  2     l_sql   VARCHAR2 (32767);
  3     l_val   VARCHAR2 (32767);
  4  BEGIN
  5     FOR i IN 1 .. 50
  6     LOOP
  7        l_val := LPAD ('x', i * 70, 'x');
  8        l_sql := 'begin ';
  9  
 10        FOR j IN 1 .. 10
 11        LOOP
 12           l_sql := l_sql || ' insert into test values(''' || l_val || ''');';
 13        END LOOP;
 14  
 15        l_sql := l_sql || ' end;';
 16        --DBMS_OUTPUT.put_line ('sql text:' || l_sql);
 17        DBMS_OUTPUT.put_line ('length:' || LENGTHB (l_sql));
 18  
 19        EXECUTE IMMEDIATE l_sql;
 20     END LOOP;
 21  END;
 22  /
length:1001
length:1701
length:2401
...
length:32501
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 12

在11g 版本以前execute immediate sql text的长度是32K 字符,但是在9I内因为一个内部bug有可能超过32k，但在10g中修改后会抛出pls-172 异常，在11g中 execute immediate 允许clob 类型突破了sql text长度32k的限制.
引自MOS

In 9i, SQL string argument for EXECUTE IMMEDIATE could potentially exceed 32k due to an internal bug. This bug was resolved in 10g by raising an Oracle PLS-172 error for string
arguments to EXECUTE IMMEDIATE which are > 32k.
Prior to 11g, the SQL string used must be < 32k.
As of 11g, the EXECUTE IMMEDIATE usage has been re-written to accept strings > 32k as CLOB values are permitted, implemented as resolution to an Enhancement Request titled
"STATEMENT EXPRESSION AS CLOB IN DYNAMIC SQL".

-- version 11.2.0.3

anbob@ANBOB>DECLARE
  2     l_sql   CLOB;
  3     l_val   VARCHAR2 (32767);
  4  BEGIN
  5     FOR i IN 1 .. 100
  6     LOOP
  7        l_val := LPAD ('x', i * 70, 'x');
  8  
  9        DBMS_LOB.createtemporary (l_sql, FALSE);
 10        DBMS_LOB.append (l_sql, TO_CLOB ('begin '));
 11  
 12        FOR j IN 1 .. 10
 13        LOOP
 14           DBMS_LOB.append (
 15              l_sql,
 16              TO_CLOB (' insert into test values(''' || l_val || ''');'));
 17        END LOOP;
 18  
 19        DBMS_LOB.append (l_sql, TO_CLOB ('end;'));
 20        --DBMS_OUTPUT.put_line ('sql text:' || l_sql);
 21        DBMS_OUTPUT.put_line ('length:' || dbms_lob.GETLENGTH (l_sql));
 22  
 23        EXECUTE IMMEDIATE l_sql;
 24     END LOOP;
 25  END;
 26  /
length:1000
length:1700
length:2400
...
length:38800
length:39500
length:40200
length:40900
DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 32:
PL/SQL: ORA-01704: string literal too long

可以看到用clob 类型execute immediate动态sql 的确可以突破32K，但是我的测试发现sql 也是增加到了40000多一些.