根据Oracle版本，字段长度变为32k或4k

你好，

我最近研究了一个非常奇怪的bug。
和最小化的代码到这个:

BEGIN
  FOR i IN (SELECT *
              FROM (SELECT MAX(CAST('USD' AS CHAR(3))) OVER() c
                      FROM Dual))
  LOOP
    Dbms_Output.Put_Line(LENGTH(i.c));
  END LOOP;
END;

它在Oracle 19上输出32767，在Oracle 12c上输出4000。
答案不应该是3吗？

愉快的一天

日伊尔维纳斯·维德曼塔斯

4k和32k之间的差异很可能与版本无关，但是您的数据库最大字符串设置

SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
max_string_size                      string      EXTENDED

标准 = 4k，扩展 = 32k

也就是说，这仍然很有趣，在这两种情况下我们都没有回来。我认为那是一只虫子。

请在支持下记录一个错误 (与我这样做相比，它来自客户的重量更大)。

这里有一些额外的测试用例，你可以添加到它

SQL> declare
  2    x varchar2(100);
  3  BEGIN
  4    FOR i IN (SELECT MAX(CAST('USD' AS CHAR(3))) OVER() c FROM Dual)
  5    LOOP
  6      x := i.c;
  7    END LOOP;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2    x varchar2(100);
  3  BEGIN
  4    FOR i IN (SELECT *
  5                FROM (SELECT MAX(CAST('USD' AS CHAR(3))) OVER() c
  6                        FROM Dual))
  7    LOOP
  8      x := i.c;
  9    END LOOP;
 10  END;
 11  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-06512: at line 8


SQL>
SQL> declare
  2    x clob;
  3  BEGIN
  4    FOR i IN (SELECT *
  5                FROM (SELECT MAX(CAST('USD' AS CHAR(3))) OVER() c
  6                        FROM Dual))
  7    LOOP
  8     select dump(i.c) into x from dual;
  9    END LOOP;
 10    dbms_output.put_line(x);
 11  END;
 12  /
Typ=96 Len=32767:
85,83,68,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
[snip]
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> declare
  2    x varchar2(100);
  3  BEGIN
  4    SELECT *
  5    into x
  6                FROM (SELECT MAX(CAST('USD' AS CHAR(3))) OVER() c
  7                        FROM Dual);
  8  END;
  9  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4


SQL>
SQL>
SQL>