今天遇到一個錯誤提示：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
38:
39:
40:
