Data Types - Answers 1 of 4 (number)

 

I know several developers who feel that the Oracle number data type is too limiting. In many cases they want to explicitly stipulate types like int and smallint (integer and small integer). I believe that their desire to do so represents a misunderstanding of how the number data type is used, for example:

SQL> create table number_test (

  2    column_1    number(5),

  3    column_2    number(5,3),

  4    column_3    number

  5  );

 

Table created.

 

SQL> insert into number_test

  2    values (1.123,1.123,1.123);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select *

  2    from number_test;

 

  COLUMN_1   COLUMN_2   COLUMN_3

---------- ---------- ----------

         1      1.123      1.123

 

Notice how column 1 became a the whole number 1.  What do you think will happen when we insert a number like 1.75?

 

SQL> update number_test

  2     set column_1 = 1.75;

 

1 row updated.

 

SQL> commit;

 

Commit complete.

 

SQL> select *

  2    from number_test;

 

  COLUMN_1   COLUMN_2   COLUMN_3

---------- ---------- ----------

         2      1.123      1.123

 

SQL>

 

The number rounds itself up to the nearest whole number. It is essentially an int.  I have stipulated that it should be a number(5) which is sufficient for a smallint. If I wanted a bigint, I should specify a larger precision when defining the data type, but no scale.

 

Should I expect the same result from column 3? No! The number specification without precision or scale will represent numbers like 1.234567890... It will not round like the number(5) did. What would happen if I put a number like 123.456 into column 2?

 

 

SQL> update number_test

  2     set column_2 = 123.456;

   set column_2 = 123.456

                  *

ERROR at line 2:

ORA-01438: value larger than specified precision allowed for this column

 

 

SQL> update number_test

  2     set column_2 = 1234.56;

   set column_2 = 1234.56

                  *

ERROR at line 2:

ORA-01438: value larger than specified precision allowed for this column

 

 

SQL>

 

In each case, Oracle threw the “ORA-01438: value larger than specified precision allowed” error.

 

Finally, as of Oracle 11g the number data type will store, “positive and negative fixed numbers with absolute values from 1.0 x 10^-130 to but not including 1.0 x 10¹²⁶” (Oracle Database SQL Language Reference, #B28286-01 July 2007).