Query all_tab_columns table and decode the data_type, data_precision, nullable columns : all_tab_columns « System Tables Views « Oracle PL / SQL

1. Oracle PL / SQL
2. System Tables Views
3. all_tab_columns

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> --

   
  

Related examples in the same category