When creating a column of type NUMBER in Oracle, you have the option of not specifying a precision or scale. What do these default do if you don't specify them?
Asked
Active
Viewed 2.2e+01k times
7 Answers
47
NUMBER (precision, scale)
If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.
A lot more info at:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832
Angelo Marcotullio
- 801
- 6
- 6
-
2Float is very different from number type which is discrete. So, I would think it is not going to use float – paolov Sep 08 '17 at 12:33
-
10I know it's straight from the docs, but IMO the second sentence should read, "*If precision is specified but scale is not*, the scale is zero." Otherwise both apply to `number`: "no scale is specified," meaning "the scale is zero" (only integers allowed), and "a precision is not specified," meaning values are stored "as given" (floating points allowed). "As given" is the one that's actually true for `number`, but not for lack of overlap. Fortunately they clarified for 12c. – Eric Eskildsen Oct 04 '17 at 18:30
-
1I know the question in not about SSIS but a search brought me here. If I reference a `NUMBER` column without precision or scale in SSIS, it calls it a float (DT_R8). So from my perspective, @qualidafial is correct – Nick.Mc Mar 26 '19 at 01:35
-
9That untrue sentence in the documentation is exactly why people search for this. That sentence states that `NUMBER` means `NUMBER(38, 0)`, which is not true, and contradicts the example shown later. – ddekany Mar 03 '20 at 17:27
32
The NUMBER type can be specified in different styles:
Resulting Resulting Precision
Specification Precision Scale Check Comment
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
NUMBER NULL NULL NO 'maximum range and precision',
values are stored 'as given'
NUMBER(P, S) P S YES Error code: ORA-01438
NUMBER(P) P 0 YES Error code: ORA-01438
NUMBER(*, S) 38 S NO
Where the precision is the total number of digits and scale is the number of digits right or left (negative scale) of the decimal point.
Oracle specifies ORA-01438 as
value larger than specified precision allowed for this column
As noted in the table, this integrity check is only active if the precision is explicitly specified. Otherwise Oracle silently rounds the inserted or updated value using some unspecified method.
maxschlepzig
- 35,645
- 14
- 145
- 182
-
1I vote for this answer. If you define the column as NUMBER only, you will have maximum precision and maximum scale. If you define the column as NUMBER(5), the precision is 5, the scale is 0. – toree May 08 '15 at 07:19
-
What does `*` mean as *precision* in `NUMBER`? What if I define `a NUMBER(*,0)` and try to insert `123456789123456789123456789123456789123456789` (i.e 45 digits)? What will be stored? and why? What is the mathematics? – Nawaz Nov 25 '16 at 11:37
-
1@Nawaz, `*` just means that Oracle uses its default, i.e. 38. That means that `NUMBER(*, 0)` is equivalent to `NUMBER(38, 0)`. If you try to insert a number with 45 decimal digits into a `NUMBER(38,0)` column you get an error. – maxschlepzig Nov 25 '16 at 16:16
-
2@maxschlepzig: But if I insert a 45 digits number int a `NUMBER(*,0)`, then it succeeds *without* any warning, though with some rounding. So, that means `NUMBER(38,0)` and `NUMBER(*,0)` are **not** same. `INT/INTEGER/SMALLINT` seems to behave in the same was as `NUMBER(*,0)` though. – Nawaz Nov 25 '16 at 19:54
-
2@Nawaz, then the [Oracle Number semantics](https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT313) are even more complicated - with the asterisk you get 38 digits of precision ('the precision is 38'), but it seems you get integrity checking only when both parameters are explicitly specified: 'When you specify numeric fields, it is a good idea to specify the precision and scale. This provides extra integrity checking on input.' - Oracle doesn't specify how it rounds after the 38 digits - probably also platform dependent. I'll update the table next week. – maxschlepzig Nov 25 '16 at 20:20
-
@maxschlepzig: Thanks for the update, which seems reasonable to me now. If I find anything to share, I'll comment here again. :-) – Nawaz Feb 06 '17 at 17:48
13
I believe the default precision is 38, default scale is zero. However the actual size of an instance of this column, is dynamic. It will take as much space as needed to store the value, or max 21 bytes.
baretta
- 7,385
- 1
- 25
- 25
-
13This answer is not correct. You can easily give a counter example: `create table ex(n number); insert into ex(n) values(1.5); select * from ex;` You get back `1.5`. With `scale=0` you would get back `1`. This is also documented, e.g. with 11.2 Oracle documents that just `NUMBER` (without precision and without scale) results in 'maximum range and precision'. – maxschlepzig Apr 23 '15 at 21:57
12
Oracle stores numbers in the following way: 1 byte for power, 1 byte for the first significand digit (that is one before the separator), the rest for the other digits.
By digits here Oracle means centesimal digits (i. e. base 100)
SQL> INSERT INTO t_numtest VALUES (LPAD('9', 125, '9'))
2 /
1 row inserted
SQL> INSERT INTO t_numtest VALUES (LPAD('7', 125, '7'))
2 /
1 row inserted
SQL> INSERT INTO t_numtest VALUES (LPAD('9', 126, '9'))
2 /
INSERT INTO t_numtest VALUES (LPAD('9', 126, '9'))
ORA-01426: numeric overflow
SQL> SELECT DUMP(num) FROM t_numtest;
DUMP(NUM)
--------------------------------------------------------------------------------
Typ=2 Len=2: 255,11
Typ=2 Len=21: 255,8,78,78,78,78,78,78,78,78,78,78,78,78,78,78,78,78,78,78,79
As we can see, the maximal number here is 7.(7) * 10^124, and he have 19 centesimal digits for precision, or 38 decimal digits.
Quassnoi
- 413,100
- 91
- 616
- 614
5
Actually, you can always test it by yourself.
CREATE TABLE CUSTOMERS
(
CUSTOMER_ID NUMBER NOT NULL,
JOIN_DATE DATE NOT NULL,
CUSTOMER_STATUS VARCHAR2(8) NOT NULL,
CUSTOMER_NAME VARCHAR2(20) NOT NULL,
CREDITRATING VARCHAR2(10)
)
;
select column_name, data_type, nullable, data_length, data_precision, data_scale from user_tab_columns where table_name ='CUSTOMERS';
spectra
- 51
- 1
- 1
-
2This unfortunately doesn't reveal the default precision and scale of NUMBER, it just returns NULL CUSTOMER_ID DATA_TYPE:NUMBER NULLABLE:N DATA_LENGTH:22 DATA_PRECISION:(null) DATA_SCALE:(null) – Ed Randall Jun 04 '13 at 09:53
-
2However if you test by inserting very large numbers, all will be revealed: CREATE TABLE TEST_NUMBER (N1 NUMBER); INSERT INTO TEST_NUMBER (N1) VALUES (98765432109876543210987654321098765432109876543210.01234567890123456789); SELECT N1 FROM TEST_NUMBER; yields: 98765432109876543210987654321098765432110000000000 – Ed Randall Jun 04 '13 at 10:11
3
I expand on spectra‘s answer so people don’t have to try it for themselves.
This was done on Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production.
CREATE TABLE CUSTOMERS
(
CUSTOMER_ID NUMBER NOT NULL,
FOO FLOAT NOT NULL,
JOIN_DATE DATE NOT NULL,
CUSTOMER_STATUS VARCHAR2(8) NOT NULL,
CUSTOMER_NAME VARCHAR2(20) NOT NULL,
CREDITRATING VARCHAR2(10)
);
select column_name, data_type, nullable, data_length, data_precision, data_scale
from user_tab_columns where table_name ='CUSTOMERS';
Which yields
COLUMN_NAME DATA_TYPE NULLABLE DATA_LENGTH DATA_PRECISION DATA_SCALE
CUSTOMER_ID NUMBER N 22
FOO FLOAT N 22 126
JOIN_DATE DATE N 7
CUSTOMER_STATUS VARCHAR2 N 8
CUSTOMER_NAME VARCHAR2 N 20
CREDITRATING VARCHAR2 Y 10
bugybunny
- 544
- 5
- 13
1
The currently accepted answer is incorrect:
If no scale is specified, the scale is zero.
This is only true if the precision is specified.
Otherwise, for a column of type NUMBER the maximum scale is used (this maximum scale does not seem to be specified anywhere for some reason).
This table from Oracle demonstrates the behavior:
| Input Data | Specified As | Stored As |
|---|---|---|
| 123.89 | NUMBER | 123.89 |
| 123.89 | NUMBER(3) | 124 |
Jeremy Philippe
- 71
- 2
- 4