Re: Problems with numbers in ALL_TAB_COLS.

On Oct 17, 11:48 am, "sybrandb" <sybra..._at_gmail.com> wrote:
> jayw..._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, JWIf 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- Hide quoted text -- Show quoted text -

JW, if you just want to generate a series of describe table statements then on UNIX this is eary just using SQLPlus. Write SQL to generate a list of "desc owner.table_name" and spool it to a file then execute the file. Use select against sys.dual to place SQLPlus commands into the generated file.

I have used this technique to generate describes for every table in a sub-application and given the results to customers for use in determining what tables they needed to use in their adhoc queries.

HTH -- Mark D Powell -- Received on Tue Oct 17 2006 - 18:39:54 CDT