Lately I have seen projects using the NUMBER datatype for table columns without setting precision and scale. When I see this I always question “Is this intentional or did they just happen to forget to set the values?”. This is the problem with using implicit configuration/settings. You don’t really know if it was a mistake or intentional. So I usually go for the explicit definitions (but of course … it depends).
If you wanted to be explicit, you can use NUMBER(*). This is equivalent to just using NUMBER. But what does this really mean? It is basically the same as using NUMBER(38), except for the precision checking which occur when using NUMBER(38). Let’s look at an example:
SQL> set echo on
SQL> CREATE TABLE test1 (id NUMBER);
Table TEST1 created.
SQL> CREATE TABLE test2 (id NUMBER(*));
Table TEST2 created.
SQL> CREATE TABLE test3 (id NUMBER(38));
Table TEST3 created.
SQL> INSERT INTO test1 VALUES ('12345678991234567899123456789912345678991234567899');
1 row inserted.
SQL> INSERT INTO test2 VALUES ('12345678991234567899123456789912345678991234567899');
1 row inserted.
SQL> INSERT INTO test3 VALUES ('12345678991234567899123456789912345678991234567899');
Error starting at line : 14 in command -
INSERT INTO test3 VALUES ('12345678991234567899123456789912345678991234567899')
Error report -
ORA-01438: value larger than specified precision allowed for this column
SQL> col id for a53
SQL> SELECT to_char(id,'9999999999999999999999999999999999999999999999999999') id FROM test1;
ID
-----------------------------------------------------
12345678991234567899123456789912345678990000000000
SQL> SELECT to_char(id,'9999999999999999999999999999999999999999999999999999') id FROM test2;
ID
-----------------------------------------------------
12345678991234567899123456789912345678990000000000
As the example shows, when using NUMBER or NUMBER(*) the precision is not checked, and the precision in your INSERT is LOST!. Taking the large number into account this will probably never be an issue, but it MIGHT!
Post a Comment