What is the default Precision and Scale for a Number in Oracle?

Matt_5024 0
Viewed 208431

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?

number-formatting oracle database
6 Answers
Angelo_Marcotullio_598660

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.

Dennis_Myren_300520

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.

user_551590

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
user_40400680
Related