Numeric Overflow exception with collection.EXISTS(n) method

Question

I'm getting ORA-01426: numeric overflow when running the following piece of code on Oracle 11g database:

DECLARE
    TYPE my_type
    IS
      RECORD
  (
    a NUMBER,
    b VARCHAR2(10) );
TYPE my_table
IS
  TABLE OF my_type INDEX BY BINARY_INTEGER;
  my_var my_table;
  my_num1 NUMBER;
  my_num2 NUMBER;
BEGIN
  my_num1 := 1;
  my_num2 := 781301042106240;
  IF NOT my_var.EXISTS(my_num1) THEN
    dbms_output.put_line('my num1 works');
  END IF;
  IF NOT my_var.EXISTS(my_num2) THEN
    dbms_output.put_line('my num2 works');
  END IF;
END;

It appears that EXISTS method can not handle the large number. But shouldn't it accept a NUMBER data type as input? Oracle documentation doesn't help much as it doesn't mention parameter's data type.

Does anyone knows what is max precision that EXISTS can accept?

Variables declared as BINARY_INTEGER can be assigned values up to 2**31 (2,147,483,647). — schurik, Sep 16 '13 at 11:18
Thanks schurik, you are right this is INDEX BY precision problem. The fix would be to use VARCHAR2 instead of BINARY_INTEGER... TYPE my_table IS TABLE OF my_type INDEX BY VARCHAR2(40); — Slav Pilus, Sep 16 '13 at 12:09

score 0 · Accepted Answer · edited Dec 03 '13 at 07:51

In your code you have used record data type in which you have mentioned a variable with

varchar2(10) now in the third line (my_var my_table) you are referring a table of record

type but when you have initialized a number ( my_num2 := 781301042106240;) the precision

is much beyond the variable size in record so you are getting an error please try this:

DECLARE

 TYPE my_type

    IS

      RECORD

  (

    a NUMBER,

    b VARCHAR2(10) );

TYPE my_table

IS

  TABLE OF my_type INDEX BY BINARY_INTEGER;

  my_var my_table;

  my_num1 NUMBER;

 my_num2 simple_integer :=1078130104;

BEGIN

  my_num1 := 1;

  IF NOT my_var.EXISTS(my_num1) THEN

    dbms_output.put_line('my num1 works');

  END IF;

  IF NOT my_var.EXISTS(my_num2) THEN

    dbms_output.put_line('my num2 works');

  END IF;

END;

please revert back if you are not able to follow what i am trying to say or you are not

satisfied with the answer.

Numeric Overflow exception with collection.EXISTS(n) method

1 Answers1