3 Ways to Check Column Data Type in Oracle

by

Here are three ways to get information about a table’s columns in Oracle, including their data types.

The DESCRIBE Command

The DESCRIBE command lists the column definitions of a table or view.

The syntax goes like this:

DESCRIBE [CATALOG] [ schema.] table [@ dblink]

Here’s an example:

DESCRIBE HR.COUNTRIES;

That gets information about the COUNTRIES table, owned by HR.

We can also shorten DESCRIBE to DESC, so that it looks like this:

DESC HR.COUNTRIES;

The ALL_TAB_COLUMNS View

Another way to get a column’s data type is to query the ALL_TAB_COLUMNS view:

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HR'
AND TABLE_NAME = 'COUNTRIES';

This view describes the columns of the tables, views, and clusters accessible to the current user.

Related views:

  • DBA_TAB_COLUMNS describes the columns of all tables, views, and clusters in the database.
  • USER_TAB_COLUMNS describes the columns of the tables, views, and clusters owned by the current user. This view does not display the OWNER column.

The ALL_TAB_COLS View

Another way to do it is with the ALL_TAB_COLS view:

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE
FROM ALL_TAB_COLS
WHERE OWNER = 'HR'
AND TABLE_NAME = 'COUNTRIES';

This view is similar to ALL_TAB_COLUMNS except that system-generated hidden columns are not filtered out.

Related views:

  • DBA_TAB_COLS describes the columns of all tables, views, and clusters in the database.
  • USER_TAB_COLS describes the columns of the tables, views, and clusters owned by the current user. This view does not display the OWNER column.