Problems with numbers in ALL_TAB_COLS.

From: <jaywalk_at_users.sourceforge.net>
Date: 17 Oct 2006 08:29:04 -0700
Message-ID: <1161098944.619721.194100@i42g2000cwa.googlegroups.com>

I'm trying to take a shortcut documenting some database tables by using ALL_TAB_COLS rather than typing in the results of DESC commands. The query I'm using is:

select COLUMN_NAME,

     DECODE(DATA_TYPE,


          'NUMBER',DATA_TYPE||

               '('||
               TO_CHAR(DATA_LENGTH)||
               DECODE(DATA_SCALE,
                    NULL,')',
                    ','||TO_CHAR(DATA_SCALE)||')'),


          'VARCHAR2',DATA_TYPE||'('||TO_CHAR(DATA_LENGTH)||')',

          DATA_TYPE) DATA_TYPE,
     DECODE(NULLABLE,


          'Y','NULL',

          'N','NOT NULL',

          'HUH?') NULLS

from all_tab_cols
where owner = 'MYSCHEMA'
  and table_name = 'TABLENAME'
order by COLUMN_ID;

The problem is that I'm coming up with a lot of fields that show up as "NUMBER(22)" which are in the catalog as other things, like DECIMAL(38,0) or DOUBLE PRECISION. Is ALL_TAB_COLS the right place to look for datatypes, or should I be looking somewhere else?

Any help would be appreciated.

AdvThanxance, JW Received on Tue Oct 17 2006 - 10:29:04 CDT