Thanks for the question, Sridhar.

Asked: November 15, 2016 - 7:15 am UTC

Last updated: October 11, 2017 - 1:29 pm UTC

Version: 11g R2

Viewed 10K+ times! This question is

You Asked

I am attempting to Solve some mathematical problems using PL/SQL and I am facing a problem with huge numbers. One such question involves calculating 2 power 1000 (2 power 3 = 2*2*2 => 8). I tried assigning the values to multiple datatypes that I knew, but all are returning with Numeric Overflow error. I know that Database is to store data rather than doing mathematical calculations, but I still would like to know if it is feasible to do such calculation in PL/SQL.
Example:
---
declare
l_number pls_integer;
l_number1 number;
l_number2 float;
begin

begin
select power(2,1000) into l_number from dual;
exception when others then
dbms_output.put_line('pls_integer->'||sqlerrm);
end;

begin
select power(2,1000) into l_number1 from dual;
exception when others then
dbms_output.put_line('number->'||sqlerrm);
end;

begin
select power(2,1000) into l_number2 from dual;
exception when others then
dbms_output.put_line('float->'||sqlerrm);
end;
end;
---

Output:
pls_integer->ORA-01426: numeric overflow
number->ORA-01426: numeric overflow
float->ORA-01426: numeric overflow

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/d5h7duzosxy14adp0j1a51rfq

and Chris said...

2 to the power 1,000? Or 1e301?

That's huge!

The maximum size of a number is 999...(38 9's) e125

You can find further details about the data type limits at:

http://docs.oracle.com/database/121/REFRN/GUID-963C79C9-9303-49FE-8F2D-C8AAF04D3095.htm#REFRN0041

And for PL/SQL data types at:

http://docs.oracle.com/database/121/LNPLS/datatypes.htm#LNPLS003

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Numeric overflow error for me some one help me to answer

raj, October 10, 2017 - 7:13 am UTC

create table fromtable (col1 number(17), col2 number(17) );
/

create table fromtable (col1 number(17), col2 number(17) );
/

insert into fromtable ( col1,col2 ) values (17100617100650001, 17100617100650100 );

/
run all sql in HR schema
step 1
run above script one by one
now the table will be created as follows

fromtable
col1 col2
17100617100650001 17100617100650010

totable ( empty table)
col1 col2

step 2

Create script as follows

Script Name :test.sql

declare
i number;
exprec fromtable%rowtype;
cursor c1 is select *from fromtable where col1 = 17100617100650001;
begin
open c1;

loop
fetch c1 into exprec;

exit when c1%notfound;

for i in exprec.col1 .. exprec.col2

loop

insert into totable(col1) values(i);

end loop;
commit work;
end loop;
close c1;
end;
/

The output should be as follows

fromtable
col1 col2
17100617100650001 null
17100617100650002 null
17100617100650003 null
17100617100650004 null
17100617100650005 null
17100617100650006 null
17100617100650007 null
17100617100650008 null
17100617100650009 null
17100617100650010 null

when run the sql

Sql> @ drive name :\test.sql

eg : Sql> d:\test\test.sql ;

err message : Numeric overflow

.
Note : When executing

October 11, 2017 - 1:29 pm UTC

When you use a for loop, Oracle Database implicitly creates a new variable of type pls_integer. This has precedence over the variable you declared at the start of the block.

You can see this in the following example:

DECLARE
  i NUMBER := 5;
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i));
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));
END;
/

Inside loop, i is 1
Inside loop, i is 2
Inside loop, i is 3
Outside loop, i is 5

This implicit variable has a type of pls_integer. Which has an upper limit of 2,147,483,647. Much lower than the numbers in your example!

To overcome this, you can use a basic loop. Increment your variable inside this and write an appropriate exit condition:

create table fromtable (col1 number(17), col2 number(17) ); 

create table totable (col1 number(17), col2 number(17) ); 

insert into fromtable ( col1,col2 ) values (17100617100650001, 17100617100650100 ); 

commit;

declare
  i        number;
  exprec   fromtable%rowtype;
  cursor c1 is
    select *
    from fromtable
    where col1 = 17100617100650001;

begin
  open c1;
  loop
    fetch c1 into exprec;
    exit when c1%notfound;
    i := exprec.col1;
    loop
      insert into totable ( col1 ) values ( i );

      i := i + 1;
      exit when i > exprec.col2;
    end loop;

  end loop;

  close c1;
end;
/

select * from totable;

COL1               COL2  
17100617100650001        
17100617100650002        
17100617100650003        
17100617100650004        
17100617100650005        
17100617100650006
...

Of course, if all you're doing is loading values from one table to another, why are you using PL/SQL at all?

The following SQL will do the trick:

roll;

insert into totable(col1)
select col1+rownum-1 from fromtable ft, 
  lateral (select * from dual connect by level <= ft.col2 - ft.col1 + 1);

select * from totable;

COL1               COL2  
17100617100650001        
17100617100650002        
17100617100650003        
17100617100650004        
17100617100650005        
17100617100650006
...

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Numeric overflow error for me some one help me to answer

More to Explore

DBMS_OUTPUT