SQL Language Reference

This section will provide a basic understanding of SQL Language Commands.
Most of the information contained in this section is DIRECTLY extracted from
``ORACLE7 Server SQL Language Reference Manual'' and all credit should be given
to ORACLE. If you require more detailed information than provided in this
section, consult the ``ORACLE7 Server SQL Language Reference Manual''.

SQL (Structured Query Language, pronounced ``sequel'') is the set of commands
that all programs and users must use to access data within the ORACLE database.
Application programs and ORACLE tools often allow users to access the database
without directly using SQL, but these applications in turn must use SQL when
executing the user's request.

When you issue a SQL statement, you can include one or more tabs, carriage
returns, spaces, or comments anywhere a space occurs within the definition of
the command. Thus, ORACLE evaluates the following statements in the same manner:

A schema is a collection of logical structures of data, or schema objects.
A schema is owned by a database user and has the same name as that user.
Each user owns a single schema. Schema objects can be created and
manipulated with SQL and include the following types of objects:

indexes

packages

stand-alone procedures

sequences

synonyms

tables

database triggers

views

Other types of objects are also stored in the database and can be created
and manipulated with SQL, but are not contained in a schema:

profiles

roles

rollback segments

tablespaces

You must provide names for most types of objects when you create them.
These names must follow the rules listed below. Some objects are made up
of parts that you must also name, i.e., columns in a table or view.

Names can only contain alphanumeric characters and the characters
_,$,and #. Oracle Corporation discourages the use of $ and #.

A name cannot be an ORACLE reserved word. The list of reserved
words can be found in Appendix A.

The word DUAL should not be used as a name for an object or part.
DUAL is the name of a dummy table frequently accessed by SQL*Plus
and SQL*Forms.

The ORACLE SQL language contains other keywords that have special
meanings and should not be used. The list of these keywords can be
found in Appendix B.

A name must be unique across its namespace. Objects in the same
namespace must have different names. Refer to page 2-8 of ``ORACLE7
Server SQL Language Reference Manual'' for a description of the
namespaces. For example, a table, view and packages share the same
namespace but tables/views and indexes share a different namespace.
Each schema in the database has its own namespaces for the objects
it contains. This means, that two tables in different
schemas are in different namespaces and can have the same name.

A name can be enclosed in double quotes. Such names can contain any
combination of characters, ignoring rules 3 through 7 in this list.
Such names can also include spaces. Once you have given an object
a name enclosed in double quotes, you must use double quotes
whenever you refer to the object.

When you refer to an object in a SQL statement, ORACLE considers the
context of the SQL statement and locates the object in the appropriate
namespace. ORACLE always attempts to resolve an object reference within
the namespaces in your own schema before considering namespaces outside
your schema. You refer to the object with the following syntax:

[schema.]object[.part]

where

object is the name of the object.

schema is the schema containing the object. The schema qualifier
allows you to refer to an object in a schema other than your
own. Note that you must be granted privileges to refer to
objects in other schemas. If you omit this qualifier, ORACLE
assumes that you are refering to an object in your own schema.

part is a part of the object. This identifier allows you to refer
to a part of a schema object, such as a column of a table.
Note that not all types of objects have parts.

Each literal or column value manipulated in ORACLE has a datatype. A
value's datatype associates a fixed set of properties with the value.
When you create a table or cluster, you must specify an internal datatype
for each of its columns. When you create a procedure or stored function,
you must specify an internal datatype for each of its arguments. These
datatypes define the domain of values that each column can contain or
each argument can have. The most likely used datatypes are listed below,
for a complete list of all datatypes see page 2-21 of ``ORACLE7 Server
SQL Language Reference Manual''.

char(size) - The CHAR datatype specifies a fixed length character
string. When you create a table with a CHAR column, you can
supply the column length in bytes. ORACLE subsequently ensures
that all values stored in that column have this length. If you
insert a value that is shorter than the column length, ORACLE
blank-pads the value to column length. If you try to insert a
value that is too long for the column, ORACLE returns an error.
The default length for a CHAR column is 1 byte. The maximum
length of CHAR data is 255 bytes. ORACLE compares CHAR values
using blank-padded comparison semantics.

varchar2(size) - The VARCHAR2 datatype specifies a variable length
character string. When you create a VARCHAR2 column, you can
supply the maximum number of bytes of data that it can hold.
ORACLE subsequently stores each value in the column exactly as
you specify it, provided it does not exceed the column's
maximum length. If you try to insert a value that exceeds this
length, ORACLE returns an error. You must specify a maximum
length for a VARCHAR2 column. The maximum length of VARCHAR2
data is 2000 bytes. ORACLE compares VARCHAR2 values using
non-padded comparison semantics.

varchar(size) - The VARCHAR datatype is currently synonymous with the
VARCHAR2 datatype. Oracle Corporation recommends that you use
VARCHAR2 rather than VARCHAR. In a future version of ORACLE,
VARCHAR might be a separate datatype used for variable length
character strings compared with different comparison semantics.

number(p,s)] - where p is the precision, or the total number of digits
and s is the scale, or the number of digits to the right of
the decimal point. You can use number(p) which is a fixed point
number with precision p and scale 0, or number which is a
floating point number with precision 38. If the scale is
negative, the actual data is rounded to the specified number
of places to the left of the decimal point. For example, a
specification of (10,-2) means to round to hundreds - 7456123.89
would be stored as 7456100.

float(b)] - where b specifies a floating point number with binary
precision b. The precision b can range from 1 to 126 with a
default value of 126. To convert from binary to decimal
precision, multiply b by 0.30103. To convert from decimal to
binary precision, multiply the decimal precision by 3.32193.
The maximum of 126 digits of binary precision is roughly
equivalent to 38 digits of decimal precision.

LONG columns store variable length character strings containing
up to 2 gigabytes, or 2**31-1 bytes. LONG columns have many of
the characteristics of VARCHAR2 columns. You can use LONG
columns to store long text strings. ORACLE uses LONG columns
in the data dictionary to store the text of view definitions.
The length of LONG values may also be limited by the memory
available on your computer.

You can reference LONG columns in SQL statements in these
places:

SELECT lists

SET clauses of UPDATE statements

VALUES clauses of INSERT statements

The use of LONG values are subject to some restrictions:

A table cannot contain more than one LONG column.

LONG columns cannot appear in integrity constraints
(except for NULL and NOT NULL constraints).

LONG columns cannot be indexed.

A procedure or stored function cannot accept a LONG argument.

Also, LONG columns cannot appear in certain parts of SQL
statements:

WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with
the DISTINCT operator in SELECT statements.

The DATE datatype is used to store date and time information.
Although date and time information can be represented in both
CHAR and NUMBER datatypes, the DATE datatype has special
associated properties. For each DATE value the following
information is stored:

century year month day hour minute second

You cannot specify a date literal. To specify a date value, you
must convert a character or numeric value to a date value with
the TO_DATE function. ORACLE automatically converts character
values that are in the default date format into date values
when they are used in date expressions. The default date format
is specified by the initialization parameter NLS_DATE_FORMAT
and is a string such as 'DD-MON-YY'. This example date format
includes two-digit number for the day of the month, an
abbreviation of the month name, and the last two digits of
this year.

If you specify a date value without a time component, the
default time is 12:00:00a.m. (midnight). If you specify a date
value without a date, the default date is the first day of the
current month. The date function SYSDATE returns the current
date and time.

You can add and subtract number constants as well as other dates
from dates. ORACLE interprets number constants in arithmetic
date expressions as number of days. For example, SYSDATE + 1
is tomorrow. SYSDATE + (10/1440) is ten minutes from now. ORACLE
provides functions for many of the common date operations. For
example, the ADD_MONTHS function allows you to add or subtract
months from a date. The MONTHS_BETWEEN function returns the
number of months between two dates.

A Julian date is the number of days since Jan 1, 4712BC. Julian
dates allow continuous dating from a common reference. You can
use the date format model ``J'' with date functions TO_DATE and
TO_CHAR to convert between ORACLE DATE values and their Julian
equivalents. For example, this satement returns the Julian
equivalent of January 1, 1992:

The RAW and LONG RAW datatypes are used for byte-oriented data (for
example, binary data or byte strings) to store character strings,
floating point data, and binary data such as graphics images and
digitized sound. ORACLE returns RAW values as hexadecimal character
values. RAW data can only be stored and retrieved. RAW is equivalent
to VARCHAR2 and LONG RAW to LONG except that there is no conversion
between database and session character set.

Each row in the database has an address. You can examine a row's
address by querying the pseudocolumn ROWID. Values of this
pseudocolumn are hexadecimal strings representing the address of each
row. For more information on the ROWID pseudocolumn, see page 2-38 of
``ORACLE7 Server SQL Language Reference Manual''.

Generally an expression cannot contain values of different datatypes. For
example, an expression cannot multiply 5 by 10 and then add 'JAMES'.
However, ORACLE supports both implicit and explicit conversion of values
from one datatype to another. In this statement, ORACLE implicitly
converts '12-MAR-1993' to a DATE value using the default date format
'DD-MON-YYYY':

SELECT ename
FROM emp
WHERE hiredate = '12-MAR-1993'

You can also explicitly specify datatype conversions using SQL conversion
functions. For more information on these functions, see the section
``Conversion Functions'' on page 3-35 of ``ORACLE7 Server SQL Language
Reference Manual''. Oracle Corporation recommends that you specify explicit
conversions rather than rely on implicit or automatic conversions.

If a row lacks a value for a particular column, that column is said to be
null, or to contain a null. Nulls can appear in columns of any datatype
that are not restricted by NOT NULL or PRIMARY KEY integrity constraints.
Use a null when the actual value is unknown or when a value would not be
meaningful. ORACLE currently treats a character value with a length of
zero as null. Do not use null to represent a value of zero, because they
are not equivalent. Any arithmetic expression containing a null always
evaluates to null.

All Scalar functions (except NVL and TRANSLATE) return null when given
a null argument. The NVL function can be used to return a value when a
null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM
is null or the value of COMM if it is not null.

To test for nulls, only use the comparison operators IS NULL and IS NOT
NULL. If you use any other operator with nulls and the result depends on
the value of the null, the result is unknown. Because null represents a
lack of data, a null cannot be equal to or unequal to any value or to
another null. ORACLE treats conditions evaluating to unknown values as
FALSE. For example, since the condition COMM = NULL is always unknown,
a SELECT statement with this condition in its WHERE clause returns no
rows. Note that ORACLE returns no error message in this case.

Comments within SQL statements do not affect the statement execution,
but they may make your application easier for you to read and maintain.
You may want to include a comment in a statement that describes the
statement's purpose within your application.

A comment can appear between any keywords, parameters or punctuation
marks in a statement. You can include a comment in a statement using
either of these means:

Begin the comment with /*. Proceed with the text of the comment.
This text can span multiple lines. End the comment with */.

Begin the comment with -\space- (two hyphens). Proceed with the text of the
comment. This text cannot extend to a new line. End the comment with
a line break.

Note that you cannot use these styles of comments between SQL statements
in a SQL script. You can use the SQL*Plus REMARK command for this purpose.

An operator is used to manipulate individual data items and return a
result. These data items are called operands or arguments. Some of the operators
will be listed below and where necessary, an explanation will be provided.
For more information on these operators see chapter 3 of ``ORACLE7 Server
SQL Language Reference Manual''.

Note: % matches any string of zero or more characters except
null. The character ``_'' matches any single character.

Set Operators
Set operators combines the results of two queries into a single
result:

UNION

- All distinct rows selected by either query.

UNION ALL

- All rows selected by either query, including all duplicates.

INTERSECT

- All distinct rows selected by both queries.

MINUS

- All distinct rows selected by the first query but not the second.

Example 1 This condition is true for all ENAME values beginning with ``MA''

ename LIKE 'MA%'

All of these ENAME values make the condition TRUE:

MARTIN, MA, MARK, MARY

Since case is significant, ENAME values beginning with ``Ma'',
``ma'', and ``mA'' make the condition FALSE.

Consider this condition:

ename LIKE 'SMITH_'

This condition is true for these ENAME values:

SMITHE, SMITHY, SMITHS

This condition is false for 'SMITH', since the special character
``_'' must match exactly one character of the ENAME value.

The ESCAPE Option - You can include the actual characters ``%''
or ``-'' in the pattern by using the ESCAPE option. The ESCAPE
option identifies the escape character. If the escape character
appears in the pattern before the character ``%'' or ``_'', ORACLE
interprets this character literally in the pattern, rather than
as a special pattern matching character.

A function is similar to an operator in that it manipulates data items
and returns a result. Functions differ from operators in the format in
which they appear with their arguments. If you call a function with an
argument of a datatype other than the datatype expected by the function,
ORACLE implicitly converts the argument to the expected datatype before
performing the function. If you call a function with a null argument, the
function automatically returns null. The only functions that do not follow
this rule are CONCAT, REPLACE, DUMP and NVL.

There are two general types of functions:

single row (or scalar) functions

group functions (or aggregate) functions

These functions differ in the number of rows upon which they act. A single
row function returns a single result row for every row of a queried table
or view, while a group function returns a single result row for a group
of queried rows.

Single row functions can appear in select lists (provided the SELECT
statement does not contain a GROUP BY clause), WHERE clauses, START WITH
clauses, and CONNECT BY clauses.

Group functions can appear in select lists and HAVING clauses. If you use
the GROUP BY clause in a SELECT statement, ORACLE divides the rows of a
queried table or view into groups. In a query containing a GROUP BY clause,
all elements of the select list must be either expressions from the GROUP
BY clause, expressions containing group functions, or constants. ORACLE
applies the group functions in the select list to each group of rows and
returns a single result row for each group.

This section will list the common functions with their arguments expected.
A short example will be provided when necessary. For more information on
these functions see chapter 3 of ``ORACLE7 Server SQL Language Reference
Manual''.

Returns char, with the first letter of each word in uppercase, all other
letters in lowercase.

LOWER(char)

Returns char, with all letters lowercase.

LPAD(c1,n[,c2])

Returns c1, left-padded to length n with the sequence of characters in
c2; c2 defaults to `\space', a single blank.

LTRIM(c1[,set])

Removes characters from the left of c1, with initial characters removed
up to the first character not in set; set defaults to `\space', a single
blank.

REPLACE(c1, s1 [,r1])

Returns the string, c1, with every occurrence of s1, search string,
replaced with replacement string, r1. If r1 replacement string is
omitted or null, all occurrences of s1 are removed. If s1 is null, c1
is returned.

RPAD(c1,n[,c2])

Returns c1, right-padded to length n with c2, replicated as many times as
necessary; c2 defaults to `\space', a single blank. If c1 is longer than
n, this function returns the portion of char1 that fits in n.

RTRIM(c1[,set])

Returns char, with final characters removed after the last character not
in set; set defaults to `\space'.

SOUNDEX(char)

Returns a character string containing the phonetic representation of
char. This function allows you to compare words that are spelled
differently, but sound alike in English.

SUBSTR(c1,m[,n])

Returns a portion of c1, beginning at character m, n characters long.
If m is positive, ORACLE counts from the beginning of char to find the
first character. If m is negative, ORACLE counts backwards from the end
of char. The value m cannot be 0. If n is omitted, ORACLE returns all
characters to the end of char. The value n cannot be less than 1.

SUBSTRB(c1,m[,n])

The same as SUBSTR, except that the arguments m and n are expressed in
bytes, rather than in characters.

TRANSLATE(c1,from,to)

Returns c1 with all occurrences of each character in from replaced
by its corresponding character in to. Characters in c1 that are
not in from are not replaced. The argument from can contain
more characters than to. In this case, the extra characters at the
end of from have no corresponding characters in to. If
these extra characters appear in c1, they are removed from the return
value. You cannot use an empty string for to in order to remove
all characters in from from the return value. ORACLE interprets
the empty string as null, and if this function has a null argument, it
returns null.

UPPER(char)

Returns char, with all letters uppercase.

ASCII(char)

Returns the decimal representation in the database set of the first
byte of char.

INSTR(c1,c2[,n[,m]])

Searches c1 beginning with its nth character for the mth occurrence of
c2 and returns the position of the character in c1 that is the first
character of this occurrence. If n is negative, ORACLE counts and
searches backward from the end of c1. The value of m must be positive.
The default values of both n and m are 1, meaning ORACLE begins searching
at the first character of c1 for the first occurrence of c2.

LENGTH(char)

Returns the length of char in characters.

Date Functions

ADD_MONTHS(d,n)

Returns the date d plus n months. The argument n can be any integer. If
d is the last day of the month or if the resulting month has fewer days
than the day component of d, then the result is the last day of the
resulting month. Otherwise, the result has the same day component as d.

LAST_DAY(d)

Returns the date of the last day of the month that contains d. You might
use this function to determine how many days are left in the current
month.

MONTHS_BETWEEN(d1,d2)

Returns number of months between dates d1 and d2. If d1 is later than d2,
result is positive; if earlier, negative. If d1 and d2 are either the
same days of the month or both last days of months, the result is always
an integer; otherwise ORACLE calculates the fractional portion of the
result based on a 31-day month and also considers the difference in time
components of d1 and d2.

NEW_TIME(d,z1,z2)

Returns the date and time in time zone z2 when date and time in time
zone z1 are d. The arguments z1 and z2 can be any of these text strings:

'AST' or 'ADT' Atlantic Standard or Daylight Time
'BST' or 'BDT' Bering Standard or Daylight Time
'CST' or 'CDT' Central Standard or Daylight Time
'EST' or 'EDT' Eastern Standard or Daylight Time
'GMT' Greenwich Mean Time
'HST' or 'HDT' Alaska-Hawaii Standard Time or Daylight Time
'MST' or 'MDT' Mountain Standard Time or Daylight Time
'NST' Newfoundland Standard Time
'PST' or 'PDT' Pacific Standard or Daylight Time
'YST' or 'YDT' Yukon Standard or Daylight Time

NEXT_DAY(d,char)

Returns the date of the first weekday named by char that is later than
the date d. The argument char must be a day of the week in your session's
date language. The return value has the same hours, minutes, and seconds
component as the argument d.

ROUND(d[,fmt])

Returns d rounded to the unit specified by the format model fmt. If you
omit fmt, d is rounded to the nearest day.

SYSDATE

Returns the current date and time. Requires no arguments. In distributed
SQL statements, this function returns the date and time of your
local database. You cannot use this function in the condition of a
CHECK constraint.

TRUNC(d[,fmt])

Returns d with the time portion of the day truncated to the unit
specified by the format model fmt. If you omit fmt, d is truncated to
the nearest day.

Format Model Rounding or Truncating Unit
CC, SCC Century
SYYY,YYYY, Year (rounds up on July 1)
YEAR,SYEAR,
YYY,YY,Y
IYYY,IYY,IY,I ISO Year
Q Quarter (rounds up on the sixteenth day of the
second month of the quarter)
MONTH,MON, Month (rounds up on the sixteenth day)
MM,RM
WW Same day of the week as the first day of the year
IW Same day of the week as the first day of the ISO year
W Same day of the week as the first day of the month
DDD,DD,J Day
DAY,DY,D Starting day of the week
HH,HH12,HH24 Hour
MI Minute

Conversion Functions

CHARTOROWID(char)

Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.

HEXTORAW(char)

Converts char containing hexadecimal digits to a raw value.

RAWTOHEX(raw)

Converts raw to a character value containing its hexadecimal equivalent.

ROWIDTOCHAR(rowid)

Converts a ROWID value to VARCHAR2 datatype. The result of this
conversion is always 18 characters long.

TO_CHAR(d [,fmt])

Converts d of DATE datatype to a value of VARCHAR2 datatype in the
format specified by the date format fmt. If you omit fmt, d is converted
to a VARCHAR2 value in the default date format.

TO_CHAR(n, [,fmt])

Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the
optional number format fmt. If you omit fmt, n is converted to a VARCHAR2
value exactly long enough to hold its significant digits.

TO_DATE(char [,fmt])

Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype.
The fmt is a date format specifying the format of char. If you omit fmt,
char must be in the default date format. If fmt is 'J', for Julian, then i
char must be a number.

TO_NUMBER(char [,fmt])

Converts char, a value of CHAR or VARCHAR2 datatype containing a number
in the format specified by the optional format model fmt, to a value
of NUMBER datatype.

Other Funtions

GREATEST(expr [,expr] ...)

Returns the greatest of the list of exprs. All exprs after the first are
implicitly converted to the datatype of the first prior to the comparison.
ORACLE compares the exprs using non-padded comparison semantics.

LEAST(expr [,expr] ...)

Returns the least of the list of exprs. All exprs after the first are
implicitly converted to the datatype of the first prior to the
comparison. ORACLE compares the exprs using non-padded comparison
semantics.

NVL(expr1,expr2)

If expr1 is null, returns expr2; if expr2 is not null, returns expr1.
The arguments expr1 and expr2 can have any datatype. If their datatypes
are different, ORACLE converts expr2 to the datatype of expr1 before
comparing them.

UID

Returns an integer that uniquely identifies the current user.

USER

Returns the current ORACLE user with the datatype VARCHAR2.

USERENV(option)

Returns information of VARCHAR2 datatype about the current session. This
information can be useful for writing an application-specific audit trail
table or for determining the language-specific characters currently used
by your session.

This option causes a group function to consider only distinct values of
the argument expression.

ALL

This option causes a group function to consider all values including all
duplicates

All group functions except COUNT(*) ignore nulls. You can use the NVL
in the argument to a group function to substitute a value for a null.
If a query with a group function returns no rows or only rows with nulls
for the argument to the group function, the group function returns null.

AVG([DISTINCT|ALL] n)

Returns average value of n.

COUNT({* | [DISTINCT|ALL] expr} )

Returns the number of rows in the query. If you specify expr, this
function returns rows where expr is not null. You can count either all
rows, or only distinct values of expr. If you specify the asterisk (*),
this function returns all rows, including duplicates and nulls.

MAX([DISTINCT|ALL] expr)

Returns maximum value of expr.

MIN([DISTINCT|ALL] expr)

Returns minimum value of expr.

STDDEV([DISTINCT|ALL] x)

Returns the standard deviation of x, a number. ORACLE calculates the
standard deviation as the square root of the variance defined for the
VARIANCE group function.

SUM([DISTINCT|ALL] n)

Returns sum of values of n.

VARIANCE([DISTINCT|ALL] x)

Returns variance of x, a number. For the variance formula see page 3-48
of ``ORACLE7 Server SQL Language Reference Manual''.

A format model is a character literal that describes the format of DATE
or NUMBER data stored in a character string. You can use a format model
as an argument of the TO_CHAR or TO_DATE function for these purposes

to specify the format for ORACLE to use to return a value from
the database to you

to specify the format for a value you have specified for ORACLE
to store in the database

Note that a format model does not change the internal representation of
the value in the database.

in the TO_CHAR function to translate a value of NUMBER datatype
to VARCHAR2 datatype

in the TO_NUMBER function to translate a value of CHAR or VARCHAR2
datatype to NUMBER datatype

All number format models cause the number to be rounded to the specified
number of significant digits. If a value has more significant digits
to the left of the decimal place than are specified in the format,
pound signs (#) replace the value.

A number format model is composed of one or more number format elements.
The table below lists the elements of a number format model.

Element

Example

Description

9

9999

Number of ``9''s specifies number of significant digits returned. Blanks
are returned for leading zeroes and for a value of zero.

0

0999
9990

Returns a leading zero or a value of zero in this position as a 0,
rather than as a blank.

$

$9999

Prefixes values with dollar sign.

B

B9999

Returns zero value as blank, regardless of ``0''s in the format model.

MI

9999MI

Returns ``-'' after negative values. For positive values, a trailing
space is returned.

S

S9999

Returns ``+'' for positive values and ``-'' for negative values in this
position.

PR

9999PR

Returns negative values in . For positive values, a
leading and trailing space is returned.

D

99D99

Returns the decimal character in this position, separating the integral
and fractional parts of a number.

G

9G999

Returns the group separator in this position.

C

C999

Returns the ISO currency symbol in this position.

L

L999

Returns the local currency symbol in this position.

,(comma)

9,999

Returns a comma in this position.

.(period)

99.99

Returns a period in this position, separating the integral and fractional
parts of a number.

V

999V99

Multiplies values by 10**{n}, where n is the number of ``9''s after the
``V''.

EEEE

9.999EEEE

Returns value in scientific notation.

RN

RN

Returns upper- or lower-case Roman numerals.

rn

Value can be an integer between 1 and 3999.

The MI and PR format elements can only appear in the last position of
a number format model. The S format element can only appear in the first
or last position.

If a number format model does not contain the MI, S, or PR format
elements, negative sign and positive values automatically contain a
leading space.

A number format model can contain only a single decimal character (D)
or period (.), but it can contain multiple group separators (G) or
commas (,). A group separator or comma cannot appear to the right of
a decimal character or period in a number format model.

The characters returned by some of these format elements are specified
by initialization parameters. The table below lists these elements and
parameters.

in the TO_CHAR function to translate a DATE value that is in
a format other than the default date format

in the TO_DATE function to translate a character value that is
in a format other than the default date format

A date format model is composed of one or more date format elements.
The table below lists the elements of a date format model.

Element

Meaning

SCC or CC

Century; ``S'' prefixes BC dates with ``-''.

YYYY or SYYYY

4-digit year; ``S'' prefixes BC dates with ``-''.

IYYY

4-digit year based on the ISO standard.

YYY or YY or Y

Last 3, 2, or 1 digit(s) of year.

IYY or IY or I

Last 3, 2, or 1 digit(s) of ISO year.

Y, YYY

Year with comma in this position.

SYEAR or YEAR

Year, spelled out;``S'' prefixes BC dates with ``-''.

RR

Last 2 digits of year; for years in other centuries.

BC or AD

BC/AD indicator.

B.C. or A.D.

BC/AD indicator with periods.

Q

Quarter of year (1,2,3,4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

RM

Roman numeral month (I-XII; JAN = I).

MONTH

Name of month, padded with blanks to length of 9 characters.

MON

Abbreviated name of month.

WW

Week of year (1-53) where week 1 starts on the first day of the year and
continues to the seventh day of the year.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

W

Week of month (1-5) where week 1 starts on the first day of the month
and ends on the seventh.

DDD

Day of year (1-366).

DD

Day of month (1-31).

D

Day of week (1-7).

DAY

Name of day, padded with blanks to length of 9 characters.

DY

Abbreviated name of day.

J

Julian day; the number of days since January 1, 4712 BC. Numbers
specified with 'J' must be integers.

AM or PM

Meridian indicator.

A.M. or P.M.

Meridian indicator with periods.

HH or HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight 90-86399).

-/,.;:"text"

Punctuation and quoted text is reproduced in the result.

The RR date format element is similar to the YY date format element,
but it provides additional flexibility for storing date values in other
centuries. The RR date format element allows you to store twenty-first
century dates in the twentieth century by specifying only the last two
digits of the year. It will also allow you to store twentieth century
dates in the twenty-first century in the same way if necessary.

If you use the TO_DATE function with the YY date format element, the
date value returned is always in the current century. If you use the RR
date format element instead, the century of the return value varies
according to the specified two-digit year and the last two digits of
the current year.

These characters appear in the return value in the same location as
they appear in the format model. Note that character literals must be
enclosed in quotation marks (double quotes).

You can use the FM and FX modifiers in format models for the
TO_CHAR function to control blank padding and exact format checking.
A modifier can appear in a format model more than once. In such case,
each subsequent occurrence toggles the effects of the modifier. Its
effects are enabled for the portion of the model following its first
occurrence, and then disabled for the portion following its second, and
then re-enabled for the portion following its third, and so on.

In a date format element of a TO_CHAR function, this modifier
suppresses blanks in subsequent character elements (such as
MONTH) and suppresses leading zeroes for subsequent number
elements (such as MI) in a date format model. Since there is no
blank padding, the length of the return value may vary. Without
FM, the result of a character element is always right padded with
blanks to a fixed length and the leading zero are always returned
for a number element.

In a number format element of a TO\_CHAR function, this modifier
suppresses blanks added to the left of the number in the result
to right-justify it in the output buffer. Without FM, the result
is always right justified in the buffer, resulting in
blank-padding to the left of the number.

The table shows the results of the following query for different
values of number and 'fmt' using FM:

FX - ``Format Exact'' This modifier specifies exact matching for the
character argument and date format model of a TO\_DATE function.

Punctuation and quoted text in the character argument must
exactly match (except for case) the corresponding parts of the
format model. Without FX, punctuation and quoted text in the
character argument need only match the length and position of
the corresponding parts of the format model.

The character argument cannot have extra blanks. Without FX,
ORACLE ignores extra blanks.

Numeric data in the character argument must have the same number
of digits as the corresponding element in the format model.
Without FX, numbers in the character argument can omit leading
zeroes. When FX is enabled, you can disable this check for
leading zeros by using the FM modifier as well.

The table shows whether the following statement meets the matching
conditions for different values of char and 'fmt' using FX:

The following section provides a functional summary of Data Definition Language SQL commands. If
you require more detailed information than that provided here, see
chapter 4 of ``ORACLE7 Server SQL Language Reference Manual''.

SQL commands are divided into a number of categories, of which the DDL
commands are but one part:

Data Definition Language commands

Data Manipulation Language commands

Transaction Control commands

Session Control commands

Data Definition Language commands allow you to perform these tasks:

- create, alter, and drop objects

- grant and revoke privileges and roles

ORACLE implicitly commits the current transaction before and after every
Data Definition Language statement.

Note: Not all of the DDL commands have
been explained here, only the ones that you are most likely to use.

This command allows the user to create multiple tables, multiple views and perform multiple
grants in a single transaction.

schema - is the name of the schema. The schema name must be the
same as your ORACLE username.

CREATE TABLE - is a CREATE TABLE statement to be issued as part
of this CREATE SCHEMA statement.

CREATE VIEW - is a CREATE VIEW statement to be issued as part
of this CREATE SCHEMA statement.

GRANT - is a GRANT statement (Objects Privileges) to be issued
as part of this CREATE SCHEMA statement.

The CREATE SCHEMA statement only supports the syntax of these
commands as defined by standard SQL, rather than the complete
syntax supported by ORACLE. For more information see
``ORACLE and Standard SQL,'' of ``ORACLE7 Server SQL Language
Reference Manual''.

To execute a CREATE SCHEMA statement, ORACLE executes each
included statement. If all statements execute successfully,
ORACLE commits the transaction. If any statement results in an
error, ORACLE rolls back all the statements. Terminate a CREATE
SCHEMA statement just as you would any other SQL statement
using the terminator character specific to your tool. For
example, if you issue a CREATE SCHEMA statement in SQL*Plus,
terminate the statement with a semi-colon (;). Do not separate
the individual statements within a CREATE SCHEMA statement with
the terminator character.

The order in which you list the CREATE TABLE, CREATE VIEW, and
GRANT statements is unimportant:

A CREATE VIEW statement can create a view that is based on
a table that is created by a later CREATE TABLE statement.

A CREATE TABLE statement can create a table with a foreign
key that depends on the primary key of a table that is
created by a later CREATE TABLE statement.

A GRANT statement can grant privileges on a table or view
that is created by a later CREATE TABLE or CREATE VIEW
statement.

The statements within a CREATE SCHEMA statement can also
reference existing objects.

This command allows the user to create a table, the basic structure to hold
user data, by specifying the following information:

column definitions

integrity constraints

the table's tablespace

storage characteristics

an optional cluster

data from an arbitrary query

schema - is the schema to contain the table. If you omit schema,
ORACLE creates the table in your own schema.

table - is the name of the table to be created.

column - specifies the name of a column of the table. The number
of columns in a table can range from 1 to 254.

datatype - is the datatype of a column. Datatypes are defined
previously in this manual.

DEFAULT - specifies a value to be assigned to the column if a
subsequent INSERT statement omits a value for the
column. The datatype of the expression must match the
datatype of the column. A DEFAULT expression cannot
contain references to other columns.

column_constraint - defines an integrity constraint as part of
the column definition.

table_constraint - defines an integrity constraint as part of
the table definition.

PCTFREE - specifies the percentage of space in each of the
table's data blocks reserved for future updates to the
table's rows. PCTFREE has the same function in the
commands that create and alter clusters, indexes,
snapshots, and snapshot logs. The combination of
PCTFREE and PCTUSED determines whether inserted rows
will go into existing data blocks or into new blocks.
See discussion on PCTFREE, PCTUSED, INITRANS, MAXTRANS,
in Chapter 2 of ``ORACLE Architecture and Terminology''.
These parameters need not be set as the default values
will be sufficient for your purpose.

AS subquery - inserts the rows returned by the subquery into the
table upon its creation.

After creating a table, you can define additional columns and
integrity constraints with the ADD clause of the ALTER TABLE
command. You can change the definition of an existing column
with the MODIFY clause of the ALTER TABLE command. To modify
an integrity constraint, you must drop the constraint and
redefine it.

This table contains 8 columns. For example, the EMPNO column is
of datatype NUMBER and has an associated integrity constraint
named PK_EMP. The HIREDATE column is of datatype DATE and has a
default value of SYSDATE. This table definition specifies a
PCTFREE of 5 and a PCTUSED of 75, which is appropriate for a
relatively static table.

This directive defines a view, a logical table based on one or more
tables or views. To create a view in your own schema, you must
have the CREATE VIEW system privilege. The owner of the schema
containing the view must have the privileges necessary to
either select, insert, update or delete rows from all the
tables or views on which the view is based.

schema - is the schema to contain the table. If you omit schema,
ORACLE creates the table in your own schema.

OR REPLACE - recreates the view if it already exists. You can
use this option to change the definition of an existing
view without dropping, recreating, and regranting object
privileges previously granted to it.

FORCE - creates the view regardless of whether the view's base
tables exist or the owner of the schema containing the
view has privileges on them. Note that both of these
conditions must be true before any SELECT, INSERT,
UPDATE, or DELETE statements can be issued against the
view.

NOFORCE - creates the view only if the base tables exist and the
owner of the schema containing the view has privileges
on them. The default is NOFORCE.

schema - is the schema to contain the view. If you omit schema,
ORACLE creates the view in your own schema.

view - is the name of the view.

alias - specifies names for the expressions selected by the
view's query. The number of aliases must match the
number of expressions selected by the view.

AS subquery - identifies columns and rows of the table(s) that
the view is based on. A view's query can be any SELECT
statement without the ORDER BY or FOR UPDATE clauses.

WITH CHECK OPTION - specifies that inserts and updates performed
through the view must result in rows that the view
query can select. The CHECK OPTION cannot make this
guarantee if there is a subquery in the query of this
view or any view on which this view is based.

CONSTRAINT - is the name assigned to the CHECK OPTION
constraint. If you omit this identifier, ORACLE
automatically assigns the constraint a name of the
form ``SYS_Cn''.

Views are used for these purposes:

To provide an additional level of table security,
by restricting access to a predetermined set of rows
and /or columns of a base table.

To hide data complexity.

To present data from another perspective.

Note these caveats:

A view's query cannot select the CURRVAL or NEXTVAL
pseudocolumns.

If a view's query selects the ROWID, ROWNUM, or LEVEL
pseudocolumns, they must have aliases in the view's
query.

You can define a view with a query that uses an
asterisk (*) to select all the columns of a table:

CREATE VIEW emp_vu
AS SELECT * FROM emp

ORACLE translates the asterisk into a list of all
the columns in the table at the time the CREATE VIEW
statement is issued.

If the view query contains any of the following constructs, you
cannot perform inserts, updates, or deletes on the view:

joins

set operators

group functions

GROUP BY, CONNECT BY, or START WITH clauses

the DISTINCT operator

Note: If a view contains pseudocolumns or expressions, you
can only update the view with an UPDATE statement that does not
refer to any of the pseudocolumns or expressions.

This command permits the user to grant privileges for a particular object to users and roles.
The object must be in your own schema or you must have been
granted the object privileges with the GRANT OPTION.

object_priv - is an object privilege to be granted. You can
substitute any of these values:

ALTER

DELETE

EXECUTE

INDEX

INSERT

REFERENCES

SELECT

UPDATE

ALL PRIVILEGES - grants all the privileges for the object that
has all privileges on the object with the GRANT OPTION.

column - specifies a table or view column on which privileges
are granted. You can only specify columns when granting
the INSERT, REFERENCES, or UPDATE privilege. If you do
not list columns, the grantee has the specified
privilege on all columns in the table or view.

ON - identifies the object on which the privileges are granted.

TO - identifies users or roles to which the object privilege is
granted.

PUBLIC - grants object privileges to all users.

WITH GRANT OPTION - allows the grantee to grant the object
privileges to other users and roles. The grantee must
be a user or PUBLIC, rather than a role.

A privilege cannot appear more than once in the list of
privileges to be granted. A user or role cannot appear more
than once in the TO clause.

Example

GRANT SELECT, UPDATE
ON golf_handcap
TO PUBLIC
GRANT REFERENCES(empno), UPDATE(empno, sal, comm)
ON scott.emp
TO blake

BLAKE can subsequently update values of EMPNO, SAL and COMM
columns. BLAKE can also define referential integrity
constraints that refer to the EMPNO column. However, since
the GRANT statement lists only these columns, BLAKE cannot
perform operations on any of the other columns of the EMP
table. For example, BLAKE can create a table with a
constraint:

This directive permits the user to create an index on one or more columns of a table
or a cluster. An index is a database object that contains an
entry for each value that appears in the indexed column(s) of
the table or cluster and provides direct, fast access to rows.
To create an index in your own schema, you must have either
space quota on the tablespace to contain the index or UNLIMITED
TABLESPACE system privilege.

schema - is the schema to contain the index. If you omit schema,
ORACLE creates the index in your own schema.

index - is the name of the index to be created.

table - is the name of the table for which the index is to be
created.

column - is the name of a column in the table. An index can
have as many as 16 columns. A column of an index
cannot be of datatype LONG or LONG RAW.

ASC DESC - are allowed for DB2 syntax compatibility, although
indexes are always created in ascending order.

NOSORT - indicates to ORACLE that the rows are stored in the
database in ascending order and therefore ORACLE does
not have to sort the rows when creating the index.

ORACLE can use indexes to improve performance when:

searching for rows with specified index column values

accessing tables in index column order

However, an index can slow down INSERT, UPDATE, and DELETE
commands that affect index column values because ORACLE must
maintain both the index data as well as the table data. When
you initially insert rows into a new table, it is generally
faster to create the table, insert the rows, and then create
the index. You can create several indexes on different columns
in the same table. ORACLE imposes no limits on the number of
indexes you can create on a single table. Note that each index
increases the processing time needed to maintain the table
during updates to indexed data.

A sequence is a database object from which multiple
users may generate unique integers. You can use sequences to
automatically generate primary key values. You must have CREATE
SEQUENCE privilege to use this command.

schema - is the schema to contain the sequence.

sequence - is the name of the sequence to be created.

INCREMENT BY - specifies the interval between sequence numbers.
This value can be any positive or negative ORACLE
integer, but it cannot be 0. If this value is negative,
then the sequence descends. If the increment is
positive, then the sequence ascends. If you omit this
clause, the interval defaults to 1.

START WITH - specifies the first sequence number to be
generated.

CYCLE - specifies that the sequence continues to generate
values after reaching either its maximum or minimum
value.

CACHE - specifies how many values of the sequence ORACLE
preallocates and keeps in memory for faster access.

ORDER - guarantees that sequence numbers are generated in
order of request. You may want to use this option
if you are using the sequence numbers as timestamps.

You can use sequence numbers to automatically generate unique
primary key values for your data, and you can also coordinate
the keys across multiple rows or tables. Values for a given
sequence are automatically generated by special ORACLE routines
and, consequently, sequences avoid the performance bottleneck
which results from implementation of sequences at the
application level. ORACLE sequences permit the simultaneous
generation of multiple sequence numbers while guaranteeing
that every sequence number is unique.

Once a sequence is created, you can access its values in SQL
statements with these pseudocolumns:

CURRVAL - returns the current value of the sequence

NEXTVAL - increments the sequence and returns the new value.

Example

CREATE SEQUENCE eseq
INCREMENT BY 10

The first reference to ESEQ.NEXTVAL returns 1. The second
returns 11. Each subsequent reference will return a value
10 greater than the one previous.

A synonym is an alternative name for a table, view,
sequence, procedure, stored function, package, snapshot, or
another synonym. You must have CREATE SYNONYM system privilege.

PUBLIC - creates a public synonym. If you omit this option,
the synonym is private and is accessible only within
its schema.

schema - is the schema to contain the sequence.

synonym - is the name of the synonym to be created.

FOR - identifies the object for which the synonym is created.
The object can be of these types: table, view,
sequence, stored procedure, function, or package,
and synonym. The object need not currently exist and
you need not have privileges to access the object.

A synonym can be used to stand for its base object in any
Data Manipulation Language statement. Synonyms are used for
security and convenience. Creating a synonym for an object
allows you to:

reference the object without specifying its owner

reference the object without specifying the database
on which it is located

provide another name for the object

Synonyms provide both data independence and location
transparency; synonyms permit applications to function without
modification regardless of which user owns the table or view
and regardless of which database holds the table or view.

A private synonym name must be distinct from all other objects
in its schema.

Example

CREATE PUBLIC SYNONYM emp
FOR scott.emp@sales

This creates a PUBLIC synonym for the EMP table in the schema
SCOTT on the remote SALES database. Note that a synonym may
have the same name as the base table provided the base table
is contained in another schema.

This command allows the user to alter the definition of a table in one of
these ways:

to add a column

to add an integrity constraint

to redefine a column (datatype, size, default value)

to modify storage characteristics or other parameters

to enable, disable, or drop an integrity constraint

to explicitly allocate an extent

schema - is the schema containing the table.

table - is the name of the table to be altered.

ADD - adds a column or integrity constraint.

MODIFY - modifies the definition of an existing column. If you
omit any of the optional parts of the column definition
(datatype, default value, or column constraint), these
parts remain unchanged.

column - is the name of the column to be added or modified.

datatype - specifies a datatype for a new column or a new
datatype for an existing column.

DEFAULT - specifies a default value for a new column or a new
default for an existing column.

column_constraint - adds or removes a NOT NULL constraint to or
from a existing column.

table_constraint - adds an integrity constraint to the table.

DROP - drops an integrity constraint.

If you use the ADD clause to add a new column to the table,
then the initial value of each row for the new column is null.
You can add a column with a NOT NULL constraint only to a table
that contains no rows.

You can use the MODIFY clause to change the datatype, size,
default value, and NOT NULL column constraint. You can change
a column's datatype or decrease a column's size only if the
column contains nulls in all rows. However, you can always
increase the size of a character or raw column or the precision
of a numeric column. A change to a column's default value only
affects rows subsequently inserted into the table. The only
type of integrity constraint that you can add to an existing
column using the MODIFY clause with the column constraint
syntax is a NOT NULL constraint. However, you can define
other types of integrity constraints (UNIQUE, PRIMARY KEY,
referential integrity, and CHECK constraints) on existing
columns using the ADD clause and the table constraint syntax.

This directive is used to remove a table and all its data from the database.

schema - is the schema containing the table.

table - is the name of the table to be dropped.

CASCADE CONSTRAINTS - drops all referential integrity
constraints that refer to primary and unique keys in
the dropped table. If you omit this option, and such
referential integrity constraints exist, ORACLE returns
an error message and does not drop the table.

When you drop a table, ORACLE also performs these operations
automatically:

ORACLE effectively deletes all rows from the table.

ORACLE drops all the table's indexes, regardless of who
created or owns them.

ORALCE returns all data blocks allocated to the table and
its indexes to the tablespaces containing the table and
indexes.

if the table is a base table for views or if it is
referenced in stored procedures, functions, or packages,
ORACLE invalidates these objects but does not drop them.

Example

DROP TABLE test_data;

NOTE: Most DROP commands work in a similar fashion. For more
information on DROP commands (for VIEW, INDEX) see
chapter 4 of ``ORACLE7 Server SQL Language Reference
Manual''.

CHECK - specifies a condition that each row in the table must
satisfy.

USING INDEX - specifies parameters for the index ORACLE uses to
enforce a UNIQUE or PRIMARY KEY constraint. Only use
this clause when enabling UNIQUE and PRIMARY KEY
constraints.

EXCEPTIONS INTO - identifies a table into which ORACLE places
information about rows that violate an enabled integrity
constraint. This table must exist before you use this
option.

DISABLE - disables the integrity constraint. If an integrity
constraint is disabled, ORACLE does not enforce it.

Defining Integrity Constraints - To define an integrity
constraint, include a CONSTRAINT clause in a CREATE TABLE
or ALTER TABLE statement. The CONSTRAINT clause has two
syntactic forms:

table_constraint syntax - is part of the table definition. An
integrity constraint defined with this syntax can
impose rules on any columns in the table. This syntax
can define any type of integrity constraint except a
NOT NULL constraint.

column_constraint syntax - is part of a column definition. In
most cases, an integrity constraint defined with this
syntax can only impose rules on the column in which it
is defined. Column_constraint syntax that appears in a
CREATE TABLE statement can define any type of integrity
constraint. Column_constraint syntax that appears in
an ALTER TABLE statement can only define or remove
a NOT NULL constraint.

The table_constraint syntax and the column_constraint syntax
are simply different syntactic means of defining integrity
constraints. There is no functional difference between an
integrity constraint defined with table_constraint syntax and
the same constraint defined with column_constraint syntax.

NOT NULL constraint - specifies that a column cannot contain
nulls. To satisfy this constraint, every row in the
table must contain a value for the column. The NULL
keyword indicates that a column can contain nulls
(this is the default). It does not actually define an
integrity constraint. You can only specify NOT NULL or
NULL with column\_constraint syntax in a CREATE TABLE
or ALTER TABLE statement, not with table\_constraint
syntax.

ALTER TABLE emp
MODIFY (sal NUMBER CONSTRAINT nn_sal NOT NULL);

NN_SAL ensures that no employee in the table has a
null salary.

UNIQUE constraint - designates a column or combination of
columns as a unique key. To satisfy a UNIQUE constraint,
no two rows in the table can have the same value for the
unique key. However, the unique key made up of a single
column can contain nulls. A unique key column cannot be
of datatype LONG or LONG RAW. You cannot designate the
same column or combination of columns as both a unique
key and a primary key. However, you can designate the
same column or combination of columns as both a unique
key and a foreign key.

You can define a unique key on a single column with
column_constraint syntax. The constraint below ensures
that no two departments in the table have the same
name. However, the constraint does allow departments
without names.

This above statement also uses the USING INDEX option
to specify storage characteristics for the index that
ORACLE creates to enforce the constraint.

A composite unique key is a unique key made up of a
combination of columns. Since ORACLE creates an index
on the columns of a unique key, a composite unique
key can contain a maximum of 16 columns. To define
a composite unique key, you must use table_constraint
syntax, rather than column_constraint syntax.

PRIMARY KEY constraint - designates a column or combination of
columns as a table's primary key. To satisfy a PRIMARY
KEY constraint, both of these conditions must be true:

no primary key value can appear in more than one row in the table.

no column that is part of the primary key can contain null.

A table can have only one primary key.

You can use the column_constraint syntax to define a
primary key on a single column. The constraint below
ensures that no two departments in the table have
the same department number and that no department
number is NULL.

A composite primary key is a primary key made up of a
combination of columns. Because ORACLE creates an index
on the columns of a primary key, a composite primary
key can contain a maximum of 16 columns. To define a
composite primary key, you must use the table_constraint
syntax, rather than the column_constraint syntax.

REFERENTIAL INTEGRITY constraint - designates a column or
combination of columns as a foreign key and establishes
a relationship between that foreign key and a specified
primary or unique key, called the referenced key. In
this relationship, the table containing the foreign key
is called the child table and the table containing the
referenced key is called the parent table. Note these
caveats:

The child and parent tables must be on the same
database. They cannot be on different nodes of a
distributed database.

The foreign key and the referenced key can be in the
same table. In this case, the parent and child
tables are the same.

To satisfy a referential integrity constraint, each row
of the child table must meet one of these conditions:

The value of the row's foreign key must appear as
a referenced key value in one of the parent table's
rows. The row in the child table is said to depend
on the referenced key in the parent table.

The value of one of the columns that makes up the
foreign key must be null.

A referential integrity constraint is defined in the
child table. A referential integrity constraint
definition can include any of these keywords:

FOREIGN KEY - identifies the column or combination of
columns in the child table that makes up of the
foreign key. Only use this keyword when you
define a foreign key with a table constraint
clause.

REFERENCES - identifies the parent table and the column
or combination of columns that make up the
referenced key. The referenced key columns must
be of the same number and datatypes as the
foreign key columns.

ON DELETE CASCADE - allows deletion of referenced key
values in the parent table that have dependent
rows in the child table and causes ORACLE to
automatically delete dependent rows from the
child table to maintain referential integrity.
If you omit this option, ORACLE forbids deletion
of referenced key values in the parent table
that have dependent rows in the child table.

Before you define a referential integrity constraint in
the child table, the referenced UNIQUE or PRIMARY KEY
constraint on the parent table must already be defined.
Before you enable a referential integrity constraint,
its referenced constraint must be enabled.

You can use column_constraint syntax to define a
referential integrity constraint in which the foreign
key is made up of a single column. The constraint below
ensures that all employees in the EMP table work in a
department in the DEPT table. However, employees can
have null department numbers.

Note that both of these foreign key definitions omit
the ON DELETE CASCADE option, causing ORACLE to forbid
the deletion of a department if any employee works in
that department.

CHECK constraint - explicitly defines a condition. To satisfy
the constraint, each row in the table must make the
condition either TRUE or unknown (due to a null). The
condition of a CHECK constraint can refer to any column
in the table, but it cannot refer to columns of other
tables. If you create multiple CHECK constraints for
a column, design them carefully so their purposes do
not conflict. ORACLE does not verify that CHECK
conditions are not mutually exclusive.

Note that there are many more commands that are not included in this
manual. And some of these commands included in this section may have
information missing from them. The commands and missing information
can be obtained from chapter 4 of ``ORACLE7 Server SQL Language
Reference Manual''.