Wednesday, February 2, 2011

Oracle View Columns Info

Today I had a need to be able to list the columns and their data types for a view that exists in Oracle.  Since I know TOAD displays this info in a grid in the schema view, I figured the easiest way to solve this problem would be to setup a trace and see how it does it…  Here are the results in case you or I need this again in the future.
select 	cols.column_id		
        ,cols.column_name as name
        ,nullable
        ,data_type as type	
        ,decode( data_type
                ,'CHAR'         ,char_length
                ,'VARCHAR'      ,char_length
                ,'VARCHAR2'     ,char_length
                ,'NCHAR'        ,char_length
                ,'NVARCHAR'     ,char_length
                ,'NVARCHAR2'    ,char_length
                ,null) as nchar_length
        ,decode( data_type ,'NUMBER' ,data_precision + data_scale, data_length ) as length
        ,data_precision as precision
        ,data_scale as scale
        ,data_length as dlength
        ,data_default  
        ,' ' comments 
        ,data_type_mod
        ,cols.char_used
        ,initCap(histogram) as histogram
        ,num_distinct
from    all_tab_columns cols
where 1=1
  and cols.table_name = :TABNAME
  and cols.owner = :OWNNAME
order by column_id