ANBOB™

有时需要在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多一些.