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

Today encountered an error prompt: ORA-06502: PL/SQL: numberic or value error: character string buffer too small, the general corresponding Chinese information is: ORA-06502: PL/SQL: incorrect number or value: the string buffer is too small. During the debugging process, we found that the developer defined a variable, but the variable is assigned a value that exceeds its length in the script. The error is returned. I am used to summarizing every error message, which is conducive to learning and summarizing knowledge, as well as providing a timely solution to such problems in the future.

 

If you run the oerr ora 06502 command, the detailed Cause (Cause) and solution (Action) are not mentioned ). This is probably because there are too many such errors.

$ Oerr ora 1, 06502

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

// * Cause:

// * Action:

In the official documentation, refer. Very interesting. If you are interested, you 'd better read the source document directly.

1:Assign values or insert values that exceed the length.

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:YesSIMPLE_INTEGER SubtypeConstraints

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

·

· CHAR

·

· VARCHAR2

·

· NUMBER

·

· LONG

All of the preceding data types should t 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,Example 3-5Shows.

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: