Re: USER_TAB_COLUMNS vs DESCRIBE

Hello,

here a short way to fetch numeric columns :

select
'NUMBER(' || to_char ( data_precision ) || ','|| to_char( data_scale )||')' from user_tab_columns where data_type = 'NUMBER';

Attention :
View-Columns are also represented in USER_TAB_COLUMNS, they will return "NUMBER(,)", because no
precision and scale information available. --> You have to check out if the content of table_name represents a TABLE or a VIEW.

If Scale is 0 then the query returns "NUMBER(x,0)" (x=data_precision) but that's a valid syntax.

Greetinx, hope to help you

Steve Lucord <steve.a.lucord_at_lmco.com> schrieb in im Newsbeitrag: 38458744.EFE795CC_at_lmco.com...
> I am attempting to create a data dictionary report via SQL*PLUS. Using
> the USER_TAB_COLUMNS view I am able to get the following output:
>
>
> Data Data Data
> Table Name Column
> Name Type Length Scale
> Null
> ------------------------- ------------------------------ ---------
> ------ ----- ---- ----
> WTW_PERFORMANCE ACTUAL_90_DAY_RETENTION NUMBER 22
> 2 Y
>
> DESCRIBE provides the following information for same table/column name:
>
> Name Null? Type
> ------------------------------- -------- ----
>
> ACTUAL_90_DAY_RETENTION NUMBER(3,2)
>
> Does anyone know a way to format the Data Type/Data Length/Data Scale as
> NUMBER(3,2) using SQL*PLUS?
>
> Thanks,
>
> Steve Lucord
>
Received on Wed Dec 01 1999 - 19:09:14 CST