关于oracle中varchar2的最大长度

原文地址

关于 varchar2 的最大长度
varchar2有两个最大长度&＃xff1a;一个是在字段类型4000&＃xff1b;一个是在PL/SQL中变量类型32767。今天犯了一个小错误&＃xff0c;就是函数的varchar2类型的返回值长度也是4000&＃xff0c;而不是我以为的32767。
 
想了一下&＃xff0c;这是一个比较容易出错的地方。因为在函数中我可以声明长度超过4000的字符串变量&＃xff0c;并且将它作为返回值&＃xff0c;这里是不会提示编译错误的。这个函数平时都可以正常执行&＃xff0c;而一旦这个字符串长度超过4000&＃xff0c;函数执行就会出错。所以这个问题虽然比较简单&＃xff0c;仍然记录一下。
 
C:\Documents and Settings\yuechao.tianyc>sqlplus
 
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 5月 5 17:15:59 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
请输入用户名:  test/test
 
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> create or replace function funny return varchar2
  2  is
  3    v_yct varchar2(32767);
  4  begin
  5    v_yct :&＃61; rpad(&＃39;a&＃39;, 4001, &＃39;b&＃39;);
  6    return v_yct;
  7  end;
  8  /
 
函数已创建。
 
SQL> select funny from dual;
select funny from dual
       *
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 "TEST.FUNNY", line 6
 
本来以为记录下来就好。刚才跟一位同事讨论了一下&＃xff0c;认为有可能在schema级varchar2的长度限制都是4000&＃xff0c;而在PL/SQL代码级的长度限制是32767。下面继续测试&＃xff1a;
 
-- 1. 作为函数入参的限制是32767
SQL> create or replace function funny( p_char in varchar2 ) return number
  2  is
  3  begin
  4    return length(p_char);
  5  end;
  6  /
 
函数已创建。
 
SQL> declare
  2    v_char varchar2(32767);
  3  begin
  4    v_char :&＃61; rpad(&＃39;a&＃39;, 32767, &＃39;b&＃39;);
  5    dbms_output.put_line(funny(v_char));
  6  end;
  7  /
32767
 
PL/SQL 过程已成功完成。
 
-- 2. 同样的函数&＃xff0c;在schema级和在PL/SQL代码级长度限制不同
SQL> select length(rpad(&＃39;a&＃39;, 4001, &＃39;b&＃39;)) from dual;
 
LENGTH(RPAD(&＃39;A&＃39;,3276558,&＃39;B&＃39;))
-----------------------------
                         4000
 
SQL> begin
  2    dbms_output.put_line(length(rpad(&＃39;a&＃39;, 4001, &＃39;b&＃39;)));
  3  end;
  4  /
4001
 
PL/SQL 过程已成功完成。
 
 
如果中间结果字符串长度超过限制会怎样呢&＃xff1f;
 
&＃xff08;1&＃xff09;在PL/SQL中&＃xff0c;如果中间结果超过32767&＃xff0c;没有影响&＃xff1a;
 
SQL> declare
  2    v_char1 varchar2(32767);
  3    v_char2 varchar2(32767);
  4    v_char3 varchar2(10);
  5  begin
  6    v_char1 :&＃61; lpad(&＃39;a&＃39;, 32767, &＃39;a&＃39;);
  7    v_char2 :&＃61; lpad(&＃39;b&＃39;, 32767, &＃39;b&＃39;);
  8    v_char3 :&＃61; substr(v_char1 || v_char2, 32763, 10);
  9    dbms_output.put_line(v_char3);
 10  end;
 11  /
aaaaabbbbb
 
PL/SQL 过程已成功完成。
 
&＃xff08;2&＃xff09;在schema级如果中间结果超过4000&＃xff0c;会提示错误&＃xff1a;
 
SQL> select substr(rpad(&＃39;a&＃39;, 4000, &＃39;a&＃39;)||rpad(&＃39;b&＃39;, 4000, &＃39;b&＃39;), 3996, 10) from dual;
select substr(rpad(&＃39;a&＃39;, 4000, &＃39;a&＃39;)||rpad(&＃39;b&＃39;, 4000, &＃39;b&＃39;), 3996, 10) from dual
                                                                         *
第 1 行出现错误:
ORA-01489: 字符串连接的结果过长
 
 
如果字符串长度超过限制怎么办&＃xff1f;可以使用CLOB类型。比如最开始的那个例子&＃xff0c;可以将返回值改为CLOB类型&＃xff1a;
 
SQL> create or replace function funny return clob
  2  is
  3    v_yct varchar2(32767);
  4  begin
  5    v_yct :&＃61; rpad(&＃39;a&＃39;, 4001, &＃39;b&＃39;);
  6    return v_yct;
  7  end;
  8  /
 
函数已创建。
 
SQL> select funny from dual;
 
FUNNY
--------------------------------------------------------------------------------
abbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
 
SQL> set long 10000
SQL> select funny from dual;
FUNNY
--------------------------------------------------------------------------------
abbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
.
 
这样返回字符串的最大长度就是32767了。