4 Replies Latest reply: Dec 28, 2012 9:39 AM by Solomon Yakobson RSS

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

    scottjhn
      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
          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
            Manguilibe KAO
            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
              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
                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.