|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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
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?
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.