06502. 00000 - "PL/SQL: numeric or value error%s"

Question

Hi I have one task to solve. I wrote this code

CREATE OR REPLACE function hr_funct_task4(p_emp_id number)
  return varchar
  as
    v_emp_last_name varchar2(25);
    v_count         number;
    no_emp_id       exception;
    null_emp_id     exception;
  begin

    if p_emp_id is null
    then
      raise no_emp_id;
    else
      select count(*)
        into v_count
          from jobs j
            inner join  employees e
            on e.job_id = j.job_id
        where j.min_salary > 10000
          and e.employee_id = p_emp_id;

      if v_count <> 0
      then
        select e.last_name
          into v_emp_last_name
          from jobs j
            inner join  employees e
              on e.job_id = j.job_id
          where j.min_salary > 10000
            and e.employee_id = p_emp_id;
      else
        v_emp_last_name := 'No emp_id has salsry > 10000';
      end if;
    end if;

    return v_emp_last_name;

  exception
    when no_emp_id then raise_application_error(-20001, 'No emp found! Please, input another emp_id.');
    when null_emp_id then raise_application_error(-20001, 'Input emp_id is null. Please, input not null value.');
  end;

call function:

begin
  for i in
  (
    select hr_funct_task4(e.employee_id) as last_name
      from employees e
  )
  loop
    dbms_output.put_line(i.last_name);
  end loop;
end;

and I received the correct values but also an error 06502. 00000 - "PL/SQL: numeric or value error%s", ORA-06512: on line 4.

Can you please advise me what i am doing wrong? Thank you

Answer 1

You declared

v_emp_last_name varchar2(25);

and later modified it to

v_emp_last_name employees.last_name%type;

(I presume that last_name column isn't larger than 25 characters, is it?)

---

Then, this is what you do:

 else
    v_emp_last_name := 'No emp_id has salsry > 10000';

Guess what?

SQL> select length('No emp_id has salsry > 10000') from dual;

LENGTH('NOEMP_IDHASSALSRY>10000')
---------------------------------
                               28         --> 28 won't fit into 25

SQL>