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.

     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net