Find Underlying Data Type (int – integer) of Oracle Table instead of number

I can create an example table using following syntax.

CREATE TABLE "TABLE2" (
  "COLUMN_INT" INT NOT NULL,
  "COLUMN_INTEGER" INTEGER NOT NULL,
  "COLUMN_SMALLINT" SMALLINT NOT NULL,
  "COLUMN_NUMBER" NUMBER NOT NULL,
  "COLUMN_DECIMAL" DECIMAL NOT NULL);

After that I query USER_TAB_COLUMNS table using following query.

SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TABLE2';

I get following result.

COLUMN_NAME                    DATA_TYPE                                                                                                 
------------------------------ -------------
COLUMN_INT                     NUMBER                                                                                                     
COLUMN_INTEGER                 NUMBER                                                                                                     
COLUMN_SMALLINT                NUMBER                                                                                                     
COLUMN_NUMBER                  NUMBER                                                                                                     
COLUMN_DECIMAL                 NUMBER   

If I use VS.NET add-in for oracle to get DDL for this table. I get following.

-- ****** Object: Table DENEME.TABLE2 Script Date: 05.05.2013 21:43:11 ******
CREATE TABLE "TABLE2" (
  "COLUMN_INT" NUMBER NOT NULL,
  "COLUMN_INTEGER" NUMBER NOT NULL,
  "COLUMN_SMALLINT" NUMBER NOT NULL,
  "COLUMN_NUMBER" NUMBER NOT NULL,
  "COLUMN_DECIMAL" NUMBER NOT NULL,
    CHECK ("COLUMN_INT" IS NOT NULL),
    CHECK ("COLUMN_INTEGER" IS NOT NULL),
    CHECK ("COLUMN_SMALLINT" IS NOT NULL),
    CHECK ("COLUMN_NUMBER" IS NOT NULL),
    CHECK ("COLUMN_DECIMAL" IS NOT NULL))
/

If I use SQL Developer

--------------------------------------------------------
--  DDL for Table TABLE2
--------------------------------------------------------

  CREATE TABLE "DENEME"."TABLE2" 
   (    "COLUMN_INT" NUMBER(*,0), 
    "COLUMN_INTEGER" NUMBER(*,0), 
    "COLUMN_SMALLINT" NUMBER(*,0), 
    "COLUMN_NUMBER" NUMBER, 
    "COLUMN_DECIMAL" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table TABLE2
--------------------------------------------------------

  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_DECIMAL" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_NUMBER" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_SMALLINT" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_INTEGER" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_INT" NOT NULL ENABLE);

If I use TOAD.

Toad give me following DDL.

CREATE TABLE TABLE2
(
  COLUMN_INT       INTEGER                      NOT NULL,
  COLUMN_INTEGER   INTEGER                      NOT NULL,
  COLUMN_SMALLINT  INTEGER                      NOT NULL,
  COLUMN_NUMBER    NUMBER                       NOT NULL,
  COLUMN_DECIMAL   INTEGER                      NOT NULL
)
;

It seems that I can not get INT, INTEGER values, I used in my create table syntax?
Int, Integer, small int seems to be a syntactic sugar only. But Toad at least holds information about INTEGER part even though he considers smallint,int,decimal and integer same.

Is there any way to find that if a column is INT/INTEGER/SMALLINT using SQL?

According the ANSI, DB2, and SQL/DS Data Types:

SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type based on the conversions shown in the tables that follow.

INT, INTEGER and SMALLINT are all mapped to the Oracle datatype NUMBER(38). Since that’s what is stored in the dictionary, there’s no way to recover the type as specified in the original query.

If you’re not using comments on your columns for other purposes, you could store that information there when you create your tables, and retrieve it via the ALL_COL_COMMENTS (or similar) view.

NUMBER(38) versus INTEGER

a NUMBER(38) column was of a distinct datatype from INTEGER in Oracle.

Actually they are the same in the current Oracle DB versions but the INTEGER is specified as NUMBER (*,0).
I’m sure that the compare application uses the user_tab_columns to check it and the data_precision is null for the INTEGER type column.

COLUMN_NAME  DATA_TYPE  DATA_LENGTH  DATA_PRECISION  DATA_SCALE
----------------------------------------------------------------
NUMBER38       NUMBER       22           38              0
INT            NUMBER       22         (Null)            0

The following DML are equivalents and could be used to “alter the type to INTEGER” so the compare application would not see that as distinct again.

ALTER TABLE tab MODIFY(NUMBER38 INTEGER);
ALTER TABLE tab MODIFY(NUMBER38 NUMBER(*,0));

Source: http://oracleblues.blogspot.com.es/2011/04/number38-versus-integer.html