PL/SQL and big number in 10g

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 11 Jan 2006 17:48:51 +0100
Message-ID: <43c536f2$0$22656$626a14ce@news.free.fr>

Can someone explain me the difference in PL/SQL behaviour on numbers in 10g? (Executed on Windows XP 32-bit)

SQL> select * from v$version where rownum=1; BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

1 row selected.

SQL> declare t number;
  2 begin t := 1024*1024*1024*1024;
  3 end;
  4 /

PL/SQL procedure successfully completed.

---

SQL> select * from v$version where rownum=1; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

1 row selected.

SQL> declare t number;
  2 begin t := 1024*1024*1024*1024;
  3 end;
  4 /
declare t number;
*

ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 2

SQL> select 1024*1024*1024*1024 from dual;  1024*1024*1024*1024

---

       1099511627776

1 row selected.

SQL> declare t number;
  2 begin t := 1099511627776;
  3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> declare t number;
  2 begin t := 1048576 * 1048576;
  3 end;
  4 /
declare t number;
*

ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 2

SQL> declare t number;
  2 begin
  3 t := 1048576;
  4 t := t * t;
  5 end;
  6 /

PL/SQL procedure successfully completed.

I suspect that operations on constant are now internally made with binary_integer/pls_integer that is with 32 bits integers. Can someone confirm?

Thanks
Michel Cadot Received on Wed Jan 11 2006 - 10:48:51 CST