DESCRIBE

Syntax

DESC[RIBE] {[schema.]object[@db_link]}

Lists the column definitions for the specified table, view or synonym, or the specifications for the specified function or procedure.

Terms

schema

Represents the schema where the object or permission to describe the object resides. If you omit schema and the object is not a public synonym, then the currently available schema is used.

object

Represents the table, view, type, procedure, function, package, or synonym you wish to describe.

@db_link

Consists of the database link name corresponding to the database where object exists. For more information on which privileges allow access to another table in a different schema, refer to the Oracle Database SQL Language Reference.

Usage

The description for tables, views, types, and synonyms contains the following information:

Each column's name
Whether or not null values are allowed (NULL or NOT NULL) for each column
Data type of columns, for example, CHAR, DATE, LONG, LONGRAW, NUMBER, RAW, ROWID, VARCHAR2 (VARCHAR), XMLType, BOOLEAN
Note:
Starting with Oracle Database 23ai, the DESCRIBE command also displays the Domain information (if it exists) associated with the column.
```
Name                Null?   Type
------------------  -----   -----------------------------
CUST_EMAIL                  VARCHAR2(100) DOMAIN EMAIL
```
Precision of columns (and scale, if any, for a numeric column)
Annotation information for a table or view and its columns (if enabled)
Note:
- When annotation is enabled, the DESCRIBE command displays the annotation information for a table or view and its columns.
- When annotation is disabled, there is no change to the current behavior. The annotation information is not displayed for a table or view and its columns.
  
  See Examples.

When you execute the DESCRIBE command, the VARCHAR columns are returned with a type of VARCHAR2.

The DESCRIBE command enables you to describe objects recursively to the depth level set in the SET DESCRIBE command. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. For more information, see the SET command.

To control the width of the data displayed, use the SET LINESIZE command.

Columns output for the DESCRIBE command are typically allocated a proportion of the linesize currently specified. Decreasing or increasing the linesize with the SET LINESIZE command usually makes each column proportionally smaller or larger. This may cause unexpected text wrapping in your display. For more information, see the SET command.

To enable or disable the display of annotation information, you can use the SET DESCRIBE command. With the new SET DESCRIBE option, if enabled, the DESCRIBE command displays the column metadata of a table or view with its annotations. For more information, see the SET command.

The DESCRIBE command can be used to retrieve the metadata for the BOOLEAN data type.

The description for functions and procedures contains the following information:

the type of PL/SQL object (function or procedure)
the name of the function or procedure
the type of value returned (for functions)
the argument names, types, whether input or output, and default values, if any
the ENCRYPT keyword to indicate whether or not data in a column is encrypted

Examples

You can describe the EMP_DETAILS_VIEW view as shown in the following example:

DESCRIBE EMP_DETAILS_VIEW

The following output is displayed:

 Name                                Null?    Type
 ---------------------------------- -------- ----------------
 EMPLOYEE_ID                        NOT NULL NUMBER(6)
 JOB_ID                             NOT NULL VARCHAR2(10)
 MANAGER_ID                                  NUMBER(6)
 DEPARTMENT_ID                               NUMBER(4)
 LOCATION_ID                                 NUMBER(4)
 COUNTRY_ID                                  CHAR(2)
 FIRST_NAME                                  VARCHAR2(20)
 LAST_NAME                          NOT NULL VARCHAR2(25)
 SALARY                                      NUMBER(8,2)
 COMMISSION_PCT                              NUMBER(2,2)
 DEPARTMENT_NAME                    NOT NULL VARCHAR2(30)
 JOB_TITLE                          NOT NULL VARCHAR2(35)
 CITY                               NOT NULL VARCHAR2(30)
 STATE_PROVINCE                              VARCHAR2(25)
 COUNTRY_NAME                                VARCHAR2(40)
 REGION_NAME                                 VARCHAR2(25)

You can describe the CUSTOMER_LOOKUP procedure as shown in the following example:

DESCRIBE customer_lookup

The following output is displayed:

PROCEDURE customer_lookup
Argument Name           Type     In/Out   Default?
----------------------  -------- -------- ---------
CUST_ID                 NUMBER   IN
CUST_NAME               VARCHAR2 OUT

The procedure MYPROC has a BOOLEAN parameter, as shown in the following example:

CREATE PROCEDURE myproc (col1 IN CHAR, col2 IN NUMBER, col3 BOOLEAN) AS
BEGIN   
  Null;
END;
/

You can describe the procedure MYPROC as shown in the following example:

DESCRIBE myproc

The following output is displayed:

PROCEDURE myproc
Argument Name          Type     In/Out   Default?
------------------  -------- -------- ---------
COL1                 CHAR     IN
COL2                 NUMBER   IN
COL3                 BOOLEAN  IN

The package APACK has the procedures APROC and BPROC, as shown in the following example:

CREATE PACKAGE apack AS
PROCEDURE aproc(P1 CHAR, P2 NUMBER);
PROCEDURE bproc(P1 CHAR, P2 NUMBER);
END apack;
/

Package created.

You can describe the package APACK as shown in the following example:

DESCRIBE apack

The following output is displayed:

PROCEDURE APROC
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P1                             CHAR                    IN
 P2                             NUMBER                  IN
PROCEDURE BPROC
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P1                             CHAR                    IN
 P2                             NUMBER                  IN

An object type ADDRESS has the attributes STREET and CITY, as shown in the following example:

CREATE TYPE ADDRESS AS OBJECT
  ( STREET  VARCHAR2(20),
    CITY    VARCHAR2(20)
  );
/

Type created.

You can describe the object type ADDRESS as shown in the following example:

DESCRIBE address

The following output is displayed:

 Name                             Null?    Type
 ------------------------------ -------- ----------------------
 STREET                                   VARCHAR2(20)
 CITY                                     VARCHAR2(20)

An object type EMPLOYEE has the attributes LAST_NAME, EMPADDR, JOB_ID, and SALARY, as shown in the following code snippet:

CREATE TYPE EMPLOYEE AS OBJECT
(LAST_NAME VARCHAR2(30),
EMPADDR ADDRESS,
JOB_ID VARCHAR2(20),
SALARY NUMBER(7,2)
);
/

Type created.

You can describe the object type EMPLOYEE as shown in the following example:

DESCRIBE employee

The following output is displayed:

 Name                              Null?    Type
 ------------------------------- -------- ---------------
 LAST_NAME                                 VARCHAR2(30)
 EMPADDR                                   ADDRESS
 JOB_ID                                    VARCHAR2(20)
 SALARY                                    NUMBER(7,2)

An object type ADDR_TYPE is a table of the object type ADDRESS, as shown in the following example:

CREATE TYPE addr_type IS TABLE OF ADDRESS;
/

Type created.

You can describe the object type ADDR_TYPE as shown in the following example:

DESCRIBE addr_type

The following output is displayed:

 addr_type TABLE OF ADDRESS
 Name                              Null?    Type
 --------------------------------- -------- ------------------
 STREET                                     VARCHAR2(20)
 CITY                                       VARCHAR2(20)

An object type ADDR_VARRAY is a varray of the object type ADDRESS, as shown in the following example:

CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS;
/

Type created.

You can describe the object type ADDR_VARRAY as shown in the following example:

DESCRIBE addr_varray

The following output is displayed:

 addr_varray VARRAY(10) OF ADDRESS
 Name                               Null?    Type
 ---------------------------------- -------- -----------------
 STREET                                      VARCHAR2(20)
 CITY                                        VARCHAR2(20)

The table DEPARTMENT has the columns DEPARTMENT_ID, PERSON, and LOC, as shown in the following example:

CREATE TABLE department
(DEPARTMENT_ID NUMBER,
PERSON EMPLOYEE,
LOC NUMBER
);
/

Table created.

You can describe the table DEPARTMENT as shown in the following example:

DESCRIBE department

The following output is displayed:

 Name                                    Null?    Type
 ------------------------------------- -------- --------------------
 DEPARTMENT_ID                                   NUMBER
 PERSON                                          EMPLOYEE
 LOC                                             NUMBER

An object type RATIONAL has the attributes NUMERATOR and DENOMINATOR and the method RATIONAL_ORDER, as shown in the following code snippets:

CREATE OR REPLACE TYPE rational AS OBJECT
(NUMERATOR NUMBER,
DENOMINATOR NUMBER,
MAP MEMBER FUNCTION rational_order - 
RETURN DOUBLE PRECISION,
PRAGMA RESTRICT_REFERENCES
(rational_order, RNDS, WNDS, RNPS, WNPS) );
/

CREATE OR REPLACE TYPE BODY rational AS OBJECT
MAP MEMBER FUNCTION rational_order - 
RETURN DOUBLE PRECISION IS 
BEGIN
  RETURN NUMERATOR/DENOMINATOR;
END;
END;
/

You can describe the object type RATIONAL as shown in the following example:

DESCRIBE rational

The following output is displayed:

Name                              Null?      Type
------------------------------   --------   ------------
NUMERATOR                                   NUMBER
DENOMINATOR                                 NUMBER

METHOD
------
MAP MEMBER FUNCTION RATIONAL_ORDER RETURNS NUMBER

The table PROPERTY has an XMLType column, as shown in the following example:

CREATE TABLE PROPERTY (Price NUMBER, Description SYS.XMLTYPE);

Table created.

You can describe the table PROPERTY as shown in the following example:

DESCRIBE property

The following output is displayed:

Name                                     Null?     Type
---------------------------------------  --------  ------------------
PRICE                                               NUMBER
DESCRIPTION                                         SYS.XMLTYPE

You can format the output of the DESCRIBE command by using the SET command, as shown in the following example:

SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF

You can display the settings for an object by using the SHOW command, as shown in the following example:

SHOW DESCRIBE

DESCRIBE DEPTH 2 LINENUM OFF INDENT ON

DESCRIBE employee

The following output is displayed:

 Name                                   Null?    Type
 ------------------------------------- -------- ----------------
 FIRST_NAME                                      VARCHAR2(30)
 EMPADDR                                         ADDRESS
 STREET                                          VARCHAR2(20)
 CITY                                            VARCHAR2(20)
 JOB_ID                                          VARCHAR2(20)
 SALARY                                          NUMBER(7,2)

The table ENC_TABLE has an encrypted column COL2, as shown in the following example:

CREATE TABLE enc_table (
col1 VARCHAR2(10),
col2 VARCHAR2(15) ENCRYPT,
col3 CHAR(5),
col4 CHAR(20));

Table created.

You can describe the table ENC_TABLE as shown in the following example:

DESCRIBE enc_table

The following output is displayed:

Name                                    Null?     Type
-------------------------------------  --------  --------------------
COL1                                                 VARCHAR2(10)
COL2                                                 VARCHAR2(15) ENCRYPT
COL3                                                 CHAR(5)
COL4                                                 CHAR(20)

The table CUSTOMERS has an email domain defined on a table column, as shown in the following example:

CREATE DOMAIN Email AS VARCHAR2(30)
DEFAULT ON NULL t_seq.NEXTVAL||'@gmail.com'
CONSTRAINT EMAIL_C CHECK(REGEXP_LIKE (Email, '^(\S+)\@(\S+)\.(\S+)$')) 
                         DISPLAY '---' || SUBSTR(Email, INSTR(Email, '@') + 1);
CREATE TABLE customers (Cust_id NUMBER, Cust_email VARCHAR2(100) DOMAIN Email);

You can describe the table CUSTOMERS as shown in the following example:

DESCRIBE customers

The following output is displayed:


Name                     Null?   Type
----------------------  -----   -------------------------------------
CUST_ID                             NUMBER
CUST_EMAIL                          VARCHAR2(100) DOMAIN EMAIL

When the domain name does not fit into the TYPE column, it wraps automatically to fit in the column.

Name                              Null? Type
-------------------------------  ------- ----------------------------
CUST_ID                                  NUMBER
CUST_EMAIL                               VARCHAR2(100) DOMAIN
                                         EMAIL

The table ANNOTATION_TAB has a column annotation, as shown in the following example:

CREATE TABLE annotation_tab
  (c1 NUMBER ANNOTATIONS(EmpGroup2 'Emp_Info', Hidden),
   c2 NUMBER primary key);

You can use the SET DESCRIBE command to enable displaying the column annotation information:

SET DESCRIBE ANNOTATION ON

You can describe the table ANNOTATION_TAB as shown in the following example:

DESCRIBE annotation_tab

The following output is displayed:


Name          Null?    Type       Annotation
-----------  -------   --------- ---------------------------------------
C1                     NUMBER     EmpGroup2 Emp_Info 
                                  Hidden:
C2                     NUMBER

For more information on using the CREATE TYPE command, see the Oracle Database SQL Language Reference.

For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands.