This discussion is archived
4 Replies Latest reply: Dec 28, 2012 7:39 AM by Solomon Yakobson RSS

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

scottjhn Newbie
Currently Being Moderated
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
    vlethakula Expert
    Currently Being Moderated
    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
    ManguilibeKAO Pro
    Currently Being Moderated
    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
    John Spencer Oracle ACE
    Currently Being Moderated
    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
    Solomon Yakobson Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points