Oracle SQL Error Code ORA-06502: PL/SQL: numeric or value error: character string buffer too small at line 18

Question

I have been stuck trying to fix this error for a while and I cannot figure it out. I have researched this error code and the problem is I am trying to store a number/char that cannot fit into a variable. Everything looks fine within my code and I cannot find the problem.

Here is the Student table information:

  COLUMN_NAME DATA_TYPE      NULLABLE DATA_DEFAULT COLUMN_ID COMMENTS 
1 STU_ID      NUMBER(6,0)    Yes      (null)       1         (null) 
2 LAME        CHAR(8 BYTE)   Yes      (null)       2         (null) 
3 FAME        CHAR(8 BYTE)   Yes      (null)       3         (null)
4 MI          CHAR(2 BYTE)   Yes      (null)       4         (null) 
5 SEX         CHAR(2 BYTE)   Yes      (null)       5         (null) 
6 MAJOR       CHAR(10 BYTE)  Yes      (null)       6         (null) 
7 HOME_STATE  CHAR(5 BYTE)   Yes      (null)       7         (null)

Here is the code that I am using:

SET SERVEROUTPUT ON;
DECLARE
  class_num NUMBER(6) := 0;
  counter NUMBER := 0;
  total NUMBER := 0;
  state CHAR(5);
  in_state NUMBER := 0;
  out_state NUMBER := 0;
  Stu_name CHAR;
BEGIN
  SELECT COUNT(Home_State) INTO total FROM Student;
  FOR counter IN 1..(total)
   LOOP
    class_num := 10010 + counter;
    SELECT Home_State
     INTO state
      FROM Student
       WHERE Stu_ID = class_num;
    SELECT Fname
     INTO Stu_name
      FROM Student
       WHERE Stu_ID = class_num;
    IF state = 'Tx'
     THEN
      DBMS_OUTPUT.PUT_LINE(Stu_name||'is in state.');
      in_state := in_state + 1;
    ELSE
      DBMS_OUTPUT.PUT_LINE(Stu_name||'is out of state');
      out_state := out_state + 1;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('There are '||in_state||' students in state.');
  DBMS_OUTPUT.PUT_LINE('There are '||out_state||' students out of state.');
END;

When I run the code I get this error message:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 18
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.

I am trying to find out whether the student is in or out of Texas. I will add 1 to the in_state or out_state depending on whether the student is in or out of Texas. I will also print a message telling the user the student is in or out of Texas. When the loop is finished, I print the totals of in_state and out_state.

Littlefoot · Accepted Answer · 2020-06-17 05:42:18Z

1

I think you're wrong about error position. Should be the next command:

 SELECT Fname
     INTO Stu_name

because you declared STU_NAME CHAR; and that's not enough.

You'd rather declare variables so that they inherit columns' datatypes, e.g.

declare
  stu_name student.fname%type;

Doing so, you'd avoid such problems.

Also, try to avoid CHAR datatype unless values you stored into such a column/variable are fixed length. Because - CHAR right pads values with spaces up to the maximum length of the column so you usually have to truncate it. Pick VARCHAR2 instead.

answered Jun 17, 2020 at 5:42

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thorsten Kettner Over a year ago

Correct. Tom Kyte advises strongly against using CHAR at all. asktom.oracle.com/pls/asktom/…,

Gabriel Mongaras Over a year ago

Thank you for your help. I fixed my code and it works now. I will go through and changed all my CHAR types to VARCHAR2 types.

Koen Lostrie Over a year ago

In addition you'd also want to define your datatype size as CHAR, not as BYTE. If you have any multibyte characters in your data, those will be more than a single byte (but just a single char) and that makes debugging a ton harder.

Collectives™ on Stack Overflow

Oracle SQL Error Code ORA-06502: PL/SQL: numeric or value error: character string buffer too small at line 18

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related