DESCRIBE

Purpose

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

Syntax

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

Terms and Clauses

Refer to the following list for a description of each term or clause:

schema

Represents the schema where the object resides. If you omit schema, SQL*Plus assumes you own object.

object

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

@net_service_name

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 Oracle8i SQL Reference.

Usage Notes

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

• datatype of columns, for example, NUMBER, CHAR, VARCHAR2 (VARCHAR), LONG, DATE, RAW, LONGRAW, or ROWID

• precision of columns (and scale, if any, for a numeric column)

When you do a DESCRIBE, VARCHAR columns are returned with a type of VARCHAR2.

The DESCRIBE command allows 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 later in this chapter.

To control the width of the data displayed, use the SET LINESIZE command. For more information, see the SET command later in this chapter.

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 they are input or output, and default values, if any

Examples

To describe the table EMP, enter

SQL> DESCRIBE EMP

SQL*Plus lists the following information:

Name                           Null?    Type
------------------------------ -------- ------------
EMPNO                          NOT NULL NUMBER(4)
ENAME                                   CHAR(10)
JOB                                     JOB(9)
MGR                                     NUMBER(4)
HIREDATE                                DATE
SAL                                     NUMBER(7,2)
COMM                                    NUMBER(7,2)
DEPTNO                                  NUMBER(2)

To describe a procedure called CUSTOMER_LOOKUP, enter

SQL> DESCRIBE customer_lookup

SQL*Plus lists the following information:

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

To create and describe the package APACK that contains the procedures aproc and bproc, enter

SQL> CREATE PACKAGE apack AS
  2  PROCEDURE aproc(P1 CHAR, P2 NUMBER);
  3  PROCEDURE bproc(P1 CHAR, P2 NUMBER);
  4  END apack;
  5  /
SQL> DESCRIBE apack

SQL*Plus lists the following information:

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

To create and describe the object type ADDRESS that contains the attributes STREET and CITY, enter

SQL> CREATE TYPE ADDRESS AS OBJECT
  2  ( STREET  VARCHAR2(20),
  3    CITY    VARCHAR2(20)
  4  );
  5  /
SQL> DESCRIBE address

SQL*Plus lists the following information:

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

To create and describe the object type EMPLOYEE that contains the attributes ENAME, EMPADDR, JOB and SAL, enter

SQL> CREATE TYPE EMPLOYEE AS OBJECT
  2  ( ENAME   VARCHAR2(30),
  3    EMPADDR  ADDRESS,
  4    JOB     VARCHAR2(20),
  5    SAL     NUMBER(7,2)
  6  );
  7  /
SQL> DESCRIBE employee

SQL*Plus lists the following information:

Name                           Null?    Type
------------------------------ -------- ------------
ENAME                                   VARCHAR2(30)
EMPADDR                                 ADDRESS
JOB                                     VARCHAR2(20)
SAL                                     NUMBER(7,2)

To create and describe the object type addr_type as a table of the object type ADDRESS, enter

SQL> CREATE TYPE addr_type IS TABLE OF ADDRESS;
  2  /
SQL> DESCRIBE addr_type

SQL*Plus lists the following information:

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

To create and describe the object type addr_varray as a varray of the object type ADDRESS, enter

SQL> CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS;
  2  /
SQL> DESCRIBE addr_varray

SQL*Plus lists the following information:

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

To create and describe the table dept_emp that contains the columns DEPTNO, PERSON and LOC, enter

SQL> CREATE TABLE dept_emp
  2  ( DEPTNO  NUMBER,
  3    PERSON  EMPLOYEE,
  4    LOC     NUMBER
  5  );
  6  /
SQL> DESCRIBE dept_emp

SQL*Plus lists the following information:

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

To create and describe the object type rational that contains the attributes NUMERATOR and DENOMINATOR, and the METHOD rational_order, enter

SQL> CREATE OR REPLACE TYPE rational AS OBJECT
  2  ( NUMERATOR   NUMBER,
  3    DENOMINATOR NUMBER,
  4    MAP MEMBER FUNCTION rational_order - 
>       RETURN DOUBLE PRECISION,
  5    PRAGMA RESTRICT_REFERENCES
  6   (rational_order, RNDS, WNDS, RNPS, WNPS) );
  7  /
SQL> CREATE OR REPLACE TYPE BODY rational AS OBJECT
  2  MAP MEMBER FUNCTION rational_order - 
>     RETURN DOUBLE PRECISION IS 
  3   BEGIN
  4    RETURN NUMERATOR/DENOMINATOR;
  5   END;
  6  END;
  7  /
SQL> DESCRIBE rational

SQL*Plus lists the following information:

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

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

To describe the object emp_object and then format the output using the SET DESCRIBE command, first enter

SQL> desc emp_object

SQL*Plus lists the following information:

 Name                                      Null     Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE                                           RECUR_PERSON
 DEPT                                               RECUR_DEPARTMENT
 START_DATE                                         DATE
 POSITION                                           VARCHAR2(1)
 SAL                                                RECUR_SALARY

To format the DESCRIBE output use the SET command as follows:

SQL> set linesize 80
SQL> set desc depth 2
SQL> set desc indent on
SQL> set desc line off

To display the settings for the object, use the SHOW command as follows:

SQL> show desc
describe DEPTH  2 LINENUM OFF INDENT ON
SQL> desc emp_object

SQL*Plus lists the following information:

 Name                                      Null     Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE                                           RECUR_PERSON
   NAME                                             VARCHAR2(20)
   ADDR                                             RECUR_ADDRESS
   ADDR1                                            RECUR_ADDRESS1
   DOB                                              DATE
   GENDER                                           VARCHAR2(10)
 DEPT                                               RECUR_DEPARTMENT
   DEPTNO                                           NUMBER
   DEPT_NAME                                        VARCHAR2(20)
   LOCATION                                         VARCHAR2(20)
 START_DATE                                         DATE
 POSITION                                           VARCHAR2(1)
 SAL                                                RECUR_SALARY
   ANNUAL_SAL                                       NUMBER(10,2)
   EMP_TYPE                                         VARCHAR2(1)
   COMM                                             NUMBER(10,2)
   PENALTY_RATE                                     NUMBER(5,2)

For more information on using the CREATE TYPE command, see your Oracle8i SQL Reference.

For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands later in this chapter.