Re: Difference between DESC & user_tab_columns

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 27 Oct 2004 19:21:02 +0200
Message-ID: <417fd86b$0$283$626a14ce@news.free.fr>

"Ed Prochak" <ed.prochak_at_magicinterface.com> a écrit dans le message de news:0jFfd.202$Qy.188_at_fe39.usenetserver.com...
> Praveen wrote:
>
> >
> > ------------------------------------------------------------------------
> >
> > Subject:
> > Difference between DESC & user_tab_columns
> > From:
> > Praveen <nospam_at_nospam.com>
> > Date:
> > Fri, 22 Oct 2004 12:46:40 -0700
> >
> >
> > Hi..All,
> >
> > When I do a DESC on a table , one of the columns shows the
> > datatype(lenght) as Number(38). But when I look at the details of the
> > column it shows something else. How does it come up with a different
> > lenght Or where does desc pick it from when you do a DESC? Is there any
> > other logic involved?
> >
> > Column Details :
> >
> >
> > TABLE_NAME COLUMN_NAME
> > ------------------------------ ------------------------------
> > DATA_TYPE DAT
>
> --------------------------------------------------------------------------------------------------

---

> > ---
> > DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE N
> > COLUMN_ID DEFAULT_LENGTH
> > ------------------------------ ----------- -------------- ---------- -
> > ---------- --------------
> > DATA_DEFAULT NUM_DISTINCT
> > --------------------------------------------------------------------------------
> > ------------
> > LOW_VALUE
> > ----------------------------------------------------------------
> > HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE
> > ----------------------------------------------------------------
> > ---------- ---------- ----------- --------- -----------
> > CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLO USE AVG_COL_LEN
> > -------------------------------------------- -------------------- ---
> > --- -----------
> > TEST_TABLE TEST_COLUMN
> > NUMBER
> > 22 0 N 4
> > 3
> > C102
> > C106 .333333333 0 1 26-MAR-04 143
> > NO NO 2
> >
> >
> >
> >
> >
> >
> > TIA
> >
> > P/
> >
>
> NUMBER(38) where 38 refers to the number of Decimal Digits
>
> compared to a data length of 22BYTES
> Why are they different? is that your question?
>
> The numbers seem about right to me. If I did my arithmetic right, 22bytes
> should hold 50-60 decimal digits. basically they are measuring different units.
>
> Obviously not all the needed info. is available in the USER_TAB_COLUMNS view.
>
>
> --
> Ed Prochak
> running http://www.faqs.org/faqs/running-faq/
> netiquette http://www.psg.com/emily.html
> --
> "Two roads diverged in a wood and I
> I took the one less travelled by
> and that has made all the difference."
> robert frost
>
>

For NUMBER you have to check data_precision column and not data_length. Here's a script equivalent to desc (with extra comments) but only for standard datatypes (parameter 1 is owner, 2 is table_name):

Set echo OFF
Set feedback OFF
Set heading OFF
Set linesize 2000
Set long 32760
Set longchunksize 120
Set newpage 0
Set pagesize 0
Set recsep OFF
Set space 0
Set trimout ON
Set trimspool ON
BTitle OFF
TTitle OFF
Prompt
Select decode(col.column_id, 1, col.table_name||decode(com.comments, NULL, '',' -- '||com.comments)||'
', '')||col.column_name||' '||col.data_type||

    decode(col.data_type,

           'NUMBER', decode(col.data_precision,
                            NULL, decode(col.data_scale, NULL, '', '(38)'),
                            '('||col.data_precision||
                                 decode(col.data_scale, NULL, ')',
                                        ','||col.data_scale||')')),
           'CHAR', '('||col.data_length||')',
           'VARCHAR2', '('||col.data_length||')',
           'NCHAR', '('||col.data_length||')',
           'NVARCHAR2', '('||col.data_length||')',
           'RAW', '('||col.data_length||')'
           )||

    decode(col.nullable, 'N', ' NOT NULL', '')||     decode(ccom.comments, NULL, '', ' -- '||ccom.comments) from dba_tab_columns col, dba_tab_comments com, dba_col_comments ccom

where ccom.owner = upper('&1')
  and ccom.table_name = upper('&2')
  and ccom.column_name = col.column_name
  and com.owner = upper('&1')
  and com.table_name = upper('&2')

  and col.owner = upper('&1')
  and col.table_name = upper('&2')
order by col.table_name, col.column_id
/

-- 
Regards
Michel Cadot

Received on Wed Oct 27 2004 - 12:21:02 CDT