Oracle Describe Table

Definition of Oracle Describe Table

DESCRIBE function in Oracle database can be defined as the name suggest is used to describe something which in this case will be to describe the structure of the oracle database objects like a table (list the column specifications of the table), stored procedures, stored functions, the stored package in the database (specifications of the procedure or functions), view or synonym, it can also be written as DESC (both are same) and the function is case sensitive.

Syntax:

In this section, we are going to discuss the syntax of the DESCRIBE in the Oracle database. The syntax for describe is very simple.

DESC[RIBE] {schema_name.object_name};

Parameters

• schema_name: It refers to the schema where the object is present.
• object_name: It refers to the name of the database objects which can be a table, procedure views, functions, etc.

How to Describe Table in Oracle?

As we had discussed in the definition section of this article that the DESCRIBE or DESC function basically it is used to describe the structure of the data objects. So suppose we have a table in a particular schema in a database and we want to insert a record in that particular table. The role of DESCRIBE function comes here because it allows us to know the column specifications of the table and whether it allows null values or not. So, when we execute the DESC function then it returns the column names along with the data types of the columns and their size. This helps the user to get to know the structure of the table and based on that the user can insert appropriate data into the table.

Examples of Oracle Describe Table

In the earlier sections, we got to understand the definition, syntax, and working of the DESCRIBE function. Now, in this section, we are going to look at a few examples of the describe function to have a better understanding.

1. Describe Function to Describe a Table

In this example, we are going to use the Oracle database to check how the function describes the table. For that, we will create a table with a few columns and then we will use the describe function to describe the table. Let us prepare a CREATE statement to create a table.

Query:

CREATE table employee(employee_id varchar2(25) PRIMARY KEY,
employee_name varchar2(100), city varchar2(20), salary number(5));

In the above query, we are creating the table employee with the employee_id as the primary key and three more columns.

Let us now use the DESCRIBE command to know the structure of the table.

Query:

DESCRIBE employee;

Let us now execute the query in SQL developer and check the result.

As we can see in the screenshot the output displays the column names, whether the values in the columns can be NULL or NOT NULL, and the data type along with the size of the column. The first column is NOT NULL since we had added a PRIMARY KEY constraint to the column while creating the table.

2. Describe Function to Describe a Procedure

In this example, we are going to use the Oracle database to check how the DESCRIBE function describes a stored procedure. For that, we will create a procedure with two arguments, and then we will use the describe function to describe the procedure. Let us create a procedure.

Query:

CREATE OR REPLACE PROCEDURE print_sum(
first_number NUMBER, second_number NUMBER
)
IS
r_sum number:=0;
BEGIN
r_sum := first_number + second_number;
dbms_output.put_line( r_sum );

Exception:

WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;

In this procedure, we are calculating the sum of two values and we are using the two arguments as inputs to the procedure.

Let us now execute the above query in SQL developer and create the procedure. Once we have created the procedure then now we will use the DESCRIBE function to check the structure of the procedure.

Query:

DESCRIBE print_sum;

Let us now execute the query in SQL developer and check the result.

As we can see in the above screenshot the query returns the name of the arguments, the data type of the arguments, and also whether the arguments were IN, OUT, or DEFAULT.

3. Describe Function to Describe a Package

In this example, we are going to use the Oracle database to check how the DESCRIBE function describes a stored package. For that, we will create a package with two procedures, and then we will use the describe function to describe the package. Let us create the package.

Query:

CREATE PACKAGE pck1 AS
PROCEDURE print_sum(first_number NUMBER, second_number NUMBER);
PROCEDURE print_diff(first_number NUMBER, second_number NUMBER);
END pck1;

In this query, we are creating a package with two procedures and each procedure having two arguments. Let us now execute the query and create a package. After we have created the package let us now use the DESCRIBE function to get the structure of the package.

Query:

DESCRIBE pck1;

Let us execute the query in SQL developer and check the result.

As we can see in the above screenshot the query returns the structure of each procedure present inside the package

4. Describe Function to Describe an Object

In this example, we are going to use the Oracle database to check how DESCRIBE function describes an object.

Query:

CREATE TYPE EMPLOYEE_OBJ AS OBJECT
(EMPLOYEE_NAME VARCHAR2(30),
SALARY NUMBER(7,2)
);

In this query, we are creating an object with two columns. Let us now execute the above query in SQL developer and create the object. Once we have created the object then now we will use the DESCRIBE function to check the structure of the object.

Query:

DESCRIBE EMPLOYEE_OBJ;

Let us execute the query in SQL developer and check the result.

As we can see in the screenshot of the output, the name of the column along with their data type and size is displayed.

Recommended Articles

This is a guide to Oracle Describe Table. Here we also discuss the definition and how to describe the table in oracle? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. Oracle SYSDATE()
2. Oracle COALESCE
3. PIVOT in Oracle
4. Oracle CTE