What is the default Precision and Scale for a Number in Oracle?
DatabaseOracleNumber FormattingDatabase Problem Overview
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?
Database Solutions
Solution 1 - Database
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
Solution 2 - Database
The NUMBER type can be specified in different styles:
Resulting Resulting Precision Specification Precision Scale Check Comment ――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――― NUMBER NULL NULL NO http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#sthref119">'maximum range and http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020">precision'</a>;, values are stored http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832">'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.
Solution 3 - Database
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.
Solution 4 - Database
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.
Solution 5 - Database
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';
Solution 6 - Database
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