Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

10.1 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.

10.1.1 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.

10.1.2 Describing Stored Functions and Procedures

DESCRIBE
may be used on stored procedures and
functions. When used on a stored
function, the DESCRIBE command returns the datatype of the return
value and gives you a list of arguments that the function expects:

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 DESCRIBE returns the following information for each argument:

The datatype Whether it is an input, output, or both The default value if there is one
The order in which the arguments are listed is the order in which
they should be passed into the function when you call it. The
DESCRIBE command doesn't show you the source code
for a function. To see that, you need to query the

all_source data
dictionary view. Example 10-1 shows how to get the
source for the terminate_employee function.

Example 10-1. Getting the source code for a PL/SQL function

SELECT text
FROM all_source
WHERE owner = USER
AND name = 'TERMINATE_EMPLOYEE'
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;

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

10.1.3 Describing Packages and Object Types

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:

DESCRIBE employee_type
Name Null? Type
----------------------------------------- -------- ------------------
EMPLOYEE_NAME VARCHAR2(40)
EMPLOYEE_HIRE_DATE DATE
EMPLOYEE_SALARY NUMBER(9,2) 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:

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. For example, to see the detailed
comments that Oracle includes in the source to the DBMS_OUTPUT
package, you can query all_source
as follows:

SELECT text
FROM all_source
WHERE name = 'DBMS_OUTPUT '
AND type = 'PACKAGE '
ORDER BY line;
TEXT
--------------------------------------------------------------------------
package dbms_output as
-- DE-HEAD <- tell SED where to cut when generating fixed package
------------
-- OVERVIEW
--
-- These procedures accumulate information in a buffer (via "put" and
-- "put_line") so that it can be retrieved out later (via "get_line" or
-- "get_lines"). If this package is disabled then all
-- calls to this package are simply ignored. This way, these routines
--------------------------------------------------------------------------
-- are only active when the client is one that is able to deal with the
-- information. This is good for debugging, or SP's that want to want
-- to display messages or reports to sql*dba or plus (like 'describing
-- procedures', etc.). The default buffer size is 20000 bytes. The
-- minimum is 2000 and the maximum is 1,000,000.
-----------
-- EXAMPLE
--
-- A trigger might want to print out some debugging information. To do
-- do this the trigger would do
. . .

Queries such as this can be handy when you don't
otherwise have any documentation at hand. Think of such queries as
instant, online documentation.

10.1.4 Why DESCRIBE Is Not Enough

Handy as
DESCRIBE is, it doesn't
return enough information. While it shows you all the columns in a
table, it leaves out many important details. 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, won't
show you the default values, won't show you the
triggers, and won't tell you anything about the
table's security.

How then do you get at this other information? One way is to install
the Oracle Enterprise Manager software. For Oracle
Database 10 g , you can use Oracle Grid Control or
Oracle Database Control, depending on whether you're
running a grid. Oracle Enterprise Manager implements a GUI-based
schema browser that will show you everything there is to see about
tables, indexes, views, triggers, and other objects. Several
third-party software packages on the market provide similar
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.