René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

August 27, 2005: On an integer not always being an integer

Until recently, I believed that an integer is a number that can only store whole numbers (or more mathematically expressed: the positive natural number 1, 2, 3..., the negative natural numbers -1, -2, -3, as well as zero). Unfortunately, that is not always the case with Oracle.
The following anonymous PL/SQL block declares two variables of datatype integer and number(38), respectively, and then tries to assign non-natural numbers to these variables: 
declare
  i integer;
  n number(38);
begin
  i := 3.14159265;
  n := 2.71828183;

  dbms_output.put_line('i: ' || i);
  dbms_output.put_line('n: ' || n);

end;
/
As expected, the variables cannot store the fractional parts of the numbers being assigned to the variables. In fact, Oracle rounds the numbers towards the closest natural number: 
i: 3
n: 3
Things change slightly, but significantly, when the declaration is not in the declare part of a PL/SQL block but in the argument list of a procedure. 
create procedure takes_integer(i in integer, p in pls_integer) is 
begin
  dbms_output.put_line('i [integer]: ' || i || ', p [pls_integer]: ' || p);
end takes_integer;
/
This procedure is called, again with two non-natural numbers: 
exec takes_integer(3.14159265, 2.71828183);
Surprisingly, the result is: 
i [integer]: 3.14159265, p [pls_integer]: 3
The argument i (that was declared as an integer) can obviously store non integers. However, a pls_integer can not.
Lesson to learn: if you want to have an integer passed to a procedure/function, use pls_integer instead of integer.
What then, does a description of the procedure tell me? 
desc takes_integer;

PROCEDURE takes_integer
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 I                              NUMBER(38)              IN
 P                              BINARY_INTEGER          IN
An integer, being an ansi datatype, is (rightly) implicitely converted into number(38). Still, a number(38) should not be able to stored 3.14159265.

Update August 29, 2005

Edgar Chupit sends me an e-mail, pointing out even more (imho counter intuitive) peculiarities about datatype precision: 
Dear Rene,

I would like to make a small comment to blog entry you have posted today.

Please note that Oracle does not allow you to explicitly specify
precision of the function parameters, but only type of the variable
see http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10807/08_subs.htm#sthref971

So when you bind variable of the same type (in your case number)
Oracle doesn't bother itself to check the precision of the variable
(only datatype) and truncate the data if necessary. In second case,
when Oracle sees that datatypes are different, than Oracle implicitly
converts datatype and precision.

Even further if the precision and scale of the variable and parameter
in the procedure is the same, Oracle doesn't try to convert/check the
precision of the variable.  For example:

create or replace procedure takes_integer(i in integer /* alias for number(38) */ ) is
  x number(38);
  y number(37);
begin
  x := i;
  y := i;
  dbms_output.put_line('x [number(38)]: ' || x || ', y [number(37)]: ' || y);
end takes_integer;
/

exec takes_integer(3.14159265);

x [number(38)]: 3,14159265, y [number(37)]: 3

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.