Oracle varchar2最大支持长度(转)

oerr ora 06502
06502, 00000, "PL/SQL: numeric or value error%s"
// *Cause: An arithmetic, numeric, string, conversion, or constraint error
// occurred. For example, this error occurs if an attempt is made to
// assign the value NULL to a variable declared NOT NULL, or if an
// attempt is made to assign an integer larger than 99 to a variable
// declared NUMBER(2).
// *Action: Change the data, how it is manipulated, or how it is declared so
// that values do not violate constraints.

今天遇到一个错误提示：ORA-06502:PL/SQL :numberic or value error: character string buffer too small，一般对应的中文信息为：ORA-06502: PL/SQL: 数字或值错误 :字符串缓冲区太小。仔细检查调试过程中才发现是开发人员定义了一个变量，但是在脚本里面赋予了该变量超过其长度的值。结果就报这个错误。我习惯总结每一个遇到的错误信息，既有利于学习、总结知识，也方便以后遇到此类问题能够及时给出解决方法。

如果执行oerr ora 06502命令，没有提及详细原因(Cause)以及解决方法（Action）。这个估计是出现这类错误的场景太多了的缘故。

$ oerr ora 06502

06502, 00000, "PL/SQL: numeric or value error%s"

// *Cause:

// *Action:

在官方文档http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm，我看到了关于ORA-06502的错误的一些出现场景。非常有意思。有兴趣的最好直接阅读源文档。

1: 赋值或插入超过长度的值。

Assigning or Inserting Too-Long Values

If the value that you assign to a character variable is longer than the maximum size of the variable, an error occurs. For example:

   1: DECLARE

2:

   3: c VARCHAR2(3 CHAR);

4:

   5: BEGIN

6:

   7: c := 'abc ';

8:

   9: END;

10:

  11: /

12:

  13: Result:

14:

  15: DECLARE

16:

  17: *

18:

  19: ERROR at line 1:

20:

  21: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

22:

  23: ORA-06512: at line 4

24:

2: 违反了SIMPLE_INTEGER Subtype约束

PLS_INTEGER and its subtypes can be implicitly converted to these data types:

· CHAR

· VARCHAR2

· NUMBER

· LONG

All of the preceding data types except LONG, and all PLS_INTEGER subtypes, can be implicitly converted to PLS_INTEGER.

A PLS_INTEGER value can be implicitly converted to a PLS_INTEGER subtype only if the value does not violate a constraint of the subtype. For example, casting the PLS_INTEGER value NULL to the SIMPLE_INTEGER subtype raises an exception, as Example 3-5 shows.

Example 3-5 Violating Constraint of SIMPLE_INTEGER Subtype

   1: DECLARE

2:

   3: a SIMPLE_INTEGER := 1;

4:

   5: b PLS_INTEGER := NULL;

6:

   7: BEGIN

8:

   9: a := b;

10:

  11: END;

12:

  13: /

14:

  15: Result:

16:

  17: DECLARE

18:

  19: *

20:

  21: ERROR at line 1:

22:

  23: ORA-06502: PL/SQL: numeric or value error

24:

  25: ORA-06512: at line 5

26:

3: User-Defined Constrained Subtype Detects Out-of-Range Values

Example 3-7 User-Defined Constrained Subtype Detects Out-of-Range Values

   1: DECLARE

2:

   3: SUBTYPE Balance IS NUMBER(8,2);

4:

   5: checking_account Balance;

6:

   7: savings_account Balance;

8:

   9: BEGIN

10:

  11: checking_account := 2000.00;

12:

  13: savings_account := 1000000.00;

14:

  15: END;

16:

  17: /

18:

  19: Result:

20:

  21: DECLARE

22:

  23: *

24:

  25: ERROR at line 1:

26:

  27: ORA-06502: PL/SQL: numeric or value error: number precision too large

28:

  29: ORA-06512: at line 9

30:

4: Implicit Conversion Between Constrained Subtypes with Same Base Type

A constrained subtype can be implicitly converted to its base type, but the base type can be implicitly converted to the constrained subtype only if the value does not violate a constraint of the subtype (see Example 3-5).

A constrained subtype can be implicitly converted to another constrained subtype with the same base type only if the source value does not violate a constraint of the target subtype.

Example 3-8 Implicit Conversion Between Constrained Subtypes with Same Base Type

   1: DECLARE

2:

   3: SUBTYPE Digit IS PLS_INTEGER RANGE 0..9;

4:

   5: SUBTYPE Double_digit IS PLS_INTEGER RANGE 10..99;

6:

   7: SUBTYPE Under_100 IS PLS_INTEGER RANGE 0..99;

8:

   9: d Digit := 4;

10:

  11: dd Double_digit := 35;

12:

  13: u Under_100;

14:

  15: BEGIN

16:

  17: u := d; -- Succeeds; Under_100 range includes Digit range

18:

  19: u := dd; -- Succeeds; Under_100 range includes Double_digit range

20:

  21: dd := d; -- Raises error; Double_digit range does not include Digit range

22:

  23: END;

24:

  25: /

26:

  27: Result:

28:

  29: DECLARE

30:

  31: *

32:

  33: ERROR at line 1:

34:

  35: ORA-06502: PL/SQL: numeric or value error

36:

  37: ORA-06512: at line 12

38:

5: Implicit Conversion Between Subtypes with Base Types in Same Family

Example 3-9 Implicit Conversion Between Subtypes with Base Types in Same Family

   1: DECLARE

2:

   3: SUBTYPE Word IS CHAR(6);

4:

   5: SUBTYPE Text IS VARCHAR2(15);

6:

   7: verb Word := 'run';

8:

   9: sentence1 Text;

10:

  11: sentence2 Text := 'Hurry!';

12:

  13: sentence3 Text := 'See Tom run.';

14:

  15: BEGIN

16:

  17: sentence1 := verb; -- 3-character value, 15-character limit

18:

  19: verb := sentence2; -- 5-character value, 6-character limit

20:

  21: verb := sentence3; -- 12-character value, 6-character limit

22:

  23: END;

24:

  25: /

26:

  27: Result:

28:

  29: DECLARE

30:

  31: *

32:

  33: ERROR at line 1:

34:

  35: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

36:

  37: ORA-06512: at line 13


 http://www.cnblogs.com/kerrycode/p/3796600.html

网上经常有人问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)

http://www.linuxidc.com/Linux/2012-03/56006.htm

1. varchar2最大长度为4000字节。

2. varchar2的参数有两个：byte(默认), char

其中varchar2(10 byte)表示为可以最大容纳10个字节的字符串。

varchar2(10 char)表示为可以最大容纳10个字的字符串，而不用考虑这个字占用多少个字节，该例则可最大容纳10个汉字，或者10个英文字符，但最大不得超过4000个字节。

举例说明：

1. 创建超过4000字节的varchar2，失败。

create table test3 (v2 varchar2(4001))，错误，原因：ORA-00910: specified length too long for its datatype

2. CREATE TABLE TEST(NAME VARCHAR2(2))

INSERT INTO TEST VALUES('测试')错误，原因：ORA-12899: value too large for column "ZBB"."TEST"."NAME" (actual: 6, maximum: 2)

3. CREATE TABLE TEST(NAME VARCHAR2(2 char));

INSERT INTO TEST VALUES('测试'); 成功

INSERT INTO TEST VALUES('abcd') 失败，原因：ORA-12899: value too large for column "ZBB"."TEST"."NAME" (actual: 4, maximum: 2)

4. create table test3 (v2 varchar2(4000 char));

然后插入4000个汉字，会提示：ora-01461 can bind a long value only for insert into a long column （原因还不详）

由于使用的是utf8，测试的汉字占用3个字节，所以最多可以插入汉字1334，测试发现插入1335个汉字就会报上面的错误。

可以得出不管是汉字还是其他字符，最大不能超过4000字节。

http://blog.chinaunix.net/uid-7240278-id-3209954.html

秒客网

Oracle varchar2最大支持长度(转)

相关文章