ORA-06502:PL/SQL :numberic or value error: character string buffer too small__ORA-06502

相关文章推荐

聪明的课本 · layUI利用JQuery设置select下 ...· 10 月前 ·

知识渊博的扁豆 · 如何使用 ...· 1 年前 ·

深情的火车 · 【Python3网络爬虫开发实战】 ...· 1 年前 ·

发怒的小蝌蚪 · MultipartFile ...· 1 年前 ·

玩手机的跑步鞋 · python 3.x - module ...· 1 年前 ·

今天遇到一个错误提示：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

   3: c VARCHAR2(3 CHAR);

   5: BEGIN

   7: c := 'abc ';

   9: END;

  11: /

  13: Result:

  15: DECLARE

  17: *

  19: ERROR at line 1:

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

  23: ORA-06512: at line 4

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

   3: a SIMPLE_INTEGER := 1;

   5: b PLS_INTEGER := NULL;

   7: BEGIN

   9: a := b;

  11: END;

  13: /

  15: Result:

  17: DECLARE

  19: *

  21: ERROR at line 1:

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

  25: ORA-06512: at line 5

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

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

   1: DECLARE

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

   5: checking_account Balance;

   7: savings_account Balance;

   9: BEGIN

  11: checking_account := 2000.00;

  13: savings_account := 1000000.00;

  15: END;

  17: /

  19: Result:

  21: DECLARE

  23: *

  25: ERROR at line 1:

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

  29: ORA-06512: at line 9

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

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

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

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

   9: d Digit := 4;

  11: dd Double_digit := 35;

  13: u Under_100;

  15: BEGIN

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

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

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

  23: END;

  25: /

  27: Result:

  29: DECLARE

  31: *

  33: ERROR at line 1:

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

  37: ORA-06512: at line 12

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

   3: SUBTYPE Word IS CHAR(6);

   5: SUBTYPE Text IS VARCHAR2(15);

   7: verb Word := 'run';

   9: sentence1 Text;

  11: sentence2 Text := 'Hurry!';

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

  15: BEGIN

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

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

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

  23: END;

  25: /

  27: Result:

  29: DECLARE

  31: *

  33: ERROR at line 1:

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

  37: ORA-06512: at line 13

Error : APP-FND-01926: The custom event WHEN-LOGON-CHANGED raised unhandled exception: ORA-06502: PL

sion 11.5.9 to 12.1.3 [Release 11.5 to 12.1]Oracle Applicat...

java字符串匹配到特殊字符 java字符串寻找特定字母

1.一个字符串中可能包含有a~z中的多个字符，如有重复，如String data="zassdrttyhhkjhjoiubvnvmkweqdqwe", 求出现次数最多的那个字母及出现的次数，如有多个重复的，都求出。import java.util.ArrayList; import java.util.Collections; import j