CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Mohamad Reza Motaqi
Mohamad Reza Motaqi

Reputation: 35

I choose int for column datatype but number is saved

Recently when I'm creating a table and adding columns IN SQL Developer, when I choose int or integer for column datatype and hit ok, instead if int, the number datatype is chosen and saved.also in SQL code it's saved as number datatype. has anyone encountered with this problem?

Upvotes: 1

Views: 1077

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21085

INT is not an Oracle Built-in Datatype but an ANSI datatype, that is transformed in the Oracle built-in datatype as described here.

INT transformed to NUMBER(p,0)

The important point is that in the Oracle metadata USER_TAB_COLUMNS you see only the transformed Oracle built-in type.

Technically the transformed datatype is NUMBER(*,0), which you may see in SQL Developer as the generated table DDL.

This means that the maximal possible precision in Oracle (38) is used. The scale is zero, so only integers may be stored.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142968

INT is NUMBER's subtype. Although you see it as a NUMBER, values stored into a column will be integers. For example:

SQL> create table test (col int);

Table created.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL                                                NUMBER(38)

SQL> insert into test (col) values (1.3324);

1 row created.

SQL> insert into test (col) values (0.000034);

1 row created.

SQL> insert into test (col) values (1001);

1 row created.

SQL> select * from test;

       COL
----------
         1
         0
      1001

SQL>

If you create another table whose column's datatype is NUMBER and check USER_TAB_COLUMNS, you'll see the difference in their scale (which represents number of digits left (or right, depending on its sign) of the decimal point). It is good to specify both precision and scale (so that you'd have control over it). If you omit scale (while creating columns), its default value is 0 (zero). More info here: https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

An example:

SQL> create table test1 (col number);

Table created.

SQL> select table_name, data_precision, data_scale From user_tab_columns where column_name = 'COL';

TABLE_NAME                     DATA_PRECISION DATA_SCALE
------------------------------ -------------- ----------
TEST                                                   0
TEST1

SQL>

Upvotes: 3

Related Questions