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

Question

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;

David Faber · Accepted Answer · 2015-03-13 20:31:11Z

2

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, '*'));

answered Mar 13, 2015 at 20:31

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

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

1 Comment

user3528511 Over a year ago

Thank you very much ! You solved problem , which I suffer during the one week ! . Now all code is working !

Martina · Accepted Answer · 2015-03-13 20:23:08Z

1

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

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

answered Mar 13, 2015 at 20:23

Martina

9297 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related