4 Replies Latest reply on Dec 28, 2012 3:39 PM by Solomon Yakobson

# Dynamically Find out the Integer and Decimal Data Type of a Column

Table: test (INTE number, DECI number(7,2))

Try to dynamically determine the column type.

Using select data_type from user_tab_columns. It only returns NUMBER which does not indicate if the column is an integer or decimal data type.

Is there a way to dynamically determine if a column is integer or decimal type?

Thanks
• ###### 1. Re: Dynamically Find out the Integer and Decimal Data Type of a Column
create table test (INTE number, DECI number(7,2));

select column_name,data_type,data_precision from dba_tab_columns where table_name='TEST'

COLUMN_NAME DATA_TYPE DATA_PRECISION
------------------------------ ---------------------------------------------------------------------------------------------------------- --------------
INTE NUMBER
DECI NUMBER 7
• ###### 2. Re: Dynamically Find out the Integer and Decimal Data Type of a Column
Hi,

It sounds that you are looking for something like this:
``````select dbms_metadata.get_ddl('TABLE','TEST')  get_ddl
from dual;

GET_DDL
--------------------------------------------------------------------------------

CREATE TABLE "USER1"."TEST"
(     "INTE" NUMBER,
"DECI" NUMBER(7,2)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ``````
• ###### 3. Re: Dynamically Find out the Integer and Decimal Data Type of a Column
You also need to look at the data_precision and data_scale columns of the XXX_tab_columns view. For an unconstrained number, they will be null, for an integer vallue, scale will be 0 for other constrained numbers (like your deci column), precision and scale will reflect you definition.

John
• ###### 4. Re: Dynamically Find out the Integer and Decimal Data Type of a Column
First of all your column INTE stores not just integers but any franctional numbers too:
``````SQL> create table test(INTE number, DECI number(7,2))
2  /

Table created.

SQL> insert
2    into test
3    values(
4           10.99,
5           10.99
6          )
7  /

1 row created.

SQL> select  *
2    from  test
3  /

INTE       DECI
---------- ----------
10.99      10.99

SQL>``````
If you want to declare column as integer you use either NUMBER(n) or INTEGER. And, as others already pointed out, you can check DATA_PRECISION & DATA_SCALE in data dictionary view USER/ALL/DBA_TAB_COLUMNS.

SY.