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
