Oracle int vs number datatypes difference and features

When you create a column as int datatype in a table, the datatype will be converted to number of precision 38. You can not define any precision to the int datatype. When you create a column with number as the datatype, the datatype will be considered as number without any precision. You can insert any kind of decimal values to the table. Please see some examples below.
SQL> create table test1 (s int);

Table created.

SQL> desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 S                                                  NUMBER(38)

SQL> drop table test1;

Table dropped.

SQL> create table test1 (s number);

Table created.

SQL>  desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 S                                                  NUMBER

You can not define any precision with int datatype
SQL>  create table test1 (s int(10));
 create table test1 (s int(10))
                          *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL> create table test1 (s number(5,2));

Table created.

SQL> desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 S                                                  NUMBER(5,2)

SQL>  insert into test1 values (10.10);

1 row created.

SQL> select * from test1;

         S
----------
    100000
      10.1
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google