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
Aconstrained 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