(标题的灵感来自于Gary Myers在Why does Oracle varchar2 have a mandatory size as a definition parameter?年的评论)

考虑以下变量：

declare
  -- database table column interfacing variable
  v_a tablex.a%type; -- tablex.a is varchar2
  -- PL/sql only variable
  v_b varchar2(32767); -- is this a poor convention ?
begin
  select a into v_a from tablex where id = 1;
  v_b := 'Some arbitrary string: ' || v_a; -- ignore potential ORA-06502
  insert into tabley(id,a) values(1,v_a); -- tablex.a and tabley.a types match
  v_b := v_b || ' More arbitrary characters';
end;
/

变量v_a用于连接数据库表列,因此使用%type attribute.但是如果我知道数据类型是varchar2,为什么我不应该使用varchar2(4000)或varchar2(32767)来保证从数据库列读取的字符串将总是适合PL / sql变量？除了％type属性的优越性之外,还有其他任何反对这个约定的论据吗？

变量v_b仅用于PL / sql代码,通常返回到JDBC客户端(Java / Python程序,Oracle SOA / OSB等)或转储到平面文件(使用UTL_FILE).如果varchar2表示例如csv-line为什么我应该费心去计算确切的最大可能长度(除了验证该行在所有情况下都适合32767字节所以我不需要clob)并且每次我的数据模型改变时重新计算？

有很多问题涵盖了sql中的varchar2长度语义,并解释了为什么varchar2(4000)在sql中很糟糕.此外,sql和PL / sql varchar2-type之间的区别很好：

> What is the size limit for a varchar2 PL/SQL subprogram argument in Oracle?
> VARCHAR(MAX) versus VARCHAR(n) in Oracle
> Why does Oracle varchar2 have a mandatory size as a definition parameter?
> Why does VARCHAR need length specification?
> What is the default size of a varchar2 input to Oracle stored procedure,and can it be changed?
> Why using anything else but VARCHAR2(4000) to store strings in an Oracle database?
> Why does an oracle plsql varchar2 variable need a size but a parameter does not?
> Ask Tom问题：“我与建模组合作,他们希望定义每个varchar2字段的最大长度.”

我讨论过这个问题的唯一地方是APC在answer中的第3点和第4点：

The database uses the length of a variable when allocating memory for PL/sql collections. As that memory comes out of the PGA supersizing the variable declaration can lead to programs failing because the server has run out of memory.

There are similar issues with the declaration of single variables in PL/sql programs,it is just that collections tend to multiply the problem.

例如. Oracle PL / sql编程,第5版作者：Steven Feuerstein没有提到声明varchar2变量太长的任何缺点,所以它不是一个严重的错误,对吧？

更新

经过一些谷歌搜索后,我发现Oracle文档在发布期间已经发展：

引用PL / sql用户指南和参考10g第2版Chapter 3 PL/SQL Datatypes：

Small VARCHAR2 variables are optimized for performance,and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer,PL/sql dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes,PL/sql preallocates the full declared length of the variable. For example,if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable,the former takes up 500 bytes and the latter takes up 1999 bytes.

引用PL / sql用户指南和参考11g第1版Chapter 3 PL/SQL Datatypes：

For a CHAR variable,or for a VARCHAR2 variable whose maximum size is less than 2,000 bytes,PL/sql allocates enough memory for the maximum size at compile time. For a VARCHAR2 whose maximum size is 2,000 bytes or more,PL/sql allocates enough memory to store the actual value at run time. In this way,PL/sql optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use.

For example,if you assign the same 500-byte value to VARCHAR2(1999 BYTE) and VARCHAR2(2000 BYTE) variables,PL/sql allocates 1999 bytes for the former variable at compile time and 500 bytes for the latter variable at run time.

但PL / sql用户指南和参考11g第2版Chapter 3 PL/SQL Datatypes不再提及内存分配,我根本找不到任何有关内存分配的信息. (我正在使用此版本,因此我只检查了11.2文档.)PL / sql用户指南和参考12c版本1 Chapter 3 PL/SQL Datatypes也是如此.

我还发现Jeffrey Kemp的an answer也解决了这个问题.但Jeffrey的回答是指10.2文档,问题根本不是PL / sql.