O'Reilly logo

Oracle SQL*Plus: The Definitive Guide, 2nd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The DESCRIBE Command

You may be familiar with the SQL*Plus DESCRIBE command. You can use DESCRIBE to get a list of columns in a table or view, along with its datatypes. Beginning with Oracle8, DESCRIBE may be used to see the definition of an Oracle object type or to list definitions for all the functions and procedures in a stored PL/SQL package.

Describing a Table

DESCRIBE is most often used to view the definition of a table or a view. Enter the command DESCRIBE followed by the name of the table or view you are interested in, as the following example shows:

               DESCRIBE employee

Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 EMPLOYEE_ID                               NOT NULL NUMBER
 EMPLOYEE_NAME                                      VARCHAR2(40)
 EMPLOYEE_HIRE_DATE                                 DATE
 EMPLOYEE_TERMINATION_DATE                          DATE
 EMPLOYEE_BILLING_RATE                              NUMBER(5,2)

If you aren’t the owner of the table, you can qualify the table or view name using the standard owner.table_name dot notation. This next example describes the all_users view, which is owned by the user SYS:

               DESCRIBE sys.all_users

 Name                            Null?    Type
 ------------------------------- -------- ------------
 USERNAME                        NOT NULL VARCHAR2(30)
 USER_ID                         NOT NULL NUMBER
 CREATED                         NOT NULL DATE
  . . .

DESCRIBE gives you a list of columns in the table or view, along with its resulting datatypes, lengths, and nullability. If you need to know more, such as whether a column has a default value, you will need to query the data dictionary directly. You will see how to do that later in this chapter.

Describing Stored ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required