938Part VII: Alphabetical ReferenceOracle uses National Language Support SORT. This gives the ppt

Oracle uses National Language Support SORT. This gives the collating sequence value of the given

string based on the collating sequence sort, or if omitted, the National Language Support option chosen

for the site.

REPLACE(string, if [,then])

REPLACE returns string with every occurrence of if replaced with then (zero or more characters).

If no then string is specified, then all occurrences of if are removed. See TRANSLATE.

RPAD(string,length [ ,'set'])

RPAD stands for Right PAD. It makes a string a specific length by adding a specified set of characters

to the right.

RTRIM(string [,'set'])

RTRIM stands for Right TRIM. It trims all the occurrences of any one of a set of characters off of

the right side of a string.

SOUNDEX(string)

SOUNDEX converts a string to a code value. Names with similar sounds tend to have the same

code value. You can use SOUNDEX to compare names that might have small spelling differences but

are still the same.

SUBSTR(string, start [,count])

SUBSTRing clips out a piece of a string beginning at start position and counting for count

characters from start.

TRANSLATE(string,if,then)

This TRANSLATEs a string, character by character, based on a positional matching of characters

in the if string with characters in the then string. See REPLACE.

TRIM

( [{ { LEADING | TRAILING | BOTH } [trim_character])

| trim_character

} FROM ] trim_source )

TRIM removes all the occurrences of any one of a set of characters off of the right side, left side,

or both sides of a string.

UPPER(string)

UPPER converts every letter in a string into uppercase.

USERENV(option)

USERENV returns information about the USER ENVironment, usually for an audit trail. Options

include 'ENTRYID', 'SESSIONID', and 'TERMINAL'. USERENV is still supported but has been replaced

by SYS_CONTEXT's UserEnv namespace.

VSIZE(string)

VSIZE gives the storage size of string in Oracle.

938 Part VII: Alphabetical Reference

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:938

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:28 PM

Color profile: Generic CMYK printer profile

Composite Default screen

CHARTOROWID

SEE ALSO CONVERSION FUNCTIONS, ROWIDTOCHAR

FORMAT

CHARTOROWID(string)

DESCRIPTION This stands for CHARacter TO ROW IDentifier. It changes a character string to

act like an internal Oracle row identifier, or ROWID.

CHECKPOINT

A checkpoint is a point in time at which changed blocks of data are written from the SGA to the database.

CHILD

In tree-structured data, a child is a node that is the immediate descendant of another node. The node

that the child descends from is called the parent.

CHR

SEE ALSO ASCII, CHARACTER FUNCTIONS

FORMAT

CHR(integer)

DESCRIPTION CHR will return the character with the ASCII value of integer. (integer means an

integer between 0 and 255, since the ASCII value of a character is an integer between 0 and 255.)

Those between 0 and 127 are well defined. Those above 127 (called the extended ASCII set) tend to

differ by country, application, and computer manufacturer. The letter A, for instance, is equal to the

ASCII number 65, B is 66, C is 67, and so on. The decimal point is 46. A minus sign is 45. The number

0 is 48, 1 is 49, 2 is 50, and so on.

EXAMPLE

select CHR(77), CHR(46), CHR(56) from DUAL;

C C C

- - -

M . 8

CLAUSE

A clause is a major section of a SQL statement and begins with a keyword such as select, insert, update,

delete, from, where, order by, group by, or having.

CLEAR

SEE ALSO BREAK, COLUMN, COMPUTE

FORMAT

CL[EAR] option

DESCRIPTION CLEAR clears the option.

BRE[AKS] clears breaks set by the BREAK command.

BUFF[ER] clears the current buffer.

CLEAR 939

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:939

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:29 PM

Color profile: Generic CMYK printer profile

Composite Default screen

COL[UMNS] clears options set by the COLUMN command.

COMP[UTES] clears options set by the COMPUTE command.

SCR[EEN] clears the screen.

SQL clears the SQL buffer.

TIMI[NG] deletes all timing areas created by the TIMING command.

EXAMPLES To clear computes, use this:

clear computes

To clear column definitions, use this:

clear columns

CLIENT

Client is a general term for a user, software application, or computer that requires the services, data,

or processing of another application or computer.

CLOB

CLOB is a datatype that supports character large objects. See Chapter 32.

CLOSE

SEE ALSO DECLARE, FETCH, FOR, OPEN, Chapter 27

FORMAT

CLOSE cursor;

DESCRIPTION CLOSE closes the named cursor, and releases its resources to Oracle for use

elsewhere. cursor must be the name of a currently open cursor.

Even though a cursor has been closed, its definition has not been lost. You can issue OPEN cursor

again, so long as the cursor was explicitly declared. A FOR loop will also implicitly OPEN a declared

cursor. See CURSOR FOR LOOP.

CLOSED DATABASE

A closed database is a database that is associated with an instance (the database is mounted) but not

open. Databases must be closed for some database maintenance functions. This can be accomplished

via the SQL statement ALTER DATABASE.

CLUSTER

A cluster is a means of storing together data from multiple tables, when the data in those tables contains

common information and is likely to be accessed concurrently. You can also cluster an individual table.

See CREATE CLUSTER and Chapter 20.

CLUSTER INDEX

A cluster index is one manually created after a cluster has been created and before any DML (select,

insert, update, or delete) statements can operate on the cluster. This index is created on the cluster key

columns with the SQL statement CREATE INDEX. In Oracle, you can define a hash cluster to index on

the primary key. See HASH CLUSTER.

940 Part VII: Alphabetical Reference

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:940

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:30 PM

Color profile: Generic CMYK printer profile

Composite Default screen

CLUSTER KEY

A cluster key is the column or columns that clustered tables have in common, and which is chosen as

the storage/access key. For example, two tables, BOOKSHELF and BOOKSHELF_AUTHOR, might be

clustered on the column Title. A cluster key is the same thing as a CLUSTER COLUMN.

CMDSEP (SQL*PLUS)

See SET.

COALESCE

SEE ALSO DECODE, Chapter 17

FORMAT

COALESCE(value1, value2, ...)

DESCRIPTION COALESCE will return the first non-NULL value encountered in the list of values

provided.

COALESCE (space)

To coalesce space is to unite adjoining free extents into a single extent. For example, if two 100-block

extents are next to each other within a tablespace, then they can be coalesced into a single 200-block

extent. The SMON background process will coalesce free space within tablespaces whose default

pctincrease value is non-zero. You can manually coalesce the free space within a tablespace via the

coalesce option of the alter tablespace command. See ALTER TABLESPACE.

COLLATION

SEE ALSO GROUP BY, INDEX, ORDER BY, Chapter 9

DESCRIPTION The collation or collating sequence is the order in which characters, numbers,

and symbols will be sorted because of an order by or group by clause. These sequences differ based

on the collation sequence of the computer's operating system or the national language. EBCDIC (usually

IBM and compatible mainframes) and ASCII (most other computers) sequences differ significantly. In

spite of these differences, the following rules always apply:

■ A number with a larger value is considered "greater" than a smaller one. All negative numbers

are smaller than all positive numbers. Thus, -10 is smaller than 10; -100 is smaller than -10.

■ A later date is considered greater than an earlier date.

Character strings are compared position by position, starting at the leftmost end of the string, up

to the first character that is different. Whichever string has the "greater" character in that position is

considered the greater string. One character is considered greater than another if it appears after the

other in the computer's collation sequence. Usually this means that a B is greater than an A, but the

value of A compared to a, or compared to the number 1, will differ by computer.

The collation comparison varies slightly depending on whether you are using CHAR or

VARCHAR2 strings.

If two VARCHAR2 strings are identical up to the end of the shorter one, the longer string is considered

greater. If two strings are identical and the same length, they are considered equal.

With CHAR strings, the shorter string is padded with blanks out to the length of the longer string.

If the strings are not identical after this padding, the comparison treats the padded blanks as less than

any other character, resulting in the same truth value as the VARCHAR2 comparison. If the strings are

COLLATION 941

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:941

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:31 PM

Color profile: Generic CMYK printer profile

Composite Default screen

identical after, but not before the padding, the CHAR comparison would treat them as equal whereas

the VARCHAR2 comparison would not.

In SQL it is important that literal numbers be typed without enclosing single quotes, as '10' would

be considered smaller than '6', since the quotes will cause these to be regarded as character strings

rather than numbers, and the '6' will be seen as greater than the '1' in the first position of '10'.

COLSEP (SQL*PLUS)

See SET.

COLUMN (Form 1-Definition)

A column is a subdivision of a table with a column name and a specific datatype. For example, in a

table of workers, all of the worker's ages would constitute one column. See ROW.

COLUMN (Form 2-SQL*PLUS)

SEE ALSO ALIAS, Chapters 6 and 14

FORMAT

COL[UMN] {column | expression}

[ ALI[AS] alias ]

[ CLE[AR] | DEF[AULT] ]

[ ENTMAP {ON|OFF}

[ FOLD_A[FTER]

[ FOLD_B[EFORE]

[ FOR[MAT] format ]

[ HEA[DING] text

[ JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} ] ]

[ LIKE {expression | alias} ]

[ NEWL[INE] ]

[ NEW_V[ALUE] variable ]

[ NOPRI[NT]|PRI[NT] ]

[ NUL[L] text ]

[ ON | OFF ]

[ OLD_V[ALUE] variable ]

[ WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED] ]...

DESCRIPTION COLUMN controls column and column heading formatting. The options are all

cumulative, and may be entered either simultaneously on a single line, or on separate lines at any time;

the only requirement is that the word COLUMN and the column or expression must appear on each

separate line. If one of the options is repeated, the most recent specified will be in effect. COLUMN by

itself displays all the current definitions for all columns. COLU MN with only a column or

expression will show that column's current definition.

column or expression refers to a column or expression used in the select. If an expression is used,

the expression must be entered exactly the same way that it is in the select statement. If the expression

in the select is Amount * Rate, then entering Rate * Amount in a COLUMN command will not work.

If a column or expression is given an alias in the select statement, that alias must be used here.

If you select columns with the same name from different tables (in sequential selects), a COLUMN

command for that column name will apply to both. Avoid this by assigning the columns different aliases

942 Part VII: Alphabetical Reference

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:942

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:31 PM

Color profile: Generic CMYK printer profile

Composite Default screen

in the select (not with the COLUMN command's alias clause), and entering a COLUMN command for

each column's alias.

ALIAS gives this column a new name, which then may be used to reference the column in BREAK

and COLUMN commands.

CLEAR drops the column definition.

DEFAULT leaves the column defined and ON, but drops any other options.

ENTMAP allows entity mapping to be turned on or off for selected columns in HTML output.

FOLD_A[FTER] and FOLD_B[EFORE] instruct Oracle to fold a single row of output across multiple

rows when printed. You can choose to fold the row either before or after the column.

FORMAT specifies the display format of the column. The format must be a literal like A25 or 990.99.

Without format specified, the column width is the length as defined inthe table.

A LONG column’s width defaults to the value of the SET LONG. Both regular CHAR and LONG

fields can have their width set by a format like FORMAT An, where n is an integer that is the column’s

new width.

A number column’s width defaults to the value of SET NUMWIDTH, but is changed by the width

in a format clause such as FORMAT 999,999.99. These options work with both set numformat and

the column format commands:

Format Result

9999990 The count of nines and zeros determines the maximum digits that can be displayed.

999,999,999.99 Commas and decimals will be placed in the pattern shown.

999990 Displays a zero if the value is zero.

099999 Displays numbers with leading zeros.

$99999 A dollar sign is placed in front of every number.

B99999 The display will be blank if the value is zero.

99999MI If the number is negative, a minus sign follows the number. The default is for the

negative sign to be on left.

S9999 Returns "+" for positive values, "-" for negative values.

99999PR Negative numbers are displayed within < and >.

99D99 Displays the decimal in the position indicated.

9G999 Displays the group separator in the position shown.

C9999 Displays the ISO currency symbol in this position.

L999 Displays the local currency symbol.

, Displays a comma.

. Displays a period.

9.999EEEE The display will be in scientific notation (4 E's are required).

999V99 Multiplies number by 10n, where n is the number of digits to the right of V. 999V99

turns 1234 into 123400.

RN Displays Roman numeral values, for numbers between 1 and 3999.

DATE Displays value as a date in MM/DD/YY format, for NUMBER columns used storing

Julian dates.

COLUMN (Form 2-SQL*PLUS) 943

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:943

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:32 PM

Color profile: Generic CMYK printer profile

Composite Default screen

HEADING relabels a column heading. The default is the column name or the expression. If text has

blanks or punctuation characters, it must be in single quotes. The HEADSEP character (usually '|') in

text makes SQL*PLUS begin a new line. The COLUMN command will remember the current HEADSEP

character when the column is defined, and continue to use it for this column unless the column is

redefined, even if the HEADSEP character is changed.

JUSTIFY aligns the heading over the column. By default this is RIGHT for number columns and

LEFT for anything else.

LIKE replicates the column definitions of a previously defined column for the current one, where

either the expression or label was used in the other column definition. Only those features of the other

column that have not been explicitly defined in the current column command are copied.

NEWLINE starts a new line before printing the column value.

NEW_VALUE names a variable to hold the column's value for use in the ttitle command. See

Chapter 14 for usage information.

NOPRINT and PRINT turn the column's display off or on.

NULL sets text to be displayed if the column has a NULL value. The default for this is a string of

blanks as wide as the column is defined.

OFF or ON turns all these options for a column off or on without affecting its contents.

OLD_VALUE names a variable to hold the column's value for use in the btitle command. See

Chapter 13 for usage information.

WRAPPED, WORD_WRAPPED, and TRUNCATED control how SQL*PLUS displays a heading or

string value too wide to fit the column. WRAP folds the value to the next line. WORD_WRAP folds

similarly, but breaks on words. TRUNCATED truncates the value to the width of the column definition.

COLUMN CONSTRAINT

A column constraint is an integrity constraint placed on a specific column of a table. See INTEGRITY

CONSTRAINT.

COMMAND

See STATEMENT.

COMMAND LINE

A command line is a line on a computer display where you enter a command.

COMMENT

SEE ALSO DATA DICTIONARY VIEWS, Chapter 37

FORMAT

COMMENT ON

{ TABLE [schema .] { table | view | materialized view }

| COLUMN [schema .] { table . | view . | materialized view . } column

| OPERATOR [schema .] operator

| INDEXTYPE [schema .] indextype

}

IS 'text';

944 Part VII: Alphabetical Reference

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:944

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:32 PM

Color profile: Generic CMYK printer profile

Composite Default screen

DESCRIPTION COMMENT inserts the comment text about an object or column into the data

dictionary.

You drop a comment from the database only by setting it to an empty string (set text to '').

COMMIT

To commit means to make changes to data (inserts, updates, and deletes) permanent. Before changes

are stored, both the old and new data exist so that changes can be made, or so that the data can be

restored to its prior state ("rolled back"). When a user enters the Oracle SQL command COMMIT, all

changes from that transaction are made permanent.

COMMIT (Form 1-Embedded SQL)

SEE ALSO ROLLBACK, SAVEPOINT, SET TRANSACTION, Precompiler programmer's guides

FORMAT

EXEC SQL [AT { database| :host variable}] COMMIT [WORK]

[ [COMMENT 'text' ] [RELEASE]

| FORCE 'text' [, integer ]]

DESCRIPTION You use COMMIT to commit work at various stages within a program. Without

the explicit use of COMMIT, an entire program's work will be considered one transaction, and will not

be committed until the program terminates. Any locks obtained will be held until that time, blocking

other users from access. COMMIT should be used as often as logically feasible.

WORK is optional and has no effect on usage; it is provided for ANSI compatibility. AT references

a remote database accessed by the DECLARE DATABASE command. RELEASE disconnects you from

the database, whether remote or local. FORCE manually commits an in-doubt distributed transaction.

COMMIT (Form 2-PL/SQL Statement)

SEE ALSO ROLLBACK, SAVEPOINT

FORMAT

COMMIT [WORK] [ COMMENT 'text' | FORCE 'text' [, integer] ];

DESCRIPTION COMMIT commits any changes made to the database since the last COMMIT

was executed implicitly or explicitly. WORK is optional and has no effect on usage.

COMMENT associates a text comment with the transaction. The comment can be viewed via the

data dictionary view DBA_2PC_PENDING in the event a distributed transaction fails to complete. FORCE

manually commits an in-doubt distributed transaction.

COMMUNICATIONS PROTOCOL

Communications protocol is any one of a number of standard means of connecting two computers

together so that they can share information. Protocols consist of several layers of both software and

hardware, and may connect homogeneous or heterogeneous computers.

COMMUNICATIONS PROTOCOL 945

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:945

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:33 PM

Color profile: Generic CMYK printer profile

Composite Default screen

COMPOSE

SEE ALSO CONVERSION FUNCTIONS, Chapter 10

FORMAT

COMPOSE(string)

DESCRIPTION COMPOSE takes as its argument a string in any datatype, and returns a unicode

string in its fully normalized form in the same character set as the input.

EXAMPLE To display an o with an umlaut:

select COMPOSE ( 'o' || UNISTR('\0308') ) from DUAL;

C

-

ö

COMPOSITE KEY

A composite key is a primary or foreign key composed of two or more columns.

COMPOSITE PARTITION

A composite partition involves the use of multiple partition methods, such as a range-partitioned

table in which the range partitions are then hash partitioned. See Chapter 18.

COMPRESSED INDEX

A compressed index is an index for which only enough index information is stored to identify

unique index entries; information that an index stores with the previous or following key is

"compressed" (truncated) and not stored to reduce the storage overhead required by an index.

See also NONCOMPRESSED INDEX.

COMPUTE

SEE ALSO BREAK, GROUP FUNCTIONS

FORMAT

COMP[UTE][AVG|COU[NT]|MAX[IMUM]|MIN[IMUM]|NUM[BER]|STD|SUM|VAR[IANCE]]...

[function LABEL label_name

OF {expression | column | alias} ...

ON {expression | column | alias | REPORT | ROW}...]

DESCRIPTION expression is a column or expression. COMPUTE performs computations on

columns or expressions selected from a table. It works only with the BREAK command.

By default, Oracle will use the function name (SUM, AVG, etc.) as the label for the result in the

query output. LABEL allows you to specify a label_name that overrides the default value.

OF names the column or expression whose value is to be computed. These columns also must

be in the select clause, or the COMPUTE will be ignored.

946 Part VII: Alphabetical Reference

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:946

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:34 PM

Color profile: Generic CMYK printer profile

Composite Default screen

ON coordinates the COMPUTE with the BREAK command. COMPUTE prints the computed value

and restarts the computation when the ON expression's value changes, or when a specified ROW or

REPORT break occurs. See BREAK for coordination details.

COMPUTE by itself displays the computes in effect.

AVG, MAXIMUM, MINIMUM, STD, SUM, and VARIANCE all work on expressions that are

numbers. MAXIMUM and MINIMUM also work on character expressions, but not DATEs. COUNT

and NUMBER work on any expression type.

All of these computes except NUMBER ignore rows with NULL values:

AVG Gives average value

COUNT Gives count of non-NULL values

MAXIMUM Gives maximum value

MINIMUM Gives minimum value

NUMBER Gives count of all rows returned

STD Gives standard deviation

SUM Gives sum of non-NULL values

VARIANCE Gives variance

Successive computes are simply put in order without commas, such as in this case:

compute sum avg max of Amount Rate on report

This will compute the sum, average, and maximum of both Amount and Rate for the entire report.

EXAMPLE To calculate for each Item classification and for the entire report, enter this:

break on Report on Industry skip 1

compute sum of Volume on Industry Report

CONCAT

See SET, ||.

CONCATENATED INDEX (or KEY)

A concatenated index is one that is created on more than one column of a table. It can be used to

guarantee that those columns are unique for every row in the table and to speed access to rows via

those columns. See COMPOSITE KEY.

CONCURRENCY

Concurrency is a general term meaning the access of the same data by multiple users. In database

software, concurrency requires complex software programming to assure that all users see correct

data and that all changes are made in the proper order.

CONCURRENCY 947

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:947

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:35 PM

Color profile: Generic CMYK printer profile

Composite Default screen

CONDITION

A condition is an expression whose value evaluates to either TRUE or FALSE, such as Age > 65.

CONNECT

To connect is to identify yourself to Oracle by your user name and password, in order to access the

database.

CONNECT (Form 1)

SEE ALSO COMMIT, DISCONNECT, Chapter 22

FORMAT

CON[NECT] [{user[/password] [@database] |/} [AS SYSOPER|SYSDBA}]];

DESCRIPTION You must be in SQL*PLUS to use this command, although you don't need to be

logged on to Oracle (see DISCONNECT). CONNECT commits any pending changes, logs you off of

Oracle, and logs on as the specified user. If the password is absent, you are prompted for it. It is not

displayed when you type it in response to a prompt.

@database connects to the named database. It may be on your host, or on another computer

connected via Oracle Net.

CONNECT (Form 2-Embedded SQL)

SEE ALSO COMMIT, DECLARE DATABASE, Chapter 22

FORMAT

EXEC SQL CONNECT

{ :user IDENTIFIED BY :password | :user_password }

[AT { database | :host_variable}]

[USING :connect_string]

[ALTER AUTHORIZATION :new_password

| IN {SYSDBA | SYSOPER } MODE ]

DESCRIPTION CONNECT connects a host program to a local or remote database. It may be used

more than once to connect to multiple databases. :user_password is a host variable that contains the

Oracle user name and password separated by a slash (/). Alternatively, :user and :password can be

entered separately by using the second format.

AT is used to name a database other than the default for this user. It is a required clause to

reach any databases other than the user's default database. This name can be used later in other SQL

statements with AT. This database must be first identified with DECLARE DATABASE. USING specifies

an optional Oracle Net string (such as a service name) used during the connect operation. Without the

USING string, you will be connected to the user's default database, regardless of the database named

in the AT line.

948 Part VII: Alphabetical Reference

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:948

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42a.vp

Thursday, July 25, 2002 3:53:36 PM

Color profile: Generic CMYK printer profile

Composite Default screen

CONNECT BY

SEE ALSO Chapter 13

FORMAT

SELECT expression [,expression]...

FROM [user.]table

WHERE condition

CONNECT BY [PRIOR] expression = [PRIOR] expression

START WITH expression = expression

ORDER BY expression

DESCRIPTION CONNECT BY is an operator used in a select statement to create reports on

inheritance in tree-structured data, such as company organization, family trees, and so on. START

WITH tells where in the tree to begin. These are the rules:

■ The position of PRIOR with respect to the CONNECT BY expressions determines which

expression identifies the root and which identifies the branches of the tree.

■ A where clause will eliminate individuals from the tree, but not their descendants (or

ancestors, depending on the location of PRIOR).

■ A qualification in the CONNECT BY (particularly a not equal instead of the equal sign)

will eliminate both an individual and all of its descendants.

■ CONNECT BY cannot be used with a table join in the where clause.

EXAMPLE

select Cow, Bull, LPAD(' ',6*(Level-1))||Offspring AS Offspring,

Sex, Birthdate

from BREEDING

connect by Offspring = PRIOR Cow

start with Offspring = 'DELLA'

order by Birthdate;

In this example, the following clause:

connect by Offspring = PRIOR Cow

means the offspring is the cow PRIOR to this one.

CONSTRAINT

A rule or restriction concerning a piece of data (such as a NOT NULL restriction on a column) that is

enforced at the data level, rather than the object or application level. See INTEGRITY CONSTRAINT.

constraints

SEE ALSO CREATE TABLE, INTEGRITY CONSTRAINT, Chapter 18, Chapter 20.

constraints 949

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:949

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42b.vp

Friday, July 19, 2002 1:47:10 PM

Color profile: Generic CMYK printer profile

Composite Default screen

FORMAT

constraints::=

inline_constraint::=

out_of_line_constraint::=

inline_ref_constraint::=

950 Part VII: Alphabetical Reference

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:950

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42b.vp

Friday, July 19, 2002 1:47:13 PM

Color profile: Generic CMYK printer profile

Composite Default screen

out_of_line_ref_constraint

references_clause::=

constraint_state::=

constraints 951

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:951

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42b.vp

Friday, July 19, 2002 1:47:15 PM

Color profile: Generic CMYK printer profile

Composite Default screen

using_index_clause::=

global_partitioned_index::=

index_partitioning_clause::=

segment_attributes_clause

physical_attributes_clause::=

952 Part VII: Alphabetical Reference

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1

Blind Folio 1:952

P:\010Comp\Oracle8\521-1\CD\Ventura\ch42b.vp

Friday, July 19, 2002 1:47:17 PM

Color profile: Generic CMYK printer profile

Composite Default screen