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
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
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));
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;
fetch c1 into exprec;
exit when c1%notfound;
i := exprec.col1;
insert into totable ( col1 ) values ( i );
i := i + 1;
exit when i > exprec.col2;
end loop;
end loop;
close c1;
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
