RE: Data Dictionary View for View Columns?

Chris,

No problem, and thanks for the clarification. No wonder I could not see the views, as "COL" and "COLS" sound alike but I now know they are very different in what they contain.

Thanks again for everyone's input and discussion. I am sure that it helped clear things for many people on the list, myself included.

-Ari

On Mon, 21 Aug 2000, Grabowy, Chris wrote:

> Ari,
>
> Your missing something, COL and COLS, there is a difference. Note...
>
> SQL> desc col
> Name Null? Type
> ------------------------------- -------- ----
> TNAME NOT NULL VARCHAR2(30)
> COLNO NOT NULL NUMBER
> CNAME NOT NULL VARCHAR2(30)
> COLTYPE VARCHAR2(106)
> WIDTH NOT NULL NUMBER
> SCALE NUMBER
> PRECISION NUMBER
> NULLS VARCHAR2(19)
> DEFAULTVAL LONG
> CHARACTER_SET_NAME VARCHAR2(44)
>
> SQL> desc cols
> Name Null? Type
> ------------------------------- -------- ----
> TABLE_NAME NOT NULL VARCHAR2(30)
> COLUMN_NAME NOT NULL VARCHAR2(30)
> DATA_TYPE VARCHAR2(106)
> DATA_TYPE_MOD VARCHAR2(3)
> DATA_TYPE_OWNER VARCHAR2(30)
> DATA_LENGTH NOT NULL NUMBER
> DATA_PRECISION NUMBER
> DATA_SCALE NUMBER
> NULLABLE VARCHAR2(1)
> COLUMN_ID NOT NULL NUMBER
> DEFAULT_LENGTH NUMBER
> DATA_DEFAULT LONG
> NUM_DISTINCT NUMBER
> LOW_VALUE RAW(32)
> HIGH_VALUE RAW(32)
> DENSITY NUMBER
> NUM_NULLS NUMBER
> NUM_BUCKETS NUMBER
> LAST_ANALYZED DATE
> SAMPLE_SIZE NUMBER
> CHARACTER_SET_NAME VARCHAR2(44)
> CHAR_COL_DECL_LENGTH NUMBER
> GLOBAL_STATS VARCHAR2(3)
> USER_STATS VARCHAR2(3)
> AVG_COL_LEN NUMBER
>
> I am not out to offend or insult, you or anyone else. You were missing the
> difference, I should have just explained it this way, instead of the
> example.
>
> Yours sighingly,
> Chris Grabowy
>
> -----Original Message-----
> From: Ari D Kaplan [mailto:akaplan_at_interaccess.com]
> Sent: Saturday, August 19, 2000 5:39 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Data Dictionary View for View Columns?
>
>
> Hi Rachel
>
> (What are we doing on Friday night, oy!)
>
> Unfortunately, DBA_TABLES does not contain information on views. I found
> out from the list that DBA_TAB_COLUMNS (and
> ALL_TAB_COLUMNS/USER_TAB_COLUMNS)
> contain information on view columns.
>
> Someone on the list said (with a sigh) that COL does include views, but I
> still cannot get it to work:
>
> SELECT * FROM COL WHERE TNAME='ALL_VIEWS';
>
> no rows selected
>
> SELECT * FROM COL WHERE TNAME='DBA_TABLES';
>
> no rows selected
>
> This will work from ALL_TAB_COLUMNS:
>
> SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'DBA_TABLES';
> (many rows returned)
>
> Also, looking at the "sql.bsq" I was able to deduce info on the COL$ and
> OBJ$ that includes view column information.
>
> Take care, and thanks again to everyone's input today.
>
> -Ari Kaplan
> Independent Oracle DBA Consultant
>
> <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
> <-> For 380+ Oracle tips, visit: <->
> <-> <->
> <-> www.arikaplan.com <->
> <-> <->
> <-> email: akaplan_at_interaccess.com <->
> <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
>
> On Fri, 18 Aug 2000, Rachel Carmichael wrote:
>
> > Ari,
> >
> > dba_tables
> >
> >
> > Rachel
> >
> >
> > >From: Ari D Kaplan <akaplan_at_interaccess.com>
> > >Reply-To: ORACLE-L_at_fatcity.com
> > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > >Subject: Data Dictionary View for View Columns?
> > >Date: Fri, 18 Aug 2000 10:49:25 -0800
> > >
> > >Does anyone know the data dictionary view (or table) that has the list of
> > >the columns and datatypes that comprise a view? I can get the syntax for
> > >the view's SQL (in ALL_VIEWS, VIEW$) and the number of columns (VIEW$),
> > >but not the actual individual columns and datatypes.
> > >
> > >I am looking for something similar to COL but for views, and am coming up
> > >empty.
> > >
> > >Thanks,
> > >
> > >-Ari
> > >
> > >--
> > >Author: Ari D Kaplan
> > > INET: akaplan_at_interaccess.com
> > >
> > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > >San Diego, California -- Public Internet access / Mailing Lists
> > >--------------------------------------------------------------------
> > >To REMOVE yourself from this mailing list, send an E-Mail message
> > >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > >the message BODY, include a line containing: UNSUB ORACLE-L
> > >(or the name of mailing list you want to be removed from). You may
> > >also send the HELP command for other information (like subscribing).
> >
> > ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> > --
> > Author: Rachel Carmichael
> > INET: carmichr_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Author: Ari D Kaplan
> INET: akaplan_at_interaccess.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Author: Ari D Kaplan
> INET: akaplan_at_interaccess.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Mon Aug 21 2000 - 10:06:46 CDT