Re: Problems with numbers in ALL_TAB_COLS.

jaywalk_at_users.sourceforge.net wrote:
> 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

If I recall correctly you need to use data_scale and data_precision. In 9i and higher there is dbms_metadata.get_ddl.

-- 
Sybrand Bakker
Senior Oracle DBA