3 Ways to Check Column Data Type in Oracle (2024)

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_SCALEFROM ALL_TAB_COLUMNSWHERE 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_COLUMNSdescribes the columns of all tables, views, and clusters in the database.
• USER_TAB_COLUMNSdescribes the columns of the tables, views, and clusters owned by the current user. This view does not display theOWNERcolumn.

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_SCALEFROM ALL_TAB_COLSWHERE OWNER = 'HR'AND TABLE_NAME = 'COUNTRIES';

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

Related views:

• DBA_TAB_COLSdescribes the columns of all tables, views, and clusters in the database.
• USER_TAB_COLSdescribes the columns of the tables, views, and clusters owned by the current user. This view does not display theOWNERcolumn.