今天碰到一个问题，通过一个SQL语句查询时，出如下问题：
       ORA-06502: PL/SQL: numeric or value error: character string buffer too small
       ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30

 问题出现在通过WMSYS.WM_CONCAT_IMPL函数重新组装字符串的时候，组装的串过长，varchar2类型的数据类型最大为4000个字节，在PL/SQL中最大为32767，原因就在这WMSYS.WM_CONCAT_IMPL组装的这个字符串长度为18031，在schema级下varchar2最大是4000，超过最大长度，报错。(记下该问题，长记性~)

关于 varchar2 的最大长度

varchar2有两个最大长度：一个是在字段类型4000；一个是在PL/SQL中变量类型32767。今天犯了一个小错误，就是函数的varchar2类型的返回值长度也是4000，而不是我以为的32767。

 

想了一下，这是一个比较容易出错的地方。因为在函数中我可以声明长度超过4000的字符串变量，并且将它作为返回值，这里是不会提示编译错误的。这个函数平时都可以正常执行，而一旦这个字符串长度超过4000，函数执行就会出错。所以这个问题虽然比较简单，仍然记录一下。

 

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 := rpad('a', 4001, 'b');
   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
  
 本来以为记录下来就好。刚才跟一位同事讨论了一下，认为有可能在schema级varchar2的长度限制都是4000，而在PL/SQL代码级的长度限制是32767。下面继续测试：
  
 -- 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 := rpad('a', 32767, 'b');
   5    dbms_output.put_line(funny(v_char));
   6  end;
   7  /
 32767
  
 PL/SQL 过程已成功完成。
  
 -- 2. 同样的函数，在schema级和在PL/SQL代码级长度限制不同
 SQL> select length(rpad('a', 4001, 'b')) from dual;
  
 LENGTH(RPAD('A',3276558,'B'))
 -----------------------------
                          4000
  
 SQL> begin
   2    dbms_output.put_line(length(rpad('a', 4001, 'b')));
   3  end;
   4  /
 4001
  
 PL/SQL 过程已成功完成。
  
  
 如果中间结果字符串长度超过限制会怎样呢？
  
 （1）在PL/SQL中，如果中间结果超过32767，没有影响：
  
 SQL> declare
   2    v_char1 varchar2(32767);
   3    v_char2 varchar2(32767);
   4    v_char3 varchar2(10);
   5  begin
   6    v_char1 := lpad('a', 32767, 'a');
   7    v_char2 := lpad('b', 32767, 'b');
   8    v_char3 := substr(v_char1 || v_char2, 32763, 10);
   9    dbms_output.put_line(v_char3);
  10  end;
  11  /
 aaaaabbbbb
  
 PL/SQL 过程已成功完成。
  
 （2）在schema级如果中间结果超过4000，会提示错误：
  
 SQL> select substr(rpad('a', 4000, 'a')||rpad('b', 4000, 'b'), 3996, 10) from dual;
 select substr(rpad('a', 4000, 'a')||rpad('b', 4000, 'b'), 3996, 10) from dual
                                                                          *
 第 1 行出现错误:
 ORA-01489: 字符串连接的结果过长
  
  
 如果字符串长度超过限制怎么办？可以使用CLOB类型。比如最开始的那个例子，可以将返回值改为CLOB类型：
  
 SQL> create or replace function funny return clob
   2  is
   3    v_yct varchar2(32767);
   4  begin
   5    v_yct := rpad('a', 4001, 'b');
   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了。