SQL*Plus User's Guide and Reference Release 8.1.6 A75664-01 |
|
Command Reference, 20 of 52
Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function or procedure.
DESC[RIBE] {[schema.]object[@net_service_name]}
Refer to the following list for a description of each term or clause:
The description for tables, views, types and synonyms contains the following information:
When you do a DESCRIBE, VARCHAR columns are returned with a type of VARCHAR2.
The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. For more information, see the SET command later in this chapter.
To control the width of the data displayed, use the SET LINESIZE command. For more information, see the SET command later in this chapter.
The description for functions and procedures contains the following information:
To describe the table EMP, enter
SQL> DESCRIBE EMP
SQL*Plus lists the following information:
Name Null? Type ------------------------------ -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME CHAR(10) JOB JOB(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
To describe a procedure called CUSTOMER_LOOKUP, enter
SQL> DESCRIBE customer_lookup
SQL*Plus lists the following information:
PROCEDURE customer_lookup Argument Name Type In/Out Default? ---------------------- -------- -------- --------- CUST_ID NUMBER IN CUST_NAME VARCHAR2 OUT
To create and describe the package APACK that contains the procedures aproc and bproc, enter
SQL> CREATE PACKAGE apack AS 2 PROCEDURE aproc(P1 CHAR, P2 NUMBER); 3 PROCEDURE bproc(P1 CHAR, P2 NUMBER); 4 END apack; 5 / SQL> DESCRIBE apack
SQL*Plus lists the following information:
PROCEDURE aproc Argument Name Type In/Out Default? ---------------------- -------- -------- --------- P1 CHAR IN P2 NUMBER IN PROCEDURE bproc Argument Name Type In/Out Default? ---------------------- -------- -------- --------- P1 CHAR IN P2 NUMBER IN
To create and describe the object type ADDRESS that contains the attributes STREET and CITY, enter
SQL> CREATE TYPE ADDRESS AS OBJECT 2 ( STREET VARCHAR2(20), 3 CITY VARCHAR2(20) 4 ); 5 / SQL> DESCRIBE address
SQL*Plus lists the following information:
Name Null? Type ------------------------------ -------- ------------ STREET VARCHAR2(20) CITY VARCHAR2(20)
To create and describe the object type EMPLOYEE that contains the attributes ENAME, EMPADDR, JOB and SAL, enter
SQL> CREATE TYPE EMPLOYEE AS OBJECT 2 ( ENAME VARCHAR2(30), 3 EMPADDR ADDRESS, 4 JOB VARCHAR2(20), 5 SAL NUMBER(7,2) 6 ); 7 / SQL> DESCRIBE employee
SQL*Plus lists the following information:
Name Null? Type ------------------------------ -------- ------------ ENAME VARCHAR2(30) EMPADDR ADDRESS JOB VARCHAR2(20) SAL NUMBER(7,2)
To create and describe the object type addr_type as a table of the object type ADDRESS, enter
SQL> CREATE TYPE addr_type IS TABLE OF ADDRESS; 2 / SQL> DESCRIBE addr_type
SQL*Plus lists the following information:
addr_type TABLE OF ADDRESS Name Null? Type ------------------------------ -------- ------------ STREET VARCHAR2(20) CITY VARCHAR2(20)
To create and describe the object type addr_varray as a varray of the object type ADDRESS, enter
SQL> CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS; 2 / SQL> DESCRIBE addr_varray
SQL*Plus lists the following information:
addr_varray VARRAY(10) OF ADDRESS Name Null? Type ------------------------------ -------- ------------ STREET VARCHAR2(20) CITY VARCHAR2(20)
To create and describe the table dept_emp that contains the columns DEPTNO, PERSON and LOC, enter
SQL> CREATE TABLE dept_emp 2 ( DEPTNO NUMBER, 3 PERSON EMPLOYEE, 4 LOC NUMBER 5 ); 6 / SQL> DESCRIBE dept_emp
SQL*Plus lists the following information:
Name Null? Type ------------------------------ -------- ------------ DEPTNO NUMBER PERSON EMPLOYEE LOC NUMBER
To create and describe the object type rational that contains the attributes NUMERATOR and DENOMINATOR, and the METHOD rational_order, enter
SQL> CREATE OR REPLACE TYPE rational AS OBJECT 2 ( NUMERATOR NUMBER, 3 DENOMINATOR NUMBER, 4 MAP MEMBER FUNCTION rational_order - > RETURN DOUBLE PRECISION, 5 PRAGMA RESTRICT_REFERENCES 6 (rational_order, RNDS, WNDS, RNPS, WNPS) ); 7 / SQL> CREATE OR REPLACE TYPE BODY rational AS OBJECT 2 MAP MEMBER FUNCTION rational_order - > RETURN DOUBLE PRECISION IS 3 BEGIN 4 RETURN NUMERATOR/DENOMINATOR; 5 END; 6 END; 7 / SQL> DESCRIBE rational
SQL*Plus lists the following information:
Name Null? Type ------------------------------ -------- ------------ NUMERATOR NUMBER DENOMINATOR NUMBER METHOD ------ MAP MEMBER FUNCTION RATIONAL_ORDER RETURNS NUMBER
To describe the object emp_object and then format the output using the SET DESCRIBE command, first enter
SQL> DESCRIBE emp_object
SQL*Plus lists the following information:
Name Null Type ----------------------------------------- -------- ---------------------------- EMPLOYEE RECUR_PERSON DEPT RECUR_DEPARTMENT START_DATE DATE POSITION VARCHAR2(1) SAL RECUR_SALARY
To format the DESCRIBE output use the SET command as follows:
SQL> SET LINESIZE 80 SQL> SET DESCRIBE DEPTH 2 SQL> SET DESCRIBE INDENT ON SQL> SET DESCRIBE LINE OFF
To display the settings for the object, use the SHOW command as follows:
SQL> SHOW DESCRIBE describe DEPTH 2 LINENUM OFF INDENT ON SQL> DESCRIBE emp_object
SQL*Plus lists the following information:
Name Null Type ----------------------------------------- -------- ---------------------------- EMPLOYEE RECUR_PERSON NAME VARCHAR2(20) ADDR RECUR_ADDRESS ADDR1 RECUR_ADDRESS1 DOB DATE GENDER VARCHAR2(10) DEPT RECUR_DEPARTMENT DEPTNO NUMBER DEPT_NAME VARCHAR2(20) LOCATION VARCHAR2(20) START_DATE DATE POSITION VARCHAR2(1) SAL RECUR_SALARY ANNUAL_SAL NUMBER(10,2) EMP_TYPE VARCHAR2(1) COMM NUMBER(10,2) PENALTY_RATE NUMBER(5,2)
For more information on using the CREATE TYPE command, see your Oracle8i SQL Reference.
For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands later in this chapter.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|