What is the default precision and scale for a number in Oracle?

Question

When creating a column of type NUMBER in Oracle, you have the option of not specifying precision or scale. What to do by default if you did not specify them?

Answer 1

NUMBER (accuracy, scale)

If precision is not specified, values ​​are stored in the column. If no scale is specified, the scale is zero.

More detailed information:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

Answer 2

Type NUMBER 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) PS YES Error code: ORA-01438
 NUMBER (P) P 0 YES Error code: ORA-01438
 NUMBER (*, S) 38 S NO

Where precision is the total number of digits and the scale is the number of digits to the right or left (negative scale) of the decimal point.

Oracle defines ORA-01438 as

value that exceeds the specified accuracy allowed for this column

As noted in the table, this integrity check is active only if precision is explicitly specified. Otherwise, Oracle silently rounds the inserted or updated value using an unspecified method.

Answer 3

I believe that the default accuracy is 38, the default is zero. However, the actual instance size of this column is dynamic. It takes up as much space as needed to store the value, or max. 21 bytes.

Answer 4

Oracle stores numbers as follows: 1 byte for power, 1 byte for the first significant digit (i.e., before the separator), the rest for the remaining digits.

In digits here, Oracle stands for 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 maximum number here is 7.(7) * 10^124 , and it has 19 digits for precision or 38 decimal digits.

Answer 5

Actually, you can always check it 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';

Answer 6

I am expanding the range of answers so that people do not have to try it themselves.

This was done in 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 gives

 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