The syntax for the DESCRIBE command looks like this:

DESC[RIBE] [schema.]object_name[@database_link_name]

where:

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

SQL> 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

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

SQL> DESCRIBE sys.all_users
 Name                            Null?    Type
 ------------------------------- -------- ----
 USERNAME                        NOT NULL VARCHAR2(30)
 USER_ID                         NOT NULL NUMBER
 CREATED                         NOT NULL DATE

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

DESCRIBE may also be used on stored procedures and functions. When used on a stored function, the DESCRIBE command tells you the data type of the return value, and also gives you a list of arguments that the function expects. For example:

SQL> DESCRIBE terminate_employee
FUNCTION terminate_employee RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EMP_ID                         NUMBER                  IN
 EMP_HIRE_DATE                  DATE                    OUT
 EMP_TERM_DATE                  DATE                    IN

As you can see, DESCRIBE returns the following information for each argument:

The order in which the arguments are listed is also the order in which they should be passed into the function when you call it. The DESCRIBE command does not show you the source code for a function. To see that, you need to query the ALL_SOURCE data dictionary view. The following example shows how to get the source for the TERMINATE_EMPLOYEE function:

SQL> SELECT text 
  2    FROM all_source
  3   WHERE owner = USER
  4     AND name = 'TERMINATE_EMPLOYEE'
  5  ORDER BY LINE;

TEXT
---------------------------------------------------------------------
FUNCTION terminate_employee
    (emp_id IN employee.employee_id%TYPE,
     emp_hire_date OUT employee.employee_hire_date%TYPE,
     emp_term_date IN employee.employee_termination_date%TYPE)
RETURN INTEGER AS
BEGIN
  UPDATE employee
     SET employee_termination_date = emp_term_date
   WHERE employee_id = emp_id;

  SELECT employee_hire_date INTO emp_hire_date
    FROM employee
   WHERE employee_id = emp_id;

  RETURN 1;
EXCEPTION
WHEN OTHERS THEN
  RETURN 0;
END;

19 rows selected.

Describing a procedure works the same way as describing a function. The only difference is that procedures do not have a return type.

With the release of Oracle8, the SQL*Plus DESCRIBE command was enhanced to return information about Oracle8 object types. The following example shows how this works:

SQL> DESCRIBE employee_type
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_NAME                            VARCHAR2(40)
 EMPLOYEE_HIRE_DATE                       DATE
 EMPLOYEE_SALARY                          NUMBER(9,2)

METHOD
------
 MEMBER FUNCTION TERMINATE_EMPLOYEE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EMP_ID                         NUMBER                  IN    
 EMP_TERM_DATE                  DATE                    IN

Another Oracle8 enhancement provides the ability to describe a stored package and get back a list of all functions and procedures that make up the package. This is not surprising, because objects and packages are very similar in nature. For example, you can get a list of all the entry points in the DBMS_OUTPUT package by using DESCRIBE as shown here:

SQL> DESCRIBE sys.dbms_output
PROCEDURE DISABLE
PROCEDURE ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT
PROCEDURE GET_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINE                           VARCHAR2                OUT
 STATUS                         NUMBER(38)              OUT
PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          TABLE OF VARCHAR2(255)  OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE NEW_LINE
...

As with functions and procedures, you can get at the source for a package, or for an Oracle8 object type, by querying the ALL_SOURCE view. See the previous section for an example of how this is done.

Handy as DESCRIBE is, it just doesn’t return enough information. While it shows you all the columns in a table, there are a lot of important details it leaves out. If you need to know the primary key for a table, DESCRIBE won’t tell you. If you need to know the foreign key constraints defined on a table, DESCRIBE won’t tell you that, either. DESCRIBE won’t show you the indexes. DESCRIBE won’t show you the default values. DESCRIBE won’t show you the triggers, and DESCRIBE won’t tell you anything about the table’s security.

How then do you get at this other information? One way is to install Oracle’s Enterprise Manager software. Enterprise Manager has a GUI-based schema browser that will show you everything there is to see about tables, indexes, views, triggers, and other objects. There are also several third-party software packages on the market that provide the same functionality. However, for many people, SQL*Plus is the only option available, or at least the only one conveniently available. If this is the case for you, you can still get the information you need by querying Oracle’s data dictionary.