10 + 6 = 15 : On working with large numbers

An application I was working on had several tables with columns defined as a NUMBER(16,6). As we all know, this defines a numeric column as having 16 digits in total, of which 6 decimals. It also means the value can not grow beyond 10 digits. So in a NUMBER(16,6) column, the maximum value is 9999999999.999999.

So, if we insert a value of 1234567890.123456, we expect this to succeed. Lets setup a testcase.

CREATE TABLE NUMTEST(BIGNUM NUMBER(16,6));

INSERT INTO NUMTEST VALUES (1234567890.123456);

COMMIT;

SELECT * FROM NUMTEST;

But if we try it, we see this:

Hm? Only 5 decimals, and the value is rounded? How can this be? Let’s try another testcase.

DROP TABLE NUMTEST;

CREATE TABLE NUMTEST(BIGNUM NUMBER(10,6));

INSERT INTO NUMTEST VALUES(1234.123456);

COMMIT;

SELECT * FROM NUMTEST;

This looks fine to me. So what’s the problem with the 16,6 length? Let’s revert to our first testcase and this time don’t check in Toad (our IDE), but in trusty old SQL*Plus:

SQL> DROP TABLE NUMTEST;

Table dropped.

SQL> CREATE TABLE NUMTEST (BIGNUM NUMBER(16,6));

Table created.

SQL> INSERT INTO NUMTEST VALUES (1234567890.123456);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * 2   FROM NUMTEST;

   BIGNUM

----------

1234567890

Right. Display problems, let’s try with a TO_CHAR using a somewhat larger format mask:

 SQL> SELECT TO_CHAR(BIGNUM,'99999999999.99999999')
 2 FROM NUMTEST;

TO_CHAR(BIGNUM,'99999
 ---------------------
 1234567890.12345600

So the whole number is stored in the database, apparently Toad rounds this value. In the Toad options (version 12), there’s an option called “Display large numbers in Scientific Notation”. During the above tests, this option was checked. After unchecking this option, Toad displays the value like this:

Better. In both Toad and SQL*Plus, it’s also possible to set the NUMWIDTH to a value bigger than its default value of 10. Unfortunately, this setting is session-bound, so it has to be set for every new session, or added to the (g)login.sql script. Instead of setting NUMWIDTH, one can also use the COLUMN formatting options.

SQL> SET NUMWIDTH 20

SQL> SELECT * FROM NUMTEST;

              BIGNUM

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

   1234567890.123456

To wrap things up, when working with large numbers be aware of the formatting that an IDE might perform. Check the display options, and when in doubt, check the value using a format mask.