# 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:

     SELECT ENAME, SAL*12, MONTHS_BETWEEN(HIREDATE,SYSDATE) FROM EMP

SELECT ENAME,
SQL * 12,
MONTHS_BETWEEN( HIREDATE, SYSDATE )
FROM EMP


Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names.

## Schema Objects

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.

## Object Naming Rules

This section lists rules for the names of objects and their parts.

1. Names must be from 1 to 30 bytes long.

2. Names cannot contain quotation marks.

3. Names are not case-sensitive.

4. A name must begin with an alphabetic character.

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

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

7. 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.

8. 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.

9. 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.

10. 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.

## Referring to Objects and Parts

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.

For example, this statement drops the EMP table in the schema SCOTT:

          DROP TABLE scott.emp


## Datatypes

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''.

### Character Datatypes

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 Datatypes

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 Datatypes

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.
• SQL functions (such as SUBSTR or INSTR).
• expressions or conditions.
• select lists of queries containing GROUP BY clauses.
• select lists of subqueries or queries combined by set operators.
• select lists of CREATE TABLE AS SELECT statements.

### Date Datatype

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:


SELECT TO_CHAR( TO_DATE('01-01-1992', 'MM-DD-YYYY', 'J')
FROM DUAL

TO_CHAR(TO_DATE('01-01-1992','MM-DD-YYYY','J')
----------------------------------------------
2448623


### Raw and Long Raw Datatype

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.

### RowID Datatype

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''.

## Data Conversion

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.

## Nulls

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.

## Operators

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''.

Arithmetic Operators Comparison Operators
unary       + -
arithmetic  * /
binary      + - 
equality       =
inequality     !=, <>
greater        >, >=
less           <, <=
equal to any   IN, =ANY
not equal to   NOT IN, !=ANY
any            =ANY, !=ANY, >ANY, =ANY
all            =ALL, !=ALL, >ALL, =ALL
between        [NOT] BETWEEN x ANY y
exists         EXISTS
like           x [NOT] LIKE y [ESCAPE z]
null            IS [NOT] NULL

Logical Operators
not         NOT
and         AND
or          OR
Character Operators
concatenate ||

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. - All rows selected by either query, including all duplicates. - All distinct rows selected by both queries. - 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.

Example 2

          CREATE TABLE tab1 (col1 VARCHAR(6), col2 CHAR(6),
col3 VARCHAR(6), col4 CHAR(6) )

INSERT INTO tab1 (col1,  col2,     col3,  col4)
VALUES ('abc', 'def   ', 'ghi   ', 'jkl')

SELECT col1||col2||col3||col4 Concatenation''
FROM tab1

Concatenation
-------------
abcdef   ghi   jkl


## Functions

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''.

### Single Row Functions

 ABS(n) Returns the absolute value of n. Returns smallest integer greater than or equal to n. Returns the cosine of n (angle expressed in radians). Returns the hyperbolic cosine of n. Returns e raised to the nth power. Returns largest integer equal to or less than n. Returns the natural logarithm of n (for n > 0). Returns the logarithm, base m, of n. (m <> 0 or 1). Returns remainder of m divided by n. Returns m raised to the nth power (m**n). Returns n rounded to m places right of the decimal. Returns -1 if n<0; 0 if n=0; 1 if n>0. Returns the sine of n. Returns the hyperbolic sine of n. Returns square root of n. Returns the tangent of n. Returns the hyperbolic tangent of n. Returns n truncated to m decimal places; else m=0.

 CHR(n) Returns the character having binary equivalent to n. Returns c1 concatenated with c2. Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Returns char, with all letters lowercase. Returns c1, left-padded to length n with the sequence of characters in c2; c2 defaults to \space', a single blank. 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. 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. 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. Returns char, with final characters removed after the last character not in set; set defaults to \space'. 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. 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. The same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than in characters. 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. Returns char, with all letters uppercase. Returns the decimal representation in the database set of the first byte of char. 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. Returns the length of char in characters.

 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. 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. 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. 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  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. Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day. 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. 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 

 CHARTOROWID(char) Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype. Converts char containing hexadecimal digits to a raw value. Converts raw to a character value containing its hexadecimal equivalent. Converts a ROWID value to VARCHAR2 datatype. The result of this conversion is always 18 characters long. 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. 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. 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. 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.

 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. 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. 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. Returns an integer that uniquely identifies the current user. Returns the current ORACLE user with the datatype VARCHAR2. 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.

### Group Functions

Many group functions accept these options:

 DISTINCT 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. 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. Returns maximum value of expr. Returns minimum value of expr. 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. Returns sum of values of n. Returns variance of x, a number. For the variance formula see page 3-48 of ORACLE7 Server SQL Language Reference Manual''.

## Format Models

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.

### Number Format Models

You can use number format models in these places:

• 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.

Element Description Initialization Parameter
D Decimal character NLS_NUMERIC_CHARACTERS
G Group separator NLS_NUMERIC_CHARACTERS
C ISO currency symbol NLS_ISO_CURRENCY
L Local currency symbol NLS_CURRENCY

### Date Format Models

You can use date format models in these places:

• 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.
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.

The following suffixes can be added to date format elements:

Suffix Meaning Example
Element
Example
Value
TH Ordinal number DDTH 4TH
SP Spelled number DDSP FOUR
SPTH or THSP Spelled, ordinal number DDSPTH FOURTH

Capitalization in a spelled-out word, abbreviation, or Roman numeral follows capitalization in the corresponding format element. For example, the date format model 'DAY' produces capitalized words like 'MONDAY'; 'Day' produces 'Monday'; and 'day' produces 'monday'.

You can also include these characters in a date format model:

• punctuation such as hyphens, slashes, commas, periods, and colons
• character literals

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.

FM - Fill Mode'' This modifier suppresses blank padding in the return value of the TO_CHAR function.

• 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:

          SELECT TO_CHAR (number, 'fmt') FROM dual

number                  'fmt'           Result
123.456                 999.999         ' 123.456'
123.456                 9.9EEEE         '  1.2E+02'
1E+123                  9.9EEEE         ' 1.0E+123'
123.456                 FM9.9EEEE       '1.2E+02'
123.456                 FM999.009       '123.456'
123.45                  FM999.009       '123.45'
123.0                   FM999.009       '123.00'
123.45                  L999.99         '     $123.45' 123.45 FML99.99 '$123.45'

SELECT TO_CHAR(SYSDATE, 'fmDDTH "of" Month, YYYY') Ides
FROM DUAL

Ides
------------------
3RD of April, 1992

SELECT TO_CHAR(SYSDATE, 'DDTH "of" Month, YYYY') Ides
FROM DUAL

Ides
------------------
03RD of April    , 1992


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:

          UPDATE table SET date_column = TO_DATE(char, 'fmt');

char                    'fmt'                   Match or Error?
'15/ JAN /1993'         'DD-MON-YYYY'           Match
' 15! JAN %1993'        'DD-MON-YYYY'           Match
'15/JAN/1993'           'FXDD-MON-YYYY'         Error
'15-JAN-1993'           'FXDD-MON-YYYY'         Match
'1-JAN-1993'            'FXDD-MON-YYYY'         Error
'01-JAN-1993'           'FXDD-MON-YYYY'         Error
'1-JAN-1993'            'FXFMDD-MON-YYYY'       Match


## Data Definition Language (DDL) SQL Commands

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:

1. Data Definition Language commands
2. Data Manipulation Language commands
3. Transaction Control commands
4. Session Control commands

Data Definition Language commands allow you to perform these tasks:

1. - create, alter, and drop objects
2. - 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.

### CREATE SCHEMA

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.

Example

     CREATE SCHEMA AUTHORIZATION blair
CREATE TABLE sox (color VARCHAR2(10) PRIMARY KEY,
quantity NUMBER)
CREATE VIEW red_sox AS
SELECT color, quantity FROM sox WHERE color = 'RED'
GRANT select ON red_sox TO waites;


### CREATE TABLE

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.

Example

     CREATE SCHEMA scott.emp
(empno NUMBER	CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL,
CONSTRAINT upper_ename CHECK (ename = UPPER(ename)),
job VARCHAR2(9),
mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno),
hiredate DATE DEFAULT SYSDATE,
sal NUMBER(10,2) CONSTRAINT ck_sal CHECK(sal>500),
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL,
CONSTRAINT fk_deptno REFERENCES scott.dept(deptno))
PCTFREE 5 PCTUSED 75;


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.

### CREATE VIEW

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

• 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.

Example

     CREATE VIEW clerk (id_number, person, department, position)
AS SELECT empno, ename, deptno, job
FROM emp
WHERE job = 'CLERK'
WITH CHECK OPTION CONSTRAINT wco


Because of the CHECK OPTION, you cannot subsequently insert a new row into CLERK if the new employee is not a clerk.

### GRANT

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:

          CREATE TABLE dependent
(dependno NUMBER,
dependname VARCHAR2(10),
employee NUMBER CONSTRAINT in_emp REFERENCES scott.emp(empno))


The constraint IN_EMP ensures that all dependents in the DEPENDENT table correspond to an employee in the EMP table in the schema SCOTT.

### CREATE INDEX

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.

Example

          CREATE INDEX i_emp_ename
ON emp (ename)


### CREATE ROLE

A role is a set of privileges that can be granted to users or to other roles. You must have CREATE ROLE system privilege to perform this command.

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

NOT IDENTIFIED - indicates that a user granted the role need not be verified when enabling it.

IDENTIFIED - indicates that a user granted the role must be verified when enabling it with the SET ROLE command:

• BY password - the user must specify the password to ORACLE when enabling the role.
• EXTERNALLY - the operating system verifies the user enabling to the role. This is the default.

You can add privileges to a role's privilege domain and then grant the role to a user. The user can then enable the role and exercise the privileges in the role's privilege domain.

Example

          CREATE ROLE teller
IDENTIFIED BY cashflow;
GRANT select ON emp To teller;
GRANT teller TO user;


Users who are subsequently granted the TELLER role must specify the passwords CASHFLOW to enable the role.

### CREATE SEQUENCE

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.

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.

### CREATE SYNONYM

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.

### ALTER TABLE

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

• 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.

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.

Example

          ALTER TABLE emp
loancode CHAR(1) NOT NULL);

ALTER TABLE emp
MODIFY (thriftplan NUMBER(9,2));

ALTER TABLE dept


For more information on DROP see DROP clause on page 4-254 of ORACLE7 Server SQL Language Reference Manual''.

### DROP TABLE

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''.

### TRUNCATE

This command can be used to remove all rows from a table or cluster.

TABLE - specifies the schema and name of the table to be truncated.

DROP STORAGE - deallocates the space from the deleted rows from the table or cluster.

You can use the TRUNCATE command to quickly remove all rows from a table or cluster. Removing rows with the TRUNCATE command is faster than removing them with the DELETE command for these reasons:

• the TRUNCATE command is a DDL command and generates no rollback information.

• truncating a table does not fire the table's DELETE triggers.

Deleting rows with the TRUNCATE command is also more convenient than dropping and recreating a table for these reasons:

• dropping and recreating invalidates the table's dependent objects, while truncating does not.

• dropping and recreating requires you to regrant object privileges on the table, while truncating does not.

• dropping and recreating requires you to recreate the table's indexes, integrity constraints, and triggers and respecify its STORAGE parameters, while truncating does not.

You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table.

Example

          TRUNCATE TABLE emp;


### CONSTRAINT clause

The CONSTRAINT command is used to define an integrity constraint. CONSTRAINT clauses can appear in either CREATE TABLE or ALTER TABLE commands.

CONSTRAINT - identifies the integrity constraint by the name constraint. ORACLE stores this name in the data dictionary along with the definition of the integrity constraint.

NULL - specifies that a column can contain null values.

NOT NULL - specifies that a column cannot contain null values.

UNIQUE - designates a column or combination of columns as a unique key.

PRIMARY KEY - designates a column or combination of columns as the table's primary key.

FOREIGN KEY - designates a column or combination of columns as the foreign key in a referential integrity constraint.

REFERENCES - identifies the primary or unique key that is referenced by a foreign key in a referential integrity constraint.

ON DELETE CASCADE - specifies that ORACLE maintains referential integrity by automatically removing dependent foreign key values if you remove a referenced primary or unique key value.

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.

          CREATE TABLE dept
(deptno NUMBER,
dname  VARCHAR2(9) CONSTRAINT unq_dname UNIQUE,
loc    VARCHAR2(10) )


Alternatively, you can define and enable this constraint with the table_constraint syntax.

          CREATE TABLE dept
(deptno NUMBER,
dname  VARCHAR2(9),
loc    VARCHAR2(10),
CONSTRAINT unq_dname UNIQUE (dname)
USING INDEX PCTFREE 20 TABLESPACE user_x
STORAGE (INITIAL 8K NEXT 6K) )


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.

          CREATE TABLE census
USING INDEX PCTFREE 5 TABLESPACE user_y


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.

          CREATE TABLE dept
(deptno NUMBER  CONSTRAINT pk_dept PRIMARY KEY,
dname  VARCHAR2(9),
loc    VARCHAR2(10) )


Alternatively, you can define and enable this constraint with the table_constraint syntax.

          CREATE TABLE dept
(deptno NUMBER,
dname  VARCHAR2(9),
loc    VARCHAR2(10),
CONSTRAINT pk_dept PRIMARY KEY (deptno) )


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.

          ALTER TABLE ship_cont
ADD PRIMARY KEY (ship_no, container_no) DISABLE;


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.

\begin{verbatim}
CREATE TABLE emp
(empno     NUMBER(4),
ename     VARCHAR2(10),
job       VARCHAR2(9),
mgr       NUMBER(4),
hiredate  DATE,
sal       NUBMER(7,2),
comm      NUMBER(7,2),
deptno    NUMBER(2)
CONSTRAINT fk_deptno REFERENCES dept(deptno) );


Alternatively, you can define a referential integrity constraint with table_constraint syntax:

          CREATE TABLE emp
(empno     NUMBER(4),
ename     VARCHAR2(10),
job       VARCHAR2(9),
mgr       NUMBER(4),
hiredate  DATE,
sal       NUBMER(7,2),
comm      NUMBER(7,2),
deptno    NUMBER(2),
CONSTRAINT fk_deptno FOREIGN KEY (deptno)
REFERENCES dept(deptno) );


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.

          CREATE TABLE dept
(deptno NUMBER CONSTRAINT check_deptno
CHECK (deptno BETWEEN 10 AND 99),
dname  VARCHAR2(9) CONSTRAINT check_dname
CHECK (dname = UPPER(dname)),
loc    VARCHAR2(10) CONSTRAINT check_loc
CHECK (loc IN ('DALLAS','BOSTON'))  );

CREATE TABLE order_detail
(CONSTRAINT pk_od PRIMARY KEY (order_id,part_no),
order_id  NUMBER CONSTRAINT fk_oid
REFERENCES scott.order (order_id),
part_no   NUMBER CONSTRAINT fk_pno
REFERENCES scott.part (part_no),
quantity  NUMBER CONSTRAINT nn_qty NOT NULL
CONSTRAINT check_qty_low CHECK ( quantity > 0),
cost      NUMBER CONSTRAINT check_cost CHECK ( cost > 0)  );


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''.