转自：互联网
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了。
------------------------------------------------
网上经常有人问Oracle varchar2最大支持长度为多少？其实这个叫法不太准确，varchar2分别在oracle的sql和pl/sql中都有使用，oracle 在sql参考手册和pl/sql参考手册中指出：oracle sql varchar2的最大支持长度为4000个字节(bytes)；而 oracle plsql varchar2最大支持长度为32767个字节。这就是有朋友问，在pl/sql中定义了32767个(字符/字节)，为什么在表的字段中不能定义大于4000个字节的原因了。
下面分别给出varchar2在Oracle sql和plsql中最大长度的示例。
Oracle sql中varchar2最大支持长度示例–最大长度为4000
- drop table idb_varchar2;
- create table idb_varchar2
- (id number,
- name varchar2(4000 char));
- insert into idb_varchar2 values(1,lpad('中',32767,'中'));
- insert into idb_varchar2 values(2,lpad('a',32767,'b'));
- commit;
- select id,lengthb(name),length(name) from idb_varchar2;
- drop table idb_varchar2;
- create table idb_varchar2
- (id number,
- name varchar2(4000 char));
- insert into idb_varchar2 values(1,lpad('中',32767,'中'));
- insert into idb_varchar2 values(2,lpad('a',32767,'b'));
- commit;
- select id,lengthb(name),length(name) from idb_varchar2;
输出结果:
dw@dw>drop table idb_varchar2; 表已删除。 dw@dw>create table idb_varchar2 2 (id number, 3 name varchar2(4000 char)); 表已创建。 dw@dw>insert into idb_varchar2 values(1,lpad('中',32767,'中')); 已创建 1 行。 dw@dw>insert into idb_varchar2 values(2,lpad('a',32767,'b')); 已创建 1 行。 dw@dw>commit; 提交完成。 dw@dw>select id,lengthb(name),length(name) from idb_varchar2; ID LENGTHB(NAME) LENGTH(NAME) ---------- ------------- ------------ 1 4000 2000 2 4000 4000 已选择2行。 |
Oracle sql中varchar2最大支持长度示例–设计长度为4001
- drop table idb_varchar2;
- create table idb_varchar2
- (id number,
- name varchar2(4001));
- drop table idb_varchar2;
- create table idb_varchar2
- (id number,
- name varchar2(4001));
结果：
dw@dw>drop table idb_varchar2; 表已删除。 dw@dw>create table idb_varchar2 2 (id number, 3 name varchar2(4001)); name varchar2(4001)) * 第 3 行出现错误: ORA-00910: 指定的长度对于数据类型而言过长 |
超过4001会报错。
Oracle plsql中varchar2最大支持长度示例
- set serveroutput on
- declare
- v_var varchar2(32767 byte);
- v_char varchar2(32767 char);
- begin
- v_var := lpad('a',32767,'a');
- dbms_output.put_line(length(v_var));
- v_char := lpad('中',32767,'中');
- dbms_output.put_line(lengthb(v_var));
- v_var := lpad('中',32768,'中');
- end;
- /
- --定义如果超过32768会报错
- declare
- v_var varchar2(32768);
- begin
- null;
- end;
- /
- set serveroutput on
- declare
- v_var varchar2(32767 byte);
- v_char varchar2(32767 char);
- begin
- v_var := lpad('a',32767,'a');
- dbms_output.put_line(length(v_var));
- v_char := lpad('中',32767,'中');
- dbms_output.put_line(lengthb(v_var));
- v_var := lpad('中',32768,'中');
- end;
- /
- --定义如果超过32768会报错
- declare
- v_var varchar2(32768);
- begin
- null;
- end;
- /
输出结果：
dw@dw>set serveroutput on
dw@dw>declare
2 v_var varchar2(32767 byte);
3 v_char varchar2(32767 char);
4 begin
5 v_var := lpad('a',32767,'a');
6 dbms_output.put_line(length(v_var));
7 v_char := lpad('中',32767,'中');
8 dbms_output.put_line(lengthb(v_var));
9 v_var := lpad('中',32768,'中');
10 end;
11 /
32767
32767
declare
*
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 9
dw@dw>
dw@dw>declare
2 v_var varchar2(32768);
3 begin
4 null;
5 end;
6 /
v_var varchar2(32768);
*
第 2 行出现错误:
ORA-06550: 第 2 行, 第 18 列:
PLS-00215: 字符串长度限制在范围 (1...32767)
3243
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
