ORA-06502: PL/SQL: numeric or value error

Question

Error:- While trying to execute the following procedure I am getting following error ORA-06502: PL/SQL: numeric or value error ORA-06512: at "proc1", line 31 ORA-06512: at line 10

code:-

create or replace 
PROCEDURE proc1 (
p_param1 IN INTEGER
)
IS

CURSOR c_cursor1 IS
SELECT  EMPLOYEE_ID FROM Employees ;

 r_EMPLOYEE_ID_tobe_updated c_cursor1%ROWTYPE;
 v_params   VARCHAR2 (32767) := '';

BEGIN
  OPEN c_cursor1;

  LOOP
   FETCH c_cursor1 INTO r_EMPLOYEE_ID_tobe_updated;
   EXIT WHEN c_cursor1%NOTFOUND;

   IF v_params = '' THEN
    v_params :=  v_params || r_EMPLOYEE_ID_tobe_updated.EMPLOYEE_ID;
   ELSE 
   v_params :=  v_params  || ', '  || r_EMPLOYEE_ID_tobe_updated.EMPLOYEE_ID;
   END IF;
  END LOOP;

  CLOSE c_cursor1;


DBMS_OUTPUT.PUT_LINE('outcome:: ' || v_params );

END proc1;

Error:- ORA-06502: PL/SQL: numeric or value error ORA-06512: at "proc1", line 31 ORA-06512: at line 10

Employee_id field details:- Employee_Id NUMBER(9) NOT NULL

Could you please help me out?

Which lines are line 10 and 31 ? And please add the DDL for EMPLOYEES to your question (not as a comment, but by editing the question itself). — Frank Schmitt
How many rows are there in Employes? What is the actual length of v_params? — Klas Lindbäck
In that case almost 1 million identifiers don't fit into 32.767 char length variable. — krokodilko
ohh yes..!!! the varchar2 declaration will be too small to accomodate the huge data..!!! could you please suggest which data type to use ? — user3437383

DallasB DallasB · Accepted Answer · 2014-03-27T13:37:36

This is not going to work. You cannot use dbms_output to put_line a single string containing 900k rows of values. Use the UTL_FILE package and write each row to a text file. Will still be a large file and take a while

ORA-06502: PL/SQL: numeric or value error

1 Answers