Oracle PL/SQL/System Tables Views/all tab columns

ALL_TAB_COLUMNS view contains information about the columns in all tables accessible

  
SQL> select column_name, data_type from all_tab_columns
  2  where table_name = "COUNTRIES";
COLUMN_NAME
------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
COUNTRY_ID
CHAR
COUNTRY_NAME
VARCHAR2
REGION_ID
NUMBER

SQL>

Query all_tab_columns table and decode the data_type, data_precision, nullable columns

  
SQL>
SQL>
SQL> set verify off
SQL> set linesize 72
SQL> set pagesize 9999
SQL> set feedback on
SQL>
SQL> Prompt Datatypes for Table &1
Enter value for 1:
Datatypes for Table
SQL> column data_type format a20
SQL> column column_name heading "Column Name"
SQL> column data_type   heading "Data|Type"
SQL> column data_length heading "Data|Length"
SQL> column nullable    heading "Nullable"
SQL>
SQL> select column_name,
  2         data_type,
  3         substr(
  4         decode( data_type, "NUMBER",
  5                 decode( data_precision, NULL, NULL,
  6                  "("||data_precision||","||data_scale||")" ),
  7                                     data_length),
  8                1,11) data_length,
  9         decode( nullable, "Y", "null", "not null" ) nullable
 10  from all_tab_columns
 11  where owner = USER and table_name = upper("&1")
 12  order by column_id
 13  /
Enter value for 1:
no rows selected
SQL> --