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

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.

Answer

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.

Advertisement

Answer