ORA-06502: PL/SQL: numeric or value error: character string buffer too small.at line 23

0
votes

Can we help me with bug?
In pl/sql I have a bug, but I can not find him ! I think problem with variable ast !

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

declare 

ast varchar2(50);
slr emp.salary%type;
max1 emp.employee_id%type;
min1 emp.employee_id%type;

begin 
select min (employee_id)
into min1
from employees;

select max (employee_id)
into max1
from employees;

for   i in min1..max1 
 loop
         select (round (salary /1000))      
    into slr
    from employees
    where employee_id = i ;

      for i in 1..slr loop
        ast := ast || '*' ;
        end loop;
        update emp set stars = ast
        where employee_id=i;
        commit;
end loop;
end;
2
oracleplsqlora-06502

2 Answers

2
votes

I don't understand why you're going about this this way. In the first place, this:

select min (employee_id)
into min1
from employees;

select max (employee_id)
into max1
from employees;

could be a single query:

SELECT MIN(employee_id), MAX(employee_id) INTO min1, max1
  FROM employees;

but I don't see any reason to use PL/SQL here at all. Why not just do this?

UPDATE emp
   SET stars = TRIM(RPAD(' ', ROUND(salary/1000) + 1, '*'));
1
votes

You should set ast to empty string outside the inner loop;

 for   i in min1..max1 
   loop
   ast:='';
   ...