Oracle PL/SQL Tutorial/System Tables Data Dictionary/user tab columns

30. Getting Information on Columns in Tables

You can get information about the columns in your tables from user_tab_columns.

SQL>
SQL> desc user_tab_columns;
 Name                       Null?    Type
 TABLE_NAME                 NOT NULL VARCHAR2(30)
 COLUMN_NAME                NOT NULL VARCHAR2(30)
 DATA_TYPE                           VARCHAR2(106)
 DATA_TYPE_MOD                       VARCHAR2(3)
 DATA_TYPE_OWNER                     VARCHAR2(30)
 DATA_LENGTH                         NOT NULL NUMBER
 DATA_PRECISION                      NUMBER
 DATA_SCALE                          NUMBER
 NULLABLE                            VARCHAR2(1)
 COLUMN_ID                           NUMBER
 DEFAULT_LENGTH                      NUMBER
 DATA_DEFAULT                        LONG
 NUM_DISTINCT                        NUMBER
 LOW_VALUE                           RAW(32)
 HIGH_VALUE                          RAW(32)
 DENSITY                             NUMBER
 NUM_NULLS                           NUMBER
 NUM_BUCKETS                         NUMBER
 LAST_ANALYZED                       DATE
 SAMPLE_SIZE                         NUMBER
 CHARACTER_SET_NAME                  VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                NUMBER
 GLOBAL_STATS                        VARCHAR2(3)
 USER_STATS                          VARCHAR2(3)
 AVG_COL_LEN                         NUMBER
 CHAR_LENGTH                         NUMBER
 CHAR_USED                           VARCHAR2(1)
 V80_FMT_IMAGE                       VARCHAR2(3)
 DATA_UPGRADED                       VARCHAR2(3)
 HISTOGRAM                           VARCHAR2(15)
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL> COLUMN column_name FORMAT a15
SQL> COLUMN data_type FORMAT a10
SQL> SELECT
  2  column_name, data_type, data_length, data_precision, data_scale
  3  FROM user_tab_columns
  4  WHERE table_name = "EMPLOYEE";
COLUMN_NAME     DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE
--------------- ---------- ----------- -------------- ----------
ID              VARCHAR2             4
FIRST_NAME      VARCHAR2            10
LAST_NAME       VARCHAR2            10
START_DATE      DATE                 7
END_DATE        DATE                 7
SALARY          NUMBER              22              8          2
CITY            VARCHAR2            10
DESCRIPTION     VARCHAR2            15
8 rows selected.
SQL>
SQL> clear columns;
columns cleared
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>
SQL>

30. Output report for user_tab_columns

SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create or replace procedure desc_table( p_tname in varchar2 ) AUTHID CURRENT_USER as
  2  begin
  3      dbms_output.put_line(p_tname );
  4      dbms_output.new_line;
  5
  6      for x in ( select column_name,data_type,substr(
  7               decode( data_type,"NUMBER", decode( data_precision, NULL, NULL,
  8                           "("||data_precision||","||data_scale||")" ),
  9                        data_length),1,11) data_length,
 10               decode( nullable,"Y","null","not null") nullable
 11          from user_tab_columns
 12         where table_name = upper(p_tname)
 13         order by column_id )
 14      loop
 15          dbms_output.put_line( rpad(x.column_name,31) ||
 16                                rpad(x.data_type,20)    ||
 17                                rpad(x.data_length,11) ||
 18                                x.nullable );
 19      end loop;
 20  end;
 21  /
Procedure created.
SQL>
SQL> grant execute on desc_table to public
  2  /
Grant succeeded.
SQL>
SQL> set serveroutput on format wrapped
SQL> exec desc_table( "dept" )
dept
DEPTNO                         NUMBER              (2,0)      null
DNAME                          VARCHAR2            14         null
LOC                            VARCHAR2            13         null
PL/SQL procedure successfully completed.
SQL>
SQL> drop table dept;
Table dropped.
SQL>
SQL>
SQL>