CODEWITHSUNDEEP
oracle-databaseplsql
imdev Pune
imdev Pune

Reputation: 25

'numeric overflow' error in index in Oracle

I have written below small code snippet to showcase exactly what is the problem I am facing.

DECLARE
   TYPE a IS RECORD (a1 NUMBER);

   TYPE b IS TABLE OF a
      INDEX BY BINARY_INTEGER;

   f   b;
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Begin');
   f (1).a1 := 1;
   f (2).a1 := 2;
   f (2147483647).a1 := 3;
   DBMS_OUTPUT.put_line ('2147483647');
   f (2147483648).a1 := 4;
   DBMS_OUTPUT.put_line ('2147483648');
END;

*Getting following error when I execute above code ORA-01426: numeric overflow ORA-06512: at line 14

Here line no 14 is : f (2147483648).a1 := 4;

*

I am getting 'numeric overflow' error for index that I am using. Oracle is allowing me to have index upto 2147483647 (which is nothing but 2 ^31), but if I am going beyond this value then I am getting 'numeric overflow' error. Is there anyway to solve it? In my code i need indexing that is crossing value 4653474078

Upvotes: 2

Views: 1722

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

You can't exceed the maximum permissible limit if you use BINARY_INTEGER as the index type for your associative array. One workaround would be to have string (VARCHAR2) indexes by quoting those numbers or using TO_CHAR.

SET SERVEROUTPUT ON
DECLARE
     TYPE a IS RECORD ( a1  NUMBER );
     TYPE b IS
          TABLE OF a INDEX BY VARCHAR2(12); --upto 12 digit index
     f            b;
     v_bigindex   NUMBER := 4653474078;
BEGIN
     f('2147483648').a1 := 3;
     f(TO_CHAR(v_bigindex )).a1 := 4;

     dbms_output.put_line('Index = '   || 2147483648 || 
                         ' element = ' || f('2147483648').a1
                         );
     dbms_output.put_line('Index = '   || v_bigindex || 
                         ' element = ' || f(v_bigindex).a1
                         );
END;
/


Index = 2147483648 element = 3
Index = 4653474078 element = 4


PL/SQL procedure successfully completed.

Upvotes: 3

Related Questions