Oracle Describe Table

SQL Plus is an interactive command-line utility that allows you to interact with the Oracle database using simple commands. The utility is available in standard Oracle database installation as a command-line interface or web interface.

SQL Plus provides its suite of commands and environment variables that are not accessible in other tools. It also enables you to execute PL/SQL commands in your database.

One of the useful commands provided by the SQL Plus utility is the DESCRIBE command.

In this tutorial, we will discuss how to get the column definitions of a given table in the Oracle database using the DESCRIBE command.

Oracle Describe Command

The DESCRIBE command allows us to get information about a given database table, view, or synonym by providing details about the table columns.

The command syntax is as shown:

The schema refers to the schema where the target table, view, or object is located. This is an optional parameter. If not specified, the command will use the currently available schema.

The object argument defines the target view, table, type, procedure, function, package, or synonym whose description you wish to determine.

Finally, the @db_link argument allows you to specify the database connection string where the object is located.

Information from the DESCRIBE Command

When you call the DESCRIBE command on a table, view, or synonym, the command will return information such as:

1. Available columns’ names
2. State of NULL values – are NULL values allowed or not for each column?
3. Columns data types.
4. Column precision values.

It is good to keep in mind that the command will return VARCHAR columns as VARCHAR2 data type.

For functions and stored procedures, the command returns information such as:

1. Type of function or procedure
2. Function or procedure name
3. Function return types
4. Argument names, data types, default values, and whether input or output.
5. Encryption status

Example Command Usage

The following examples demonstrate how to use the DESCRIBE command on various database objects.

Start by logging into the server using the SQL Plus tool:

Example:

Oracle Describe Table

To describe a table, use the command syntax:

You can also use the short equivalent:

For example, to describe the sample_data table in the HR database, we can use the command:

Since we are describing a table, the command should return the column names, null state, and the associated data type.

Expected output:

Conclusion

In this tutorial, you learned how to use the DESCRIBE command in Oracle SQP Plus utility to get table column definitions.