Oracle8i SQL Reference
Release 3 (8.1.7)

Part Number A85397-01

Library

Product

Contents

Index

Go to previous page Go to next page

2
Basic Elements of Oracle SQL

This chapter contains reference information on the basic elements of Oracle SQL. These elements are simplest building blocks of SQL statements. Therefore, before using the statements described in Chapter 7 through Chapter 11, you should familiarize yourself with the concepts covered in this chapter, as well as in Chapter 3, "Operators", Chapter 4, "Functions", Chapter 5, "Expressions, Conditions, and Queries", and Chapter 6, "About SQL Statements".

This chapter contains these sections:

Datatypes

Each value manipulated by Oracle has a datatype. A value's datatype associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another. For example, you can add values of NUMBER datatype, but not values of RAW datatype.

When you create a table or cluster, you must specify a datatype for each of its columns. When you create a procedure or stored function, you must specify a datatype for each of its arguments. These datatypes define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a column assumes the column's datatype. For example, if you insert '01-JAN-98' into a DATE column, Oracle treats the '01-JAN-98' character string as a DATE value after verifying that it translates to a valid date.

Oracle provides a number of built-in datatypes as well as several categories for user-defined types, as shown in Figure 2-1.

Figure 2-1 Oracle Type Categories


The syntax of the Oracle built-in datatypes appears in the next diagram. Table 2-1 summarizes Oracle built-in datatypes. The rest of this section describes these datatypes as well as the various kinds of user-defined types.


Note: The Oracle precompilers recognize other datatypes in embedded SQL programs. These datatypes are called external datatypes and are associated with host variables. Do not confuse built-in and user-defined datatypes with external datatypes. For information on external datatypes, including how Oracle converts between them and built-in or user-defined datatypes, see Pro*COBOL Precompiler Programmer's Guide, Pro*C/C++ Precompiler Programmer's Guide, and SQL*Module for Ada Programmer's Guide


built-in datatypes:


The ANSI-supported datatypes appear in the figure that follows. Table 2-2 shows the mapping of ANSI-supported datatypes to Oracle build-in datatypes.

ANSI-supported datatypes:


Table 2-1  Built-In Datatype Summary
Codea  Built-In Datatype  Description 

VARCHAR2(size) 

Variable-length character string having maximum length size bytes. Maximum size is 4000, and minimum is 1. You must specify size for VARCHAR2

NVARCHAR2(size) 

Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2

NUMBER(p,s) 

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. 

LONG 

Character data of variable length up to 2 gigabytes, or 231 -1 bytes. 

12 

DATE 

Valid date range from January 1, 4712 BC to December 31, 9999 AD. 

23 

RAW(size) 

Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value. 

24 

LONG RAW 

Raw binary data of variable length up to 2 gigabytes. 

69 

ROWID 

Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn. 

208 

UROWID [(size)] 

Hexadecimal string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes. 

96 

CHAR(size) 

Fixed-length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte. 

96 

NCHAR(size) 

Fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set. 

112 

CLOB 

A character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the CHAR database character set. Maximum size is 4 gigabytes.  

112 

NCLOB 

A character large object containing multibyte characters. Both fixed-width and variable-width character sets are supported, both using the NCHAR database character set. Maximum size is 4 gigabytes. Stores national character set data. 

113 

BLOB 

A binary large object. Maximum size is 4 gigabytes.  

114 

BFILE 

Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes. 

Character Datatypes

Character datatypes store character (alphanumeric) data, which are words and free-form text, in the database character set or national character set. They are less restrictive than other datatypes and consequently have fewer properties. For example, character columns can store all alphanumeric values, but NUMBER columns can store only numeric values.

Character data is stored in strings with byte values corresponding to one of the character sets, such as 7-bit ASCII or EBCDIC, specified when the database was created. Oracle supports both single-byte and multibyte character sets.

These datatypes are used for character data:

CHAR Datatype

The CHAR datatype specifies a fixed-length character string. When you create a table with a CHAR column, you 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 character and the maximum allowed is 2000 characters. A zero-length string can be inserted into a CHAR column, but the column is blank-padded to 1 character when used in comparisons.

See Also: "Datatype Comparison Rules" for information on comparison semantics 


Note: To ensure proper data conversion between databases with different character sets, you must ensure that CHAR data consists of well-formed strings. See Oracle8i National Language Support Guide for more information on character set support. 


NCHAR Datatype

The NCHAR datatype specifies a fixed-length national character set character string. When you create a table with an NCHAR column, you define the column length either in characters or in bytes. You define the national character set when you create your database.

If the national character set of the database is fixed width, such as JA16EUCFIXED, then you declare the NCHAR column size as the number of characters desired for the string length. If the national character set is variable width, such as JA16SJIS, you declare the column size in bytes. The following statement creates a table with one NCHAR column that can store strings up to 30 characters in length using JA16EUCFIXED as the national character set:

CREATE TABLE tab1 (col1 NCHAR(30));

The column's maximum length is determined by the national character set definition. Width specifications of character datatype NCHAR refer to the number of characters if the national character set is fixed width and refer to the number of bytes if the national character set is variable width. The maximum column size allowed is 2000 bytes. For fixed-width, multibyte character sets, the maximum length of a column allowed is the number of characters that fit into no more than 2000 bytes.

If you insert a value that is shorter than the column length, Oracle blank-pads the value to column length. You cannot insert a CHAR value into an NCHAR column, nor can you insert an NCHAR value into a CHAR column.

The following example compares the col1 column of tab1 with national character set string 'NCHAR literal':

SELECT * FROM tab1 WHERE col1 = N'NCHAR literal';

NVARCHAR2 Datatype

The NVARCHAR2 datatype specifies a variable-length national character set character string. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters or bytes it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length.

The column's maximum length is determined by the national character set definition. Width specifications of character datatype NVARCHAR2 refer to the number of characters if the national character set is fixed width and refer to the number of bytes if the national character set is variable width. The maximum column size allowed is 4000 bytes. For fixed-width, multibyte character sets, the maximum length of a column allowed is the number of characters that fit into no more than 4000 bytes.

The following statement creates a table with one NVARCHAR2 column of 2000 characters in length (stored as 4000 bytes, because each character takes two bytes) using JA16EUCFIXED as the national character set:

CREATE TABLE tab1 (col1 NVARCHAR2(2000));

VARCHAR2 Datatype

The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you 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 the value does not exceed the column's maximum length. If you try to insert a value that exceeds the specified length, Oracle returns an error.

You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual length of the string stored is permitted to be zero. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.

See Also: "Datatype Comparison Rules" for information on comparison semantics 


Note: To ensure proper data conversion between databases with different character sets, you must ensure that VARCHAR2 data consists of well-formed strings. See Oracle8i National Language Support Guide for more information on character set support. 


VARCHAR Datatype

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics.

NUMBER Datatype

The NUMBER datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, Oracle returns an error.

Specify a fixed-point number using the following form:

NUMBER(p,s)

where:

Specify an integer using the following form:

Specify a floating-point number using the following form:

Scale and Precision

Specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, Oracle returns an error. If a value exceeds the scale, Oracle rounds it.

The following examples show how Oracle stores data using different precisions and scales.

7456123.89 

NUMBER 

7456123.89 

7456123.89 

NUMBER(9) 

7456124 

7456123.89 

NUMBER(9,2) 

7456123.89 

7456123.89 

NUMBER(9,1) 

7456123.9 

7456123.89 

NUMBER(6) 

exceeds precision 

7456123.89 

NUMBER(7,-2) 

7456100 

7456123.89 

NUMBER(-7,2) 

exceeds precision 

Negative Scale

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.

Scale Greater than Precision

You can specify a scale that is greater than precision, although it is uncommon. In this case, the precision specifies the maximum number of digits to the right of the decimal point. As with all number datatypes, if the value exceeds the precision, Oracle returns an error message. If the value exceeds the scale, Oracle rounds the value. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point. The following examples show the effects of a scale greater than precision:

Actual Data  Specified As  Stored As 

.01234 

NUMBER(4,5) 

.01234 

.00012 

NUMBER(4,5) 

.00012 

.000127 

NUMBER(4,5) 

.00013 

.0000012 

NUMBER(2,7) 

.0000012 

.00000123 

NUMBER(2,7) 

.0000012 

Floating-Point Numbers

Oracle allows you to specify floating-point numbers, which can have a decimal point anywhere from the first to the last digit or can have no decimal point at all. A scale value is not applicable to floating-point numbers, because the number of digits that can appear after the decimal point is not restricted.

You can specify floating-point numbers with the form discussed in "NUMBER Datatype". Oracle also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:

LONG Datatype

LONG columns store variable-length character strings containing up to 2 gigabytes, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer.


Note: Oracle Corporation strongly recommends that you convert LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. See "TO_LOB" for more information. 


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

The use of LONG values is subject to some restrictions:

LONG columns cannot appear in certain parts of SQL statements:

Triggers can use the LONG datatype in the following manner:

You can use the Oracle Call Interface functions to retrieve a portion of a LONG value from the database.

See Also: Oracle Call Interface Programmer's Guide 

DATE Datatype

The DATE datatype stores 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, Oracle stores the following information: century, year, month, day, hour, minute, and second.

If you specify a date value without a time component, the default time is 12:00:00 AM (midnight). If you specify a time 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.

The default date format is specified by the initialization parameter NLS_DATE_FORMAT and is a string such as 'DD-MON-YY'. This example default date format includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year. Oracle automatically converts character values that are in the default date format into DATE values when they are used in date expressions.

To specify a date value that is not in the default format, you must convert a character or numeric value to a date value with the TO_DATE function. In this case, you must specify the nondefault date format model (sometimes called a "date mask") to tell Oracle how to interpret the character or numeric value. For example, the date format model for '17:45:29' is 'HH24:MI:SS'. The date format model for '11-NOV-1999' is 'DD-MON-YYYY'.

See Also:

 

Date Arithmetic

You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the hiredate column of the emp table from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values.

Oracle provides functions for many common date operations. For example, the ADD_MONTHS function lets you add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.

Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours.

See Also:

"Date Functions" for more information on date functions 

Using Julian Dates

A Julian date is the number of days since January 1, 4712 BC. 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.

Example

This statement returns the Julian equivalent of January 1, 1997:

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

TO_CHAR
--------
2450450

See Also: "Selecting from the DUAL Table" for a description of the DUAL table 

RAW and LONG RAW Datatypes

The RAW and LONG RAW datatypes store data that is not to be interpreted (not explicitly converted when moving data between different systems) by Oracle. These datatypes are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use.


Note: Oracle Corporation strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns. See TO_LOB for more information. 


RAW is a variable-length datatype like VARCHAR2, except that Net8 (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, Net8 and Import/Export automatically convert CHAR, VARCHAR2, and LONG data from the database character set to the user session character set (which you can set with the NLS_LANGUAGE parameter of the ALTER SESSION statement), if the two character sets are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.

Large Object (LOB) Datatypes

The built-in LOB datatypes BLOB, CLOB, and NCLOB (stored internally), and the BFILE (stored externally), can store large and unstructured data such as text, image, video, and spatial data up to 4 gigabytes in size.

When creating a table, you can optionally specify different tablespace and storage characteristics for LOB columns or LOB object attributes from those specified for the table.

LOB columns contain LOB locators that can refer to out-of-line or in-line LOB values. Selecting a LOB from a table actually returns the LOB's locator and not the entire LOB value. The DBMS_LOB package and Oracle Call Interface (OCI) operations on LOBs are performed through these locators.

LOBs are similar to LONG and LONG RAW types, but differ in the following ways:

You can access and populate rows of an internal LOB column (a LOB column stored in the database) simply by issuing an INSERT or UPDATE statement. However, to access and populate a LOB attribute that is part of an object type, you must first initialize the LOB attribute using the EMPTY_CLOB or EMPTY_BLOB function. You can then select the empty LOB attribute and populate it using the DBMS_LOB package or some other appropriate interface.

See Also: "EMPTY_[B | C]LOB" 

The following example creates a table with LOB columns. (It assumes the existence of tablespace resumes).

CREATE TABLE  person_table (name CHAR(40),
                            resume  CLOB,
                            picture BLOB)
  LOB (resume) STORE AS
               ( TABLESPACE resumes 
                  STORAGE (INITIAL 5M NEXT 5M) );

See Also:

 

BFILE Datatype

The BFILE datatype enables access to binary file LOBs that are stored in file systems outside the Oracle database. A BFILE column or attribute stores a BFILE locator, which serves as a pointer to a binary file on the server's file system. The locator maintains the directory alias and the filename.

Binary file LOBs do not participate in transactions and are not recoverable. Rather, the underlying operating system provides file integrity and durability. The maximum file size supported is 4 gigabytes.

The database administrator must ensure that the file exists and that Oracle processes have operating system read permissions on the file.

The BFILE datatype allows read-only support of large binary files. You cannot modify or replicate such a file. Oracle provides APIs to access file data. The primary interfaces that you use to access file data are the DBMS_LOB package and the OCI.

See Also:

 

BLOB Datatype

The BLOB datatype stores unstructured binary large objects. BLOBs can be thought of as bitstreams with no character set semantics. BLOBs can store up to 4 gigabytes of binary data.

BLOBs have full transactional support. Changes made through SQL, the DBMS_LOB package, or the OCI participate fully in the transaction. BLOB value manipulations can be committed and rolled back. Note, however, that you cannot save a BLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.

CLOB Datatype

The CLOB datatype stores single-byte character data. Both fixed-width and variable-width character sets are supported, and both use the CHAR database character set. CLOBs can store up to 4 gigabytes of character data.

CLOBs have full transactional support. Changes made through SQL, the DBMS_LOB package, or the OCI participate fully in the transaction. CLOB value manipulations can be committed and rolled back. Note, however, that you cannot save a CLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.

NCLOB Datatype

The NCLOB datatype stores multibyte national character set character (NCHAR) data. Both fixed-width and variable-width character sets are supported. NCLOBs can store up to 4 gigabytes of character text data.

NCLOBs have full transactional support. Changes made through SQL, the DBMS_LOB package, or the OCI participate fully in the transaction. NCLOB value manipulations can be committed and rolled back. Note, however, that you cannot save an NCLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.

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. These strings have the datatype ROWID. You can also create tables and clusters that contain actual columns having the ROWID datatype. Oracle does not guarantee that the values of such columns are valid rowids.

See Also:

"Pseudocolumns" for more information on the ROWID pseudocolumn 

Restricted Rowids

Beginning with Oracle8, Oracle SQL incorporated an extended format for rowids to efficiently support partitioned tables and indexes and tablespace-relative data block addresses (DBAs) without ambiguity.

Character values representing rowids in Oracle7 and earlier releases are called restricted rowids. Their format is as follows:

block.row.file

where:

block 

is a hexadecimal string identifying the data block of the datafile containing the row. The length of this string depends on your operating system. 

row 

is a four-digit hexadecimal string identifying the row in the data block. The first row of the block has a digit of 0. 

file 

is a hexadecimal string identifying the database file containing the row. The first datafile has the number 1. The length of this string depends on your operating system. 

Extended Rowids

The extended ROWID datatype stored in a user column includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. You can retrieve the data object number from data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number.

Extended rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, as well as the plus sign (+) and forward slash (/). Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid, as well as information specific to extended rowids.

See Also: Oracle8i Supplied PL/SQL Packages Reference for information on the functions available with the DBMS_ROWID package and how to use them 

Compatibility and Migration

The restricted form of a rowid is still supported in Oracle8i for backward compatibility, but all tables return rowids in the extended format.

See Also: Oracle8i Migration for information regarding compatibility and migration issues 

UROWID Datatype

Each row in a database has an address. However, the rows of some tables have addresses that are not physical or permanent, or were not generated by Oracle. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Rowids of foreign tables (such as DB2 tables accessed through a gateway) are not standard Oracle rowids.

Oracle uses "universal rowids" (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids. Both types of urowid are stored in the ROWID pseudocolumn (as are the physical rowids of heap-organized tables).

Oracle creates logical rowids based on a table's primary key. The logical rowids do not change as long as the primary key does not change. The ROWID pseudocolumn of an index-organized table has a datatype of UROWID. You can access this pseudocolumn as you would the ROWID pseudocolumn of a heap-organized (that is, using the SELECT ROWID statement). If you wish to store the rowids of an index-organized table, you can define a column of type UROWID for the table and retrieve the value of the ROWID pseudocolumn into that column.


Note: Heap-organized tables have physical rowids. Oracle Corporation does not recommend that you specify a column of datatype UROWID for a heap-organized table. 


See Also:

 

ANSI, DB2, and SQL/DS Datatypes

SQL statements that create tables and clusters can also use ANSI datatypes and datatypes from IBM's products SQL/DS and DB2. Oracle recognizes the ANSI or IBM datatype name and records it as the name of the datatype of the column, and then stores the column's data in an Oracle datatype based on the conversions shown in Table 2-2 and Table 2-3.

Table 2-2 ANSI Datatypes Converted to Oracle Datatypes
ANSI SQL Datatype  Oracle Datatype 

CHARACTER(n)

CHAR(n) 

CHAR(n) 

CHARACTER VARYING(n)

CHAR VARYING(n) 

VARCHAR(n) 

NATIONAL CHARACTER(n)

NATIONAL CHAR(n)

NCHAR(n) 

NCHAR(n) 

NATIONAL CHARACTER VARYING(n)

NATIONAL CHAR VARYING(n)

NCHAR VARYING(n) 

NVARCHAR2(n) 

NUMERIC(p,s)

DECIMAL(p,s)

NUMBER(p,s) 

INTEGER

INT

SMALLINT 

NUMBER(38) 

FLOAT(b)b

DOUBLE PRECISIONc

REAL

NUMBER 

aThe NUMERIC and DECIMAL datatypes can specify only fixed-point numbers. For these datatypes, s defaults to 0.
bThe FLOAT datatype is a floating-point number with a binary precision b. The default precision for this datatype is 126 binary, or 38 decimal.
cThe DOUBLE PRECISION datatype is a floating-point number with binary precision 126.
dThe REAL datatype is a floating-point number with a binary precision of 63, or 18 decimal.
 
Table 2-3 SQL/DS and DB2 Datatypes Converted to Oracle Datatypes
SQL/DS or DB2 Datatype  Oracle Datatype 

CHARACTER(n) 

CHAR(n) 

VARCHAR(n) 

VARCHAR(n) 

LONG VARCHAR(n) 

LONG 

DECIMAL(p,s)

NUMBER(p,s) 

INTEGER

SMALLINT 

NUMBER(38) 

FLOAT(b)b 

NUMBER 

aThe DECIMAL datatype can specify only fixed-point numbers. For this datatype, s defaults to 0.
bThe FLOAT datatype is a floating-point number with a binary precision b. The default precision for this datatype is 126 binary, or 38 decimal.
 

Do not define columns with these SQL/DS and DB2 datatypes, because they have no corresponding Oracle datatype:

Note that data of type TIME and TIMESTAMP can also be expressed as Oracle DATE data.

User-Defined Type Categories

User-defined datatypes use Oracle built-in datatypes and other user-defined datatypes as the building blocks of types that model the structure and behavior of data in applications.

The sections that follow describe the various categories of user-defined types.

See Also:

 

Object Types

Object types are abstractions of the real-world entities, such as purchase orders, that application programs deal with. An object type is a schema object with three kinds of components:

REFs

An object identifier (OID) uniquely identifies an object and enables you to reference the object from other objects or from relational tables. A datatype category called REF represents such references. A REF is a container for an object identifier. REFs are pointers to objects.

When a REF value points to a nonexistent object, the REF is said to be "dangling". A dangling REF is different from a null REF. To determine whether a REF is dangling or not, use the predicate IS [NOT] DANGLING. For example, given table dept with column mgr whose type is a REF to type emp_t, which has an attribute name:

SELECT t.mgr.name
   FROM dept t 
   WHERE t.mgr IS NOT DANGLING;

Varrays

An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.

The number of elements in an array is the size of the array. Oracle arrays are of variable size, which is why they are called varrays. You must specify a maximum size when you declare the array.

When you declare a varray, it does not allocate space. It defines a type, which you can use as:

Oracle normally stores an array object either in line (that is, as part of the row data) or out of line (in a LOB), depending on its size. However, if you specify separate storage characteristics for a varray, Oracle will store it out of line, regardless of its size.

See Also: The varray_storage_clause of CREATE TABLE 

Nested Tables

A nested table type models an unordered set of elements. The elements may be built-in types or user-defined types. You can view a nested table as a single-column table or, if the nested table is an object type, as a multicolumn table, with a column for each attribute of the object type.

A nested table definition does not allocate space. It defines a type, which you can use to declare:

When a nested table appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single table, which it associates with the enclosing relational or object table.

Datatype Comparison Rules

This section describes how Oracle compares values of each datatype.

Number Values

A larger value is considered greater than a smaller one. All negative numbers are less than zero and all positive numbers. Thus, -1 is less than 100; -100 is less than -1.

Date Values

A later date is considered greater than an earlier one. For example, the date equivalent of '29-MAR-1997' is less than that of '05-JAN-1998' and '05-JAN-1998 1:35pm' is greater than '05-JAN-1998 10:09am'.

Character String Values

Character values are compared using one of these comparison rules:

The following sections explain these comparison semantics. The results of comparing two character values using different comparison semantics may vary. The table below shows the results of comparing five pairs of character values using each comparison semantic. Usually, the results of blank-padded and nonpadded comparisons are the same. The last comparison in the table illustrates the differences between the blank-padded and nonpadded comparison semantics.

Blank-Padded  Nonpadded 

'ab' > 'aa' 

'ab' > 'aa' 

'ab' > 'a ' 

'ab' > 'a   ' 

'ab' > 'a'  

'ab' > 'a'  

'ab' = 'ab' 

'ab' = 'ab' 

'a ' = 'a'  

'a ' > 'a'  

Blank-Padded Comparison Semantics

If the two values have different lengths, Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function.

Nonpadded Comparison Semantics

Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the datatype VARCHAR2 or NVARCHAR2.

Single Characters

Oracle compares single characters according to their numeric values in the database character set. One character is greater than another if it has a greater numeric value than the other in the character set. Oracle considers blanks to be less than any character, which is true in most character sets.

These are some common character sets:

Portions of the ASCII and EBCDIC character sets appear in Table 2-4 and Table 2-5. Note that uppercase and lowercase letters are not equivalent. Also, note that the numeric values for the characters of a character set may not match the linguistic sequence for a particular language.

Table 2-4   ASCII Character Set

Symbol 

Decimal value 

Symbol 

Decimal value 

blank 

32 

; 

59 

! 

33 

< 

60 

" 

34 

= 

61 

# 

35 

> 

62 

$ 

36 

? 

63 

% 

37 

@ 

64 

& 

38 

A-Z 

65-90 

' 

39 

[ 

91 

( 

40 

\ 

92 

) 

41 

] 

93 

* 

42 

^ 

94 

+ 

43 

_ 

95 

, 

44 

` 

96 

- 

45 

a-z 

97-122 

. 

46 

{ 

123 

/ 

47 

| 

124 

0-9 

48-57 

} 

125 

: 

58 

~ 

126 

Table 2-5 EBCDIC Character Set

Symbol 

Decimal value 

Symbol 

Decimal value 

blank 

64 

% 

108 

¢ 

74 

_ 

109 

. 

75 

> 

110 

< 

76 

? 

111 

( 

77 

: 

122 

+ 

78 

# 

123 

| 

79 

@ 

124 

& 

80 

' 

125 

! 

90 

= 

126 

$ 

91 

" 

127 

* 

92 

a-i 

129-137 

) 

93 

j-r 

145-153 

; 

94 

s-z 

162-169 

ÿ 

95 

A-I 

193-201 

- 

96 

J-R 

209-217 

/ 

97 

S-Z 

226-233 

Object Values

Object values are compared using one of two comparison functions: MAP and ORDER. Both functions compare object type instances, but they are quite different from one another. These functions must be specified as part of the object type.

See Also: "CREATE TYPE" and Oracle8i Application Developer's Guide - Fundamentals for a description of MAP and ORDER methods and the values they return 

Varrays and Nested Tables

You cannot compare varrays and nested tables in Oracle8i.

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.

Implicit Data Conversion

Oracle automatically converts a value from one datatype to another when such a conversion makes sense. Oracle performs conversions in these cases:

Implicit Data Conversion Examples

Text Literal Example

The text literal '10' has datatype CHAR. Oracle implicitly converts it to the NUMBER datatype if it appears in a numeric expression as in the following statement:

SELECT sal + '10'
    FROM emp;

Character and Number Values Example

When a condition compares a character value and a NUMBER value, Oracle implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value. In the following statement, Oracle implicitly converts '7936' to 7936:

SELECT ename
    FROM emp 
    WHERE empno = '7936';

Date Example

In the following 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';

Rowid Example

In the following statement, Oracle implicitly converts the text literal 'AAAAZ8AABAAABvlAAA' to a rowid value:

SELECT ename
    FROM emp
    WHERE ROWID = 'AAAAZ8AABAAABvlAAA';

Explicit Data Conversion

You can also explicitly specify datatype conversions using SQL conversion functions. Table 2-6 shows SQL functions that explicitly convert a value from one datatype to another.

Table 2-6 SQL Functions for Datatype Conversion
TO / FROM  CHAR  NUMBER  DATE  RAW  ROWID  LONG/ LONG RAW  LOB 
CHAR 

-- 

TO_NUMBER 

TO_DATE 

HEXTORAW 

CHARTOROWID 

 

 

NUMBER  

TO_CHAR 

-- 

TO_DATE

(number,'J') 

 

 

 

 

DATE  

TO_CHAR 

TO_CHAR

(date,'J') 

-- 

 

 

 

 

RAW 

RAWTOHEX 

 

 

-- 

 

 

 

ROWID 

ROWIDTOCHAR 

 

 

 

-- 

 

 

LONG / LONG RAW 

 

 

 

 

 

-- 

TO_LOB 

LOB 

 

 

 

 

 

 

-- 


Note: You cannot specify LONG and LONG RAW values in cases in which Oracle can perform implicit datatype conversion. For example, LONG and LONG RAW values cannot appear in expressions with functions or operators. For information on the limitations on LONG and LONG RAW datatypes, see "LONG Datatype"


See Also: "Conversion Functions" 

Implicit vs. Explicit Data Conversion

Oracle recommends that you specify explicit conversions rather than rely on implicit or automatic conversions for these reasons:

Literals

The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JACK', 'BLUE ISLAND', and '101' are all character literals; 5001 is a numeric literal. Note that character literals are enclosed in single quotation marks, which enable Oracle to distinguish them from schema object names.

This section contains these topics:

Many SQL statements and functions require you to specify character and numeric literal values. You can also specify literals as part of expressions and conditions. You can specify character literals with the 'text' notation, national character literals with the N'text' notation, and numeric literals with the integer or number notation, depending on the context of the literal. The syntactic forms of these notations appear in the sections that follow.

To specify a datetime or interval datatype as a literal, you must take into account any optional precisions included in the datatypes. Examples of specifying datetime and interval datatypes as literals are provided in the relevant sections of "Datatypes".

Text

Text specifies a text or character literal. You must use this notation to specify values whenever 'text' or char appear in expressions, conditions, SQL functions, and SQL statements in other parts of this reference.

The syntax of text is as follows:

text::=


where

A text literal must be enclosed in single quotation marks. This reference uses the terms text literal and character literal interchangeably.

Text literals have properties of both the CHAR and VARCHAR2 datatypes:

Here are some valid text literals:

'Hello'
'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-98'
N'nchar literal'

See Also:

 

Integer

You must use the integer notation to specify an integer whenever integer appears in expressions, conditions, SQL functions, and SQL statements described in other parts of this reference.

The syntax of integer is as follows:

integer::=


where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

An integer can store a maximum of 38 digits of precision.

Here are some valid integers:

7
+255

See Also:

"Expressions" for the syntax description of expr 

Number

You must use the number notation to specify values whenever number appears in expressions, conditions, SQL functions, and SQL statements in other parts of this reference.

The syntax of number is as follows:

number::=


where

A number can store a maximum of 38 digits of precision.

If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, you must specify numeric literals with 'text' notation. In such cases, Oracle automatically converts the text literal to a numeric value.

For example, if the NLS_NUMERIC_CHARACTERS parameter specifies a decimal character of comma, specify the number 5.123 as follows:

'5,123'

See Also:

ALTER SESSION and Oracle8i Reference 

Here are some valid representations of number:

25
+6.34
0.5
25e-03
-1

See Also:

"Expressions" for the syntax description of expr 

Interval

An interval literal specifies a period of time. You can specify these differences in terms of years and months, or in terms of days, hours, minutes, and seconds. Oracle supports two types of interval literals, YEAR TO MONTH and DAY TO SECOND. Each type contains a leading field and may contain a trailing field. The leading field defines the basic unit of date or time being measured. The trailing field defines the smallest increment of the basic unit being considered. For example, a YEAR TO MONTH interval considers an interval of years to the nearest month. A DAY TO MINUTE interval considers an interval of days to the nearest minute.

If you have date data in numeric form, you can use the NUMTOYMINTERVAL or NUMTODSINTERVAL conversion function to convert the numeric data into interval literals.

Interval literals are used primarily with analytic functions.

See Also:

 

INTERVAL YEAR TO MONTH

Specify YEAR TO MONTH interval literals using the following syntax:


where

Restriction:

The leading field must be a larger time element than the trailing field. For example, INTERVAL '0-1' MONTH TO YEAR is not valid.

The following INTERVAL YEAR TO MONTH literal indicates an interval of 123 years, 2 months:

INTERVAL '123-2' YEAR(3) TO MONTH

Examples of the other forms of the literal follow, including some abbreviated versions:

INTERVAL '123-2' YEAR(3) TO 
MONTH
 

indicates an interval of 123 years, 2 months. You must specify the leading field precision if it is greater than the default of 2 digits. 

INTERVAL '123' YEAR(3)
 

indicates an interval of 123 years 0 months. 

INTERVAL '300' MONTH(3)
 

indicates an interval of 300 months. 

INTERVAL '4' YEAR 
 

maps to INTERVAL '4-0' YEAR TO MONTH and indicates 4 years. 

INTERVAL '50' MONTH
 

maps to INTERVAL '4-2' YEAR TO MONTH and indicates 50 months or 4 years 2 months. 

INTERVAL '123' YEAR
 

returns an error, because the default precision is 2, and '123' has 3 digits. 

You can add or subtract one INTERVAL YEAR TO MONTH literal to or from another to yield another INTERVAL YEAR TO MONTH literal. For example:

INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH TO MONTH = 
INTERVAL '6-11' YEAR TO MONTH

INTERVAL DAY TO SECOND

Specify DAY TO SECOND interval literals using the following syntax:


where

Restriction:

The leading field must be a larger time element than the trailing field. For example, INTERVAL MINUTE TO DAY is not valid. As a result of this restriction, if SECOND is the leading field, the interval literal cannot have any trailing field.

The valid range of values for the trailing field are as follows:

HOUR 

0 to 23 

MINUTE 

0 to 59 

SECOND 

0 to 59.999999999 

Examples of the various forms of INTERVAL DAY TO SECOND literals follow, including some abbreviated versions:

INTERVAL '4 5:12:10.222' DAY(3) TO SECOND(3) 

indicates 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second. 

INTERVAL '4 5:12' DAY TO MINUTE 

indicates 4 days, 5 hours and 12 minutes. 

INTERVAL '400 5' DAY(3) TO HOUR 

indicates 400 days 5 hours. 

INTERVAL '400' DAY(3)  

indicates 400 days. 

INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) 

indicates 11 hours, 12 minutes, and 10.2222222 seconds. 

INTERVAL '11:20' HOUR TO MINUTE 

indicates 11 hours and 20 minutes. 

INTERVAL '10' HOUR 

indicates 10 hours. 

INTERVAL '10:22' MINUTE TO SECOND 

indicates 10 minutes 22 seconds. 

INTERVAL '10' MINUTE 

indicates 10 minutes. 

INTERVAL '4' DAY 

indicates 4 days. 

INTERVAL '25' HOUR  

indicates 25 hours. 

INTERVAL '40' MINUTE 

indicates 40 minutes. 

INTERVAL '120' HOUR(3) 

indicates 120 hours 

INTERVAL '30.12345' SECOND(2,4)  

indicates 30.1235 seconds. The fractional second '12345' is rounded to '1235' because the precision is 4. 

You can add or subtract one DAY TO SECOND interval literal from another DAY TO SECOND literal. For example.

INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10' DAY

Format Models

A format model is a character literal that describes the format of DATE or NUMBER data stored in a character string. When you convert a character string into a date or number, a format model tells Oracle how to interpret the string. In SQL statements, you can use a format model as an argument of the TO_CHAR and TO_DATE functions:

For example, the date format model for the string '17:45:29' is 'HH24:MI:SS'. The date format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. The number format model for the string '$2,304.25' is '$9,999.99'. For lists of date and number format model elements, see Table 2-7, "Number Format Elements" and Table 2-9, "Datetime Format Elements".

The values of some formats are determined by the value of initialization parameters. For such formats, you can specify the characters returned by these format elements implicitly using the initialization parameter NLS_TERRITORY. You can change the default date format for your session with the ALTER SESSION statement.

See Also:

 

Format of Return Values: Examples

You can use a format model to specify the format for Oracle to use to return values from the database to you.

The following statement selects the commission values of the employees in Department 30 and uses the TO_CHAR function to convert these commissions into character values with the format specified by the number format model '$9,990.99':

SELECT ename employee, TO_CHAR(comm, '$9,990.99') commission
   FROM emp
   WHERE deptno = 30;
 
EMPLOYEE   COMMISSION
---------- ----------
ALLEN         $300.00
WARD          $500.00
MARTIN      $1,400.00
BLAKE
TURNER          $0.00
JAMES

Because of this format model, Oracle returns commissions with leading dollar signs, commas every three digits, and two decimal places. Note that TO_CHAR returns null for all employees with null in the comm column.

The following statement selects the date on which each employee from Department 20 was hired and uses the TO_CHAR function to convert these dates to character strings with the format specified by the date format model 'fmMonth DD, YYYY':

 SELECT ename, TO_CHAR(Hiredate,'fmMonth DD, YYYY') hiredate
    FROM emp
    WHERE deptno = 20;
 
ENAME      HIREDATE
---------- ------------------
SMITH      December 17, 1980
JONES      April 2, 1981
SCOTT      April 19, 1987
ADAMS      May 23, 1987
FORD       December 3, 1981
LEWIS      October 23, 1997

With this format model, Oracle returns the hire dates (as specified by "fm") without blank padding, two digits for the day, and the century included in the year.

See Also: "Format Model Modifiers" for a description of the fm format element 

Supplying the Correct Format Model: Examples

When you insert or update a column value, the datatype of the value that you specify must correspond to the column's datatype. You can use format models to specify the format of a value that you are converting from one datatype to another datatype required for a column.

For example, a value that you insert into a DATE column must be a value of the DATE datatype or a character string in the default date format (Oracle implicitly converts character strings in the default date format to the DATE datatype). If the value is in another format, you must use the TO_DATE function to convert the value to the DATE datatype. You must also use a format model to specify the format of the character string.

The following statement updates BAKER's hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the character string '1998 05 20' to a DATE value:

UPDATE emp 
  SET hiredate = TO_DATE('1998 05 20','YYYY MM DD') 
  WHERE ename = 'BLAKE'; 

This remainder of this section describes how to use:

Number Format Models

You can use number format models:

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. If a positive value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~). This event typically occurs when you are using TO_CHAR with a restrictive number format string, causing a rounding operation.

Number Format Elements

A number format model is composed of one or more number format elements. Table 2-7 lists the elements of a number format model. Examples are shown in Table 2-8.

Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

Table 2-7 Number Format Elements
Element  Example  Description 

, (comma) 

9,999 

Returns a comma in the specified position. You can specify multiple commas in a number format model.

Restrictions:

  • A comma element cannot begin a number format model.

  • A comma cannot appear to the right of a decimal character or period in a number format model.

 

. (period) 

99.99 

Returns a decimal point, which is a period (.) in the specified position.

Restriction: You can specify only one period in a number format model. 

$9999 

Returns value with a leading dollar sign. 

0999

9990 

Returns leading zeros.

Returns trailing zeros. 

9999 

Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative.

Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. 

B9999 

Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of "0"s in the format model). 

C999 

Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter). 

99D99 

Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).

Restriction: You can specify only one decimal character in a number format model. 

EEEE 

9.9EEEE 

Returns a value using in scientific notation. 

FM 

FM90.9 

Returns a value with no leading or trailing blanks. 

9G999 

Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.

Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model. 

L999 

Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter). 

MI 

9999MI 

Returns negative value with a trailing minus sign (-).

Returns positive value with a trailing blank.

Restriction: The MI format element can appear only in the last position of a number format model.  

PR 

9999PR 

Returns negative value in <angle brackets>.

Returns positive value with a leading and trailing blank.

Restriction: The PR format element can appear only in the last position of a number format model.  

RN

rn 

RN

rn 

Returns a value as Roman numerals in uppercase.

Returns a value as Roman numerals in lowercase.

Value can be an integer between 1 and 3999. 

S9999

9999S 

Returns negative value with a leading minus sign (-).

Returns positive value with a leading plus sign (+).

Returns negative value with a trailing minus sign (-).

Returns positive value with a trailing plus sign (+).

Restriction: The S format element can appear only in the first or last position of a number format model. 

TM 

TM 

"Text minimum". Returns (in decimal output) the smallest number of characters possible. This element is case-insensitive.

The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If output exceeds 64 characters, Oracle automatically returns the number in scientific notation.

Restrictions:

  • You cannot precede this element with any other element.

  • You can follow this element only with 9 or E (only one) or e (only one).

 

U9999 

Returns in the specified position the "Euro" (or other) dual currency symbol (the current value of the NLS_DUAL_CURRENCY parameter). 

999V99 

Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the "V". 

XXXX

xxxx 

Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, Oracle rounds it to an integer.

Restrictions:

  • This element accepts only positive values or 0. Negative values return an error.

  • You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, the return always has 1 leading blank.

 

Table 2-8 shows the results of the following query for different values of number and 'fmt':

SELECT TO_CHAR(number, 'fmt')
   FROM DUAL;
Table 2-8 Results of Example Number Conversions  
number  'fmt'  Result 

-1234567890 

9999999999S 

'1234567890-' 

0 

99.99 

' .00' 

+0.1 

99.99 

' .10' 

-0.2 

99.99 

' -.20' 

0 

90.99 

' 0.00' 

+0.1 

90.99 

' 0.10' 

-0.2 

90.99 

' -0.20' 

0 

9999 

' 0' 

1 

9999 

' 1' 

0 

B9999 

' ' 

1 

B9999 

' 1' 

0 

B90.99 

' ' 

+123.456 

999.999 

' 123.456' 

-123.456 

999.999 

'-123.456' 

+123.456 

FM999.009 

'123.456' 

+123.456 

9.9EEEE 

' 1.2E+02' 

+1E+123 

9.9EEEE 

' 1.0E+123' 

+123.456 

FM9.9EEEE 

'1.2E+02' 

+123.45 

FM999.009 

'123.45' 

+123.0 

FM999.009 

'123.00' 

+123.45 

L999.99 

' $123.45' 

+123.45 

FML999.99 

'$123.45' 

+1234567890 

9999999999S 

'1234567890+' 

Date Format Models

You can use date format models:

The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY. For information on these parameters, see Oracle8i Reference.

You can change the default date format for your session with the ALTER SESSION statement.

See Also: ALTER SESSION 

The total length of a date format model cannot exceed 22 characters.

Date Format Elements

A date format model is composed of one or more date format elements as listed in Table 2-9.

Capitalization of Date Format Elements

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

Punctuation and Character Literals in Date Format Models

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

These characters appear in the return value in the same location as they appear in the format model.

Table 2-9 Datetime Format Elements

Element 

Specify in TO_DATE? 

Meaning 

-
/
,
.
;
:
"text"
 

Yes 

Punctuation and quoted text is reproduced in the result. 

AD or 
A.D.
 

Yes 

AD indicator with or without periods.

Note: The indicator with periods is supported only if the NLS_LANGUAGE parameter is set to AMERICAN

AM
A.M.
 

Yes 

Meridian indicator with or without periods.

Note: The indicator with periods is supported only if the NLS_LANGUAGE parameter is set to AMERICAN

BC
B.C.
 

Yes 

BC indicator with or without periods.

Note: The indicator with periods is supported only if the NLS_LANGUAGE parameter is set to AMERICAN

CC
SCC
 

No 

The first two digits of the century of a four-digit year, for example, '19' from '1900' and '20' from '2001'. "S" prefixes BC dates with "-".  

D
 

Yes 

Day of week (1-7). This element is used only to validate a date specified in the TO_DATE function. 

DAY
 

Yes 

Name of day, padded with blanks to length of 9 characters. This element is used only to validate a date specified in the TO_DATE function. 

DD
 

Yes 

Day of month (1-31). 

DDD
 

Yes 

Day of year (1-366). 

DY
 

Yes 

Abbreviated name of day. This element is used only to validate a date specified in the TO_DATE function. 

E
 

Yes 

Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). 

EE
 

Yes 

Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). 

HH
 

Yes 

Hour of day (1-12). 

HH12
 

Yes 

Hour of day (1-12). 

HH24
 

Yes 

Hour of day (0-23). 

IW
 

No 

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

IYY
IY
I
 

No 

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

IYYY
 

No 

4-digit year based on the ISO standard. 

J
 

Yes 

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

MI
 

Yes 

Minute (0-59). 

MM
 

Yes 

Two-digit numeric abbreviation of month (01-12; JAN = 01) 

MON
 

Yes 

Abbreviated name of month. 

MONTH
 

Yes 

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

PM
P.M.
 

No 

Meridian indicator with or without periods.

Note: The indicator with periods is supported only if the NLS_LANGUAGE parameter is set to AMERICAN

Q
 

No 

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

RM
 

Yes 

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

RR
 

Yes 

Given a year with 2 digits:

  • If the year is <50 and the last 2 digits of the current year are >=50, the first 2 digits of the returned year are 1 greater than the first two digits of the current year.

  • If the year is >=50 and the last 2 digits of the current year are <50, the first 2 digits of the returned year are the same as the first 2 digits of the current year.

 
RRRR
 

Yes 

Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, enter the 4-digit year. 

SS
 

Yes 

Second (0-59). 

SSSSS
 

Yes 

Seconds past midnight (0-86399). 

WW
 

No 

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. 

W
 

No 

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

Y,YYY
 

Yes 

Year with comma in this position. 

YEAR
SYEAR
 

No 

Year, spelled out. "S" prefixes BC dates with "-". 

YYYY
SYYYY
 

Yes 

4-digit year. "S" prefixes BC dates with "-". 

YYY
YY
Y
 

Yes 

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

Oracle returns an error if an alphanumeric character is found in the date string where punctuation character is found in the format string. For example:

TO_CHAR (TO_DATE('0297','MM/YY'), 'MM/YY')

returns an error.

Date Format Elements and National Language Support

The functionality of some date format elements depends on the country and language in which you are using Oracle. For example, these date format elements return spelled values:

The language in which these values are returned is specified either explicitly with the initialization parameter NLS_DATE_LANGUAGE or implicitly with the initialization parameter NLS_LANGUAGE. The values returned by the YEAR and SYEAR date format elements are always in English.

The date format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.

See Also: Oracle8i Reference and Oracle8i National Language Support Guide for information on national language support initialization parameters 

ISO Standard Date Format Elements

Oracle calculates the values returned by the date format elements IYYY, IYY, IY, I, and IW according to the ISO standard. For information on the differences between these values and those returned by the date format elements YYYY, YYY, YY, Y, and WW, see the discussion of national language support in Oracle8i National Language Support Guide.

The RR Date Format Element

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 21st century dates in the 20th century by specifying only the last two digits of the year. It will also allow you to store 20th century dates in the 21st century in the same way if necessary.

If you use the TO_DATE function with the YY date format element, the date value returned always has the same first 2 digits as the current year. 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. Table 2-10 summarizes the behavior of the RR date format element.

Table 2-10 The RR Date Element Format
 

If the specified two-digit year is 

 

0 - 49 

50 - 99 

If the last two digits of the current year are: 

0-49 

The return date has the same first 2 digits as the current date. 

The first 2 digits of the return date are 1 less than the first 2 digits of the current date. 

50-99 

The first 2 digits of the return date are 1 greater than the first 2 digits of the current date. 

The return date has the same first 2 digits as the current date. 

The following examples demonstrate the behavior of the RR date format element.

RR Date Format Examples

Assume these queries are issued between 1950 and 1999:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year"
     FROM DUAL;

Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"
     FROM DUAL; 

Year
----
2017

Now assume these queries are issued between 2000 and 2049:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year"
  FROM DUAL; 

Year
----
1998 

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"
     FROM DUAL; 

Year
----
2017

Note that the queries return the same values regardless of whether they are issued before or after the year 2000. The RR date format element allows you to write SQL statements that will return the same values from years whose first two digits are different.

Date Format Element Suffixes

Table 2-11 lists suffixes that can be added to date format elements:

Table 2-11 Date Format Element Suffixes
Suffix  Meaning  Example Element  Example Value 

TH 

Ordinal Number 

DDTH 

4TH 

SP 

Spelled Number 

DDSP 

FOUR 

SPTH or THSP 

Spelled, ordinal number 

DDSPTH 

FOURTH 

Restrictions:

  • When you add one of these suffixes to a date format element, the return value is always in English.

  • Date suffixes are valid only on output. You cannot use them to insert a date into the database.

 

Format Model Modifiers

The FM and FX modifiers, used in format models in the TO_CHAR function, control blank padding and exact format checking.

A modifier can appear in a format model more than once. In such a 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 reenabled 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:

FX

"Format exact". This modifier specifies exact matching for the character argument and date format model of a TO_DATE function:

If any portion of the character argument violates any of these conditions, Oracle returns an error message.

Format Modifier Examples

The following statement uses a date format model to return a character expression:

SELECT TO_CHAR(SYSDATE, 'fmDDTH')||' of '||TO_CHAR
   (SYSDATE, 'fmMonth')||', '||TO_CHAR(SYSDATE, 'YYYY') "Ides" 
    FROM DUAL; 

Ides 
------------------ 
3RD of April, 1998

Note that the statement above also uses the FM modifier. If FM is omitted, the month is blank-padded to nine characters:

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

Ides 
----------------------- 
03RD of April    , 1998 

The following statement places a single quotation mark in the return value by using a date format model that includes two consecutive single quotation marks:

SELECT TO_CHAR(SYSDATE, 'fmDay')||'''s Special' "Menu"
     FROM DUAL; 

Menu 
----------------- 
Tuesday's Special 

Two consecutive single quotation marks can be used for the same purpose within a character literal in a format model.

Table 2-12 shows whether the following statement meets the matching conditions for different values of char and 'fmt' using FX (the table named table has a column date_column of datatype DATE):

UPDATE table 
  SET date_column = TO_DATE(char, 'fmt');
Table 2-12 Matching Character Data and Format Models with the FX Format Model Modifier

char 

'fmt' 

Match or Error? 

'15/ JAN /1998' 

'DD-MON-YYYY' 

Match 

' 15! JAN % /1998' 

'DD-MON-YYYY' 

Error 

'15/JAN/1998' 

'FXDD-MON-YYYY' 

Error 

'15-JAN-1998' 

'FXDD-MON-YYYY' 

Match 

'1-JAN-1998' 

'FXDD-MON-YYYY' 

Error 

'01-JAN-1998' 

'FXDD-MON-YYYY' 

Match 

'1-JAN-1998' 

'FXFMDD-MON-YYYY' 

Match 

String-to-Date Conversion Rules

The following additional formatting rules apply when converting string values to date values (unless you have used the FX or FXFM modifiers in the format model to control exact format checking):

Nulls

If a column in a row has no value, then the 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 not known or when a value would not be meaningful.

Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.) Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.

Nulls in SQL Functions

All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. You can use the NVL function 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.

Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.

Nulls with Comparison Operators

To test for nulls, use only 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 or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE expression.

See Also: "DECODE Expressions" for syntax and additional information, see 

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.

Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Table 2-14 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

Table 2-14 Conditions Containing Nulls
If A is:  Condition  Evaluates to: 

10 

a IS NULL 

FALSE 

10 

a IS NOT NULL 

TRUE 

NULL 

a IS NULL 

TRUE 

NULL 

a IS NOT NULL 

FALSE 

10 

a = NULL 

UNKNOWN 

10 

a != NULL 

UNKNOWN 

NULL 

a = NULL 

UNKNOWN 

NULL 

a != NULL 

UNKNOWN 

NULL 

a = 10 

UNKNOWN 

NULL 

a != 10 

UNKNOWN 

For the truth tables showing the results of logical expressions containing nulls, see Table 3-6, as well as Table 3-7 and Table 3-8.

Pseudocolumns

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

CURRVAL and NEXTVAL

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:

CURRVAL 

The CURRVAL pseudocolumn returns the current value of a sequence. 

NEXTVAL 

The NEXTVAL pseudocolumn increments the sequence and returns the next value. 

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

sequence.CURRVAL
sequence.NEXTVAL

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

See Also:

"Referring to Objects in Remote Databases" for more information on referring to database links 

Where to Use Sequence Values

You can use CURRVAL and NEXTVAL in:

Restrictions: You cannot use CURRVAL and NEXTVAL:

Also, within a single SQL statement that uses CURVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

How to Use Sequence Values

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last reference to NEXTVAL. Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

Within a single SQL statement, Oracle will increment the sequence only once per row. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.

A sequence can be accessed by many users concurrently with no waiting or locking.

See Also: CREATE SEQUENCE for information on sequences 

Finding the current value of a sequence: Example

This example selects the current value of the employee sequence:

SELECT empseq.currval 
    FROM DUAL;

Inserting sequence values into a table: Example

This example increments the employee sequence and uses its value for a new employee inserted into the employee table:

INSERT INTO emp
    VALUES (empseq.nextval, 'LEWIS', 'CLERK',
             7902, SYSDATE, 1200, NULL, 20);

Reusing the current value of a sequence: Example

This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:

INSERT INTO master_order(orderno, customer, orderdate)
    VALUES (orderseq.nextval, 'Al''s Auto Shop', SYSDATE);

INSERT INTO detail_order (orderno, part, quantity)
    VALUES (orderseq.currval, 'SPARKPLUG', 4);

INSERT INTO detail_order (orderno, part, quantity)
    VALUES (orderseq.currval, 'FUEL PUMP', 1);

INSERT INTO detail_order (orderno, part, quantity)
    VALUES (orderseq.currval, 'TAILPIPE', 2);

LEVEL

Figure 2-2 Hierarchical Tree


To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

See also:

 

ROWID

For each row in the database, the ROWID pseudocolumn returns a row's address. Oracle8i rowid values contain information necessary to locate a row:

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Values of the ROWID pseudocolumn have the datatype ROWID or UROWID.

See Also:

"ROWID Datatype" and "UROWID Datatype" 

Rowid values have several important uses:

You should not use ROWID as a table's primary key. If you delete and reinsert a row with the Import and Export utilities, for example, its rowid may change. If you delete a row, Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

Example

This statement selects the address of all rows that contain data for employees in department 20:

SELECT ROWID, ename  
   FROM emp
   WHERE deptno = 20;
 
ROWID              ENAME
------------------ ----------
AAAAqYAABAAAEPvAAA SMITH
AAAAqYAABAAAEPvAAD JONES
AAAAqYAABAAAEPvAAH SCOTT
AAAAqYAABAAAEPvAAK ADAMS
AAAAqYAABAAAEPvAAM FORD

ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM emp WHERE ROWNUM < 10;

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM emp WHERE ROWNUM < 11 ORDER BY empno;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":

SELECT * FROM
   (SELECT empno FROM emp ORDER BY empno)
   WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by empno in the subquery.

See Also:

Oracle8i Application Developer's Guide - Fundamentals for more information about top-N queries 

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM emp
    WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx
    SET col1 = ROWNUM;


Note: Using ROWNUM in a query can affect view optimization. For more information, see Oracle8i Concepts


Comments

You can associate comments with SQL statements and schema objects.

Comments Within SQL Statements

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:

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.


Note: You cannot use these styles of comments between SQL statements in a SQL script. Use the SQL*Plus REMARK command for this purpose. For information on these statements, see SQL*Plus User's Guide and Reference


Example

These statements contain many comments:

SELECT ename, sal + NVL(comm, 0), job, loc
/* Select all employees whose compensation is
greater than that of Jones.*/
  FROM emp, dept
       /*The DEPT table is used to get the department name.*/
  WHERE emp.deptno = dept.deptno
    AND sal + NVL(comm,0) >   /* Subquery:       */
   (SELECT sal + NLV(comm,0)
                              /* total compensation is sal + comm */
      FROM emp 
      WHERE ename = 'JONES');

SELECT ename,                    -- select the name
    sal + NVL(comm, 0),          -- total compensation
    job,                         -- job
    loc                          -- and city containing the office
  FROM emp,                      -- of all employees
       dept
  WHERE emp.deptno = dept.deptno
    AND sal + NVL(comm, 0) >     -- whose compensation 
                                 -- is greater than
      (SELECT sal + NVL(comm,0)  -- the compensation
    FROM emp 
    WHERE ename = 'JONES');       -- of Jones.

Comments on Schema Objects

You can associate a comment with a table, view, materialized view, or column using the COMMENT command. Comments associated with schema objects are stored in the data dictionary.

See Also: COMMENT for a description of comments 

Hints

You can use comments in a SQL statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses these hints as suggestions for choosing an execution plan for the statement.

A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The syntax below shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:

The syntax and a brief description of each hint appear below. Hints are divided into functional categories.

See Also:

Oracle8i Performance Guide and Reference and Oracle8i Concepts for more information on hints 

Optimization Approaches and Goals Hints

The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).


The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).


The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

This hint causes the optimizer to make the following choices:

The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore other hints specified for the statement block.

Access Method Hints

The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.


The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.


The FULL hint explicitly chooses a full table scan for the specified table.


The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.


The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B*-tree, and bitmap indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.


The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.


The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.


The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.


The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.


The NO_INDEX hint explicitly disallows a set of indexes for the specified table.


The ROWID hint explicitly chooses a table scan by rowid for the specified table.

Join Order Hints

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.


The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.

Join Operation Hints

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.


For a specific query, place the MERGE_AJ or HASH_AJ hints into the NOT IN subquery. MERGE_AJ uses a sort-merge anti-join and HASH_AJ uses a hash anti-join.


For a specific query, place the HASH_SJ or MERGE_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join and MERGE_SJ uses a sort merge semi-join.


The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.


The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join.


The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join.


The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table.

Parallel Execution Hints


Note: Oracle ignores parallel hints on a temporary table. 


See Also: CREATE TABLE and Oracle8i Concepts 


When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the blocks currently allocated to the table is not used.

If INSERT is parallelized using the PARALLEL hint or clause, then append mode is used by default. You can use NOAPPEND to override append mode. The APPEND hint applies to both serial and parallel insert.

The append operation is performed in LOGGING or NOLOGGING mode, depending on whether the [NO] option is set for the table in question. Use the ALTER TABLE... [NO]LOGGING statement to set the appropriate value.

The NOAPPEND hint overrides append mode.


The NOPARALLEL hint overrides a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.

Restriction: You cannot parallelize a query involving a nested table.


The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion.


Note:

The number of servers that can be used is twice the value in the PARALLEL hint if sorting or grouping operations also take place. 


If any parallel restrictions are violated, then the hint is ignored.


The PARALLEL_INDEX hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.


The PQ_DISTRIBUTE hint improves parallel join operation performance. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.

Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint if both tables are serial.

See Also: Oracle8i Performance Guide and Reference for the permitted combinations of distributions for the outer and inner join tables 


The NOPARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.

Query Transformation Hints

The MERGE hint lets you merge a view on a per-query basis.

If a view's query contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view's query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.

Complex merging is not cost-based--that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.


The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.


The NO_MERGE hint causes Oracle not to merge mergeable views.


The NOREWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. Use the NOREWRITE hint on any query block of a request.


The REWRITE hint forces the cost-based optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.

Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of its cost.


The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.

Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.


The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

The USE_CONCAT hint turns off IN-list processing and OR-expands all disjunctions, including IN-lists.

Other Hints

The CACHE hint specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.


The NOCACHE hint specifies that the blocks retrieved for the table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.


If you enabled subquery unnesting with the UNNEST_SUBQUERY parameter, then the NO_UNNEST hint turns it off for specific subquery blocks.


The ORDERED_PREDICATES hint forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint in the WHERE clause of SELECT statements.

If you do not use the ORDERED_PREDICATES hint, then Oracle evaluates all predicates in the order specified by the following rules. Predicates:

The PUSH_PRED hint forces pushing of a join predicate into the view.


The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.


The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible place in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.


Setting the UNNEST_SUBQUERY session parameter to TRUE enables subquery unnesting. Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.

UNNEST_SUBQUERY first verifies if the statement is valid. If the statement is not valid, then subquery unnesting cannot proceed. The statement must then must pass a heuristic test.

The UNNEST hint checks the subquery block for validity only. If it is valid, then subquery unnesting is enabled without Oracle checking the heuristics.

Database Objects

Oracle recognizes objects that are associated with a particular schema and objects that are not associated with a particular schema, as described in the sections that follow.

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:

Nonschema Objects

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:

In this reference, each type of object is briefly defined in Chapter 7 through Chapter 11, in the section describing the statement that creates the database object. These statements begin with the keyword CREATE. For example, for the definition of a cluster, see CREATE CLUSTER.

See Also: Oracle8i Concepts for an overview of database objects 

You must provide names for most types of schema objects when you create them. These names must follow the rules listed in the following sections.

Parts of Schema Objects

Some schema objects are made up of parts that you can or must name, such as:

Partitioned Tables and Indexes

Tables and indexes can be partitioned. When partitioned, these schema objects consist of a number of parts called partitions, all of which have the same logical attributes. For example, all partitions in a table share the same column and constraint definitions, and all partitions in an index share the same index columns.

When you partition a table or index using the range method, you specify a maximum value for the partitioning key column(s) for each partition. When you partition a table or index using the hash method, you instruct Oracle to distribute the rows of the table into partitions based on a system-defined hash function on the partitioning key column(s). When you partition a table or index using the composite-partitioning method, you specify ranges for the partitions, and Oracle distributes the rows in each partition into one or more hash subpartitions based on a hash function. Each subpartition of a table or index partitioned using the composite method has the same logical attributes.

Partition-Extended and Subpartition-Extended Table Names

Partition-extended and subpartition-extended table names let you perform some partition-level and subpartition-level operations, such as deleting all rows from a partition or subpartition, on only one partition or subpartition. Without extended table names, such operations would require that you specify a predicate (WHERE clause). For range-partitioned tables, trying to phrase a partition-level operation with a predicate can be cumbersome, especially when the range partitioning key uses more than one column. For hash partitions and subpartitions, using a predicate is more difficult still, because these partitions and subpartitions are based on a system-defined hash function.

Partition-extended table names let you use partitions as if they were tables. An advantage of this method, which is most useful for range-partitioned tables, is that you can build partition-level access control mechanisms by granting (or revoking) privileges on these views to (or from) other users or roles.To use a partition as a table, create a view by selecting data from a single partition, and then use the view as a table.

You can specify partition-extended or subpartition-extended table names for the following DML statements:

Syntax

The basic syntax for using partition-extended and subpartition-extended table names is:


Restrictions

Currently, the use of partition-extended and subpartition-extended table names has the following restrictions:

Example

In the following statement, sales is a partitioned table with partition jan97. You can create a view of the single partition jan97, and then use it as if it were a table. This example deletes rows from the partition.

CREATE VIEW sales_jan97 AS
     SELECT * FROM sales PARTITION (jan97); 
DELETE FROM sales_jan97 WHERE amount < 0; 

Schema Object Names and Qualifiers

This section provides:

Schema Object Naming Rules

The following rules apply when naming schema objects:

  1. Names must be from 1 to 30 bytes long with these exceptions:

    • Names of databases are limited to 8 bytes.

    • Names of database links can be as long as 128 bytes.

  2. Names cannot contain quotation marks.

  3. Names are not case sensitive.

  4. A name must begin with an alphabetic character from your database character set unless surrounded by double quotation marks.

  5. Names can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Oracle strongly discourages you from using $ and #. Names of database links can also contain periods (.) and "at" signs (@).

    If your database character set contains multibyte characters, Oracle recommends that each name for a user or a role contain at least one single-byte character.


    Note: You cannot use special characters from European or Asian character sets in a database name, global database name, or database link names. For example, characters with an umlaut are not allowed. 


  6. A name cannot be an Oracle reserved word. , lists all Oracle reserved words.

    Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words.

    See Also:

     

  7. Do not use the word DUAL as a name for an object or part. DUAL is the name of a dummy table.

  8. The Oracle SQL language contains other words that have special meanings. These words include datatypes, function names, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally. Therefore, if you use these words as names for objects and object parts, your SQL statements may be more difficult to read and may lead to unpredictable results.

    In particular, do not use words beginning with "SYS_" as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions.

    See Also:

    "Datatypes" and "SQL Functions" 

  9. Within a namespace, no two objects can have the same name.

    The following figure shows the namespaces for schema objects. Each box is a namespace. Tables and views are in the same namespace. Therefore, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.


    Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

    The following figure shows the namespaces for nonschema objects. Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.


  10. Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.

  11. Procedures or functions contained in the same package can have the same name, provided that their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.

  12. A name can be enclosed in double quotation marks. Such names can contain any combination of characters, including spaces, ignoring rules 3 through 7 in this list. This exception is allowed for portability, but Oracle recommends that you do not break rules 3 through 7.

    If you give a schema object a name enclosed in double quotation marks, you must use double quotation marks whenever you refer to the object.

    Enclosing a name in double quotes allows it to:

    • Contain spaces

    • Be case sensitive

    • Begin with a character other than an alphabetic character, such as a numeric character

    • Contain characters other than alphanumeric characters and _, $, and #

    • Be a reserved word

    By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:

    emp
    "emp"
    "Emp"
    "EMP "
    
    

    Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:

    emp
    EMP
    "EMP"
    
    

    If you give a user or password a quoted name, the name cannot contain lowercase letters.

    Database link names cannot be quoted.

Schema Object Naming Examples

The following examples are valid schema object names:

ename
horse
scott.hiredate
"EVEN THIS & THAT!"
a_very_long_and_valid_name

Although column aliases, table aliases, usernames, and passwords are not objects or parts of objects, they must also follow these naming rules with these exceptions:

Schema Object Naming Guidelines

Here are several helpful guidelines for naming objects and their parts:

When naming objects, balance the objective of keeping names short and easy to use with the objective of making names as descriptive as possible. When in doubt, choose the more descriptive name, because the objects in the database may be used by many people over a period of time. Your counterpart ten years from now may have difficulty understanding a database with a name like pmdd instead of payment_due_date.

Using consistent naming rules helps users understand the part that each table plays in your application. One such rule might be to begin the names of all tables belonging to the FINANCE application with fin_.

Use the same names to describe the same things across tables. For example, the department number columns of the sample employees and departments tables are both named deptno.

Syntax for Schema Objects and Parts in SQL Statements

This section tells you how to refer to schema objects and their parts in the context of a SQL statement. This section shows you:

The following diagram shows the general syntax for referring to an object or a part:


where:

You can include spaces around the periods separating the components of the reference to the object, but it is conventional to omit them.

How Oracle Resolves Schema Object References

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. After locating the object, Oracle performs the statement's operation on the object. If the named object cannot be found in the appropriate namespace, Oracle returns an error.

The following example illustrates how Oracle resolves references to objects within SQL statements. Consider this statement that adds a row of data to a table identified by the name dept:

INSERT INTO dept 
   VALUES (50, 'SUPPORT', 'PARIS');

Based on the context of the statement, Oracle determines that dept can be:

Oracle always attempts to resolve an object reference within the namespaces in your own schema before considering namespaces outside your schema. In this example, Oracle attempts to resolve the name dept as follows:

  1. First, Oracle attempts to locate the object in the namespace in your own schema containing tables, views, and private synonyms. If the object is a private synonym, Oracle locates the object for which the synonym stands. This object could be in your own schema, another schema, or on another database. The object could also be another synonym, in which case Oracle locates the object for which this synonym stands.

  2. If the object is in the namespace, Oracle attempts to perform the statement on the object. In this example, Oracle attempts to add the row of data to dept. If the object is not of the correct type for the statement, Oracle returns an error. In this example, dept must be a table, view, or a private synonym resolving to a table or view. If dept is a sequence, Oracle returns an error.

  3. If the object is not in any namespace searched in thus far, Oracle searches the namespace containing public synonyms. If the object is in that namespace, Oracle attempts to perform the statement on it. If the object is not of the correct type for the statement, Oracle returns an error. In this example, if dept is a public synonym for a sequence, Oracle returns an error.

Referring to Objects in Other Schemas

To refer to objects in schemas other than your own, prefix the object name with the schema name:

schema.object

For example, this statement drops the emp table in the schema scott:

DROP TABLE scott.emp

Referring to Objects in Remote Databases

To refer to objects in databases other than your local database, follow the object name with the name of the database link to that database. A database link is a schema object that causes Oracle to connect to a remote database to access an object there. This section tells you:

Creating Database Links

You create a database link with the statement CREATE DATABASE LINK. The statement allows you to specify this information about the database link:

Oracle stores this information in the data dictionary.

Database Link Names

When you create a database link, you must specify its name. Database link names are different from names of other types of objects. They can be as long as 128 bytes and can contain periods (.) and the "at" sign (@).

The name that you give to a database link must correspond to the name of the database to which the database link refers and the location of that database in the hierarchy of database names. The following syntax diagram shows the form of the name of a database link:

dblink::=


where:

The combination database.domain is sometimes called the "service name".

See Also:

Net8 Administrator's Guide 

Username and Password

Oracle uses the username and password to connect to the remote database. The username and password for a database link are optional.

Database Connect String

The database connect string is the specification used by Net8 to access the remote database. For information on writing database connect strings, see the Net8 documentation for your specific network protocol. The database string for a database link is optional.

Referring to Database Links

Database links are available only if you are using Oracle's distributed functionality. When you issue a SQL statement that contains a database link, you can specify the database link name in one of these forms:

Oracle performs these tasks before connecting to the remote database:

  1. If the database link name specified in the statement is partial, Oracle expands the name to contain the domain of the local database as found in the global database name stored in the data dictionary. (You can see the current global database name in the GLOBAL_NAME data dictionary view.)

  2. Oracle first searches for a private database link in your own schema with the same name as the database link in the statement. Then, if necessary, it searches for a public database link with the same name.

    • Oracle always determines the username and password from the first matching database link (either private or public). If the first matching database link has an associated username and password, Oracle uses it. If it does not have an associated username and password, Oracle uses your current username and password.

    • If the first matching database link has an associated database string, Oracle uses it. If not, Oracle searches for the next matching (public) database link. If no matching database link is found, or if no matching link has an associated database string, Oracle returns an error.

  3. Oracle uses the database string to access the remote database. After accessing the remote database, if the value of the GLOBAL_NAMES parameter is true, Oracle verifies that the database.domain portion of the database link name matches the complete global name of the remote database. If this condition is true, Oracle proceeds with the connection, using the username and password chosen in Step 2. If not, Oracle returns an error.

  4. If the connection using the database string, username, and password is successful, Oracle attempts to access the specified object on the remote database using the rules for resolving object references and referring to objects in other schemas discussed earlier in this section.

You can disable the requirement that the database.domain portion of the database link name must match the complete global name of the remote database by setting to false the initialization parameter GLOBAL_NAMES or the GLOBAL_NAMES parameter of the ALTER SYSTEM or ALTER SESSION statement.

See Also:

Oracle8i Distributed Database Systems for more information on remote name resolution 

Referencing Object Type Attributes and Methods

To reference object type attributes or methods in a SQL statement, you must fully qualify the reference with a table alias. Consider the following example:

CREATE TYPE person AS OBJECT 
   (ssno VARCHAR(20), 
    name VARCHAR (10));

CREATE TABLE emptab (pinfo person);

In a SQL statement, reference to the ssno attribute must be fully qualified using a table alias, as illustrated below:

SELECT e.pinfo.ssno FROM emptab e;

UPDATE emptab e SET e.pinfo.ssno = '510129980' 
   WHERE e.pinfo.name = 'Mike';

To reference an object type's member method that does not accept arguments, you must provide "empty" parentheses. For example, assume that age is a method in the person type that does not take arguments. In order to call this method in a SQL statement, you must provide empty parentheses as shows in this example:

SELECT e.pinfo.age() FROM emptab e 
   WHERE e.pinfo.name = 'Mike';

See Also:

Oracle8i Concepts for more information on user-defined datatypes 


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index