Oracle: Is there a way to get the column data types for a view?

For a table in oracle, I can query "all_tab_columns" and get table column information, like the data type, precision, whether or not the column is nullable.

In SQL Developer or TOAD, you can click on a view in the GUI and it will spit out a开发者_JS百科 list of the columns that the view returns and the same set of data (data type, precision, nullable, etc).

So my question is, is there a way to query this column definition for a view, the way you can for a table? How do the GUI tools do it?

You can use user_tab_columns (or all_tab_columns and dba_tab_columns respectively) regardless if table_name refers to a view or a table.

View columns appear in all_tab_columns, so you can query them just as you can tables.

Just simply write this query:

SQL> desc TABLE/VIEW NAME;

For example if the table/view name is "department" Then just write:

SQL> desc department;

This will give the list of all fields, it's type and default Null info of the table or view.

you can use the ANSI catalog views, should work for most RDBMs

select * 
from information_schema.columns c
join information_schema.tables t on c.table_name = t.table_name
where table_type = 'view'