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

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?

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.

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.

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';

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    

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.