Here’s a little gem I hadn’t come across before (because I hadn’t read the upgrade manuals). Try running the following pl/sql block in 9i, and then 10g (or later):
declare v1 number(38); begin v1 := 256*256*256*256; dbms_output.put_line(v1); end; /
In 9i the result is 4294967296; but for later versions the result is:
declare * ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at line 4
It’s not a bug, it’s expected behaviour. The expression consists of integers only, so Oracle uses INTEGER arithmetic that limits the result to roughly 9 significant figures. If you want the block to work in newer versions of Oracle you have to add a decimal point to (at least) one of the operands to make Oracle use NUMBER arithmetic that takes it up to roughly 38 significant figures.