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.