13.20 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
schemaRepresents 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.
objectRepresents the table, view, type, procedure, function, package, or synonym you wish to describe.
@db_linkConsists 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 (
NULLorNOT NULL) for each column -
Data type of columns, for example,
CHAR,DATE,LONG,LONGRAW,NUMBER,RAW,ROWID,VARCHAR2(VARCHAR),XMLType,BOOLEANNote:
Starting with Oracle Database 23ai, theDESCRIBEcommand 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
DESCRIBEcommand 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
ENCRYPTkeyword 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 OUTThe 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 INAn 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 NUMBERAn 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 NUMBERThe 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 ONDESCRIBE 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 EMAILWhen 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
EMAILThe 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 NUMBERFor 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.