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

Question

I am experiencing below error on execution of trigger:

SQL Error: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "Test.ADD_DUMMY_RULE", line 13


create or replace
TRIGGER "ADD_DUMMY_RULE" 
BEFORE DELETE ON testTable
   FOR EACH ROW
WHEN (
old.product_id != 0
      )
DECLARE
 pcnt  NUMBER(14);

BEGIN
 pcnt := 0;

trig_pkg.rowIndex := trig_pkg.rowIndex + 1;

 -- STORING IMAGE_ID
 trig_pkg.old_image_id := :OLD.image_id;

 IF (trig_pkg.rimage_id IS NOT NULL) THEN
  FOR i IN 1..(trig_pkg.hold_imid_tablsize) LOOP
   IF (trig_pkg.old_image_id = trig_pkg.rimage_id(i)) THEN
    pcnt := pcnt + 1;
   END IF;
  END LOOP;
 END IF;

END;

Please suggest what could be the cause of issue.

Edit

datatypes:

trig_pkg.rowIndex - Number
trig_pkg.rimage_id - IS TABLE OF INTEGER INDEX BY binary_integer;
trig_pkg.hold_imid_tablsize- NUMBER(14)

What type is trig_pkg.hold_imid_tablsize? More importantly, do you have more than one trigger - the code is for ADD_DUMMY_RULE_, the error is for ADD_DUMMY_RULE (without the trailing underscore) - is that something you've introduced in the question, or is that what you're really seeing? — Alex Poole
@AlexPoole, yes it is a typo..trig_pkg.hold_imid_tablsize is Number — Ramu Pasupuleti
Does trig_pkg.hold_imid_tablsize always have a value or can it be null at some point? (Not sure if 1..null would cause ORA-06502 though?) — Colin 't Hart

schurik schurik · Accepted Answer · 2013-10-15T10:49:35

try to remove the following IF part string

IF (trig_pkg.rimage_id IS NOT NULL) THEN

INDEX BY - Tables do not need to initialized

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

1 Answers