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 a precision or scale   What do these default do if you don t specify them

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  INSERT INTO t numtest VALUES  LPAD  9   125   9      2     1 row inserted  SQL gt  INSERT INTO t numtest VALUES  LPAD  7   125   7      2     1 row inserted  SQL gt  INSERT INTO t numtest VALUES  LPAD  9   126   9      2     INSERT INTO t numtest VALUES  LPAD  9   126   9     ORA-01426  numeric overflow  SQL gt  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 · Answer

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

User · Answer

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

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

Examples related to database

Examples related to oracle

Examples related to number-formatting