Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

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, then 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 that can be used as datatypes. The syntax of Oracle datatypes appears in the diagrams that follow. The text of this section is divided into the following sections:

datatypes::=

Text description of sql_elements24.gif follows
Text description of datatypes


Oracle_built_in_datatypes::=

Text description of sql_elements20.gif follows
Text description of Oracle_built_in_datatypes


character_datatypes::=

Text description of sql_elements26.gif follows
Text description of character_datatypes


number_datatypes::=

Text description of sql_elements21.gif follows
Text description of number_datatypes


long_and_raw_datatypes::=

Text description of sql_elements22.gif follows
Text description of long_and_raw_datatypes


datetime_datatypes::=

Text description of sql_elements23.gif follows
Text description of datetime_datatypes


large_object_datatypes::=

Text description of sql_elements25.gif follows
Text description of large_object_datatypes


rowid_datatypes::=

Text description of sql_elements27.gif follows
Text description of rowid_datatypes


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

ANSI_supported_datatypes::=

Text description of sql_elements30.gif follows
Text description of ANSI_supported_datatypes


Oracle_supplied_types::=

Text description of sql_elements11.gif follows
Text description of Oracle_supplied_types


any_types::=

Text description of sql_elements13.gif follows
Text description of any_types


XML_types::=

Text description of sql_elements14.gif follows
Text description of XML_types


spatial_type::=

Text description of sql_elements19.gif follows
Text description of spatial_type


media_types::=

Text description of sql_elements58.gif follows
Text description of media_types


Oracle Built-in Datatypes

Table 2-1 summarizes Oracle built-in datatypes.

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

1

VARCHAR2(size) [BYTE | CHAR]

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

BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.

1

NVARCHAR2(size)

Variable-length character string having maximum length size characters. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.

2

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.

8

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.

180

TIMESTAMP (fractional_seconds_precision)

Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6.

181

TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE

All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.

231

TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE

All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:

  • Data is normalized to the database time zone when it is stored in the database.
  • When the data is retrieved, users see the data in the session time zone.

182

INTERVAL YEAR (year_precision) TO MONTH

Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2.

183

INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)

Stores a period of time in days, hours, minutes, and seconds, where

  • day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
  • fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.

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

Base 64 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)]

Base 64 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)[BYTE | CHAR]

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

BYTE and CHAR have the same semantics as for VARCHAR2.

96

NCHAR(size)

Fixed-length character data of length size characters. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.

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 Unicode 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. Oracle subsequently ensures that all values stored in that column have the length specified by size. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, then Oracle returns an error.

The default length for a CHAR column is 1 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10) column, but the string is blank-padded to 10 bytes before it is stored.

When you create a table with a CHAR column, by default you supply the column length in bytes. The BYTE qualifier is the same as the default. If you use the CHAR qualifier, for example CHAR(10 CHAR), then you supply the column length in characters. A character is technically a codepoint of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte 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 Oracle9i Database Globalization Support Guide for more information on character set support.


See Also:

"Datatype Comparison Rules" for information on comparison semantics

NCHAR Datatype

Beginning with Oracle9i, the NCHAR datatype is redefined to be a Unicode-only datatype. When you create a table with an NCHAR column, you define the column length in characters. You define the national character set when you create your database.

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. The maximum column size allowed is 2000 bytes.

If you insert a value that is shorter than the column length, then 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 translated_description from product_descriptions
   WHERE translated_name = N'LCD Monitor 11/PM';
See Also:

Oracle9i Database Globalization Support Guide for information on Unicode datatype support

NVARCHAR2 Datatype

Beginning with Oracle9i, the NVARCHAR2 datatype is redefined to be a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters 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. The maximum column size allowed is 4000 bytes.

See Also:

Oracle9i Database Globalization Support Guide for information on Unicode datatype support

VARCHAR2 Datatype

The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters 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, then 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 string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a codepoint of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded 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 Oracle9i Database Globalization Support Guide for more information on character set support.


See Also:

"Datatype Comparison Rules" for information on comparison semantics

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, then Oracle returns an error.

Specify a fixed-point number using the following form:

NUMBER(p,s)

where:

Specify an integer using the following form:

NUMBER(p)

This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

Specify a floating-point number using the following form:

NUMBER 

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

See Also:

"Floating-Point Numbers"

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, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it.

Table 2-2 show how Oracle stores data using different precisions and scales.

Table 2-2  Storage of Scale and Precision
Actual Data Specified As Stored As

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, then 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, then Oracle returns an error message. If the value exceeds the scale, then 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. Table 2-3 show the effects of a scale greater than precision:

Table 2-3  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 lets you 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. An exponent may optionally be used following the number to increase the range (for example, 1.777 e-20). 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 range of values discussed in "NUMBER Datatype". The format is defined in "Number Literals". 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 as soon as possible. Creation of new LONG columns is scheduled for desupport.

LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG functionality has been static for several releases. See the modify_col_properties clause of ALTER TABLE and TO_LOB for more information on converting LONG columns to LOB.


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

Datetime and Interval Datatypes

The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. Values of datetime datatypes are sometimes called "datetimes". The interval datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Values of interval datatypes are sometimes called intervals.

Both datetimes and intervals are made up of fields. The values of these fields determine the value of the datatype. Table 2-4 lists the datetime fields and their possible values for datetimes and intervals.

Table 2-4  Datetime Fields and Values
Datetime Field Valid Values for Datetime Valid Values for INTERVAL

YEAR

-4712 to 9999 (excluding year 0)

Any positive or negative integer

MONTH

01 to 12

0 to 11

DAY

01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the current NLS calendar parameter)

Any positive or negative integer

HOUR

00 to 23

0 to 23

MINUTE

00 to 59

0 to 59

SECOND

00 to 59.9(n), where "9(n)" is the precision of time fractional seconds The "9(n)" portion is not applicable for DATE.

0 to 59.9(n), where "9(n)" is the precision of interval fractional seconds

TIMEZONE_HOUR

(See Note that follows.)

-12 to 14 (This range accommodates daylight savings time changes.) Not applicable for DATE.

Not applicable

TIMEZONE_MINUTE

(See Note that follows.)

00 to 59. Not applicable for DATE.

Not applicable

Note: TIMEZONE_HOUR and TIMEZONE_MINUTE are specified together and interpreted as an entity in the format +|- hh:mm, with values ranging from -12:59 to +14:00.

TIMEZONE_REGION

Query the TZNAME column of the V$TIMEZONE_NAMES data dictionary view. Not applicable for DATE.

Not applicable

TIMEZONE_ABBR

Query the TZABBREV column of the V$TIMEZONE_NAMES data dictionary view. Not applicable for DATE.

Not applicable


Note:

To avoid unexpected results in your DML operations on datetime data, you can verify the database and session time zones by querying the built-in SQL functions DBTIMEZONE and SESSIONTIMEZONE. If the time zones have not been set manually, Oracle uses the operating system time zone by default. If the operating system time zone is not a valid Oracle time zone, Oracle uses UTC as the default value.


DATE Datatype

The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.

You can specify a date value as a literal, or you can convert a character or numeric value to a date value with the TO_DATE function. To specify a date as a literal, you must use the Gregorian calendar. You can specify an ANSI date literal, as shown in this example:

DATE '1998-12-25'

The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD'). Alternatively you can specify an Oracle date literal, as in the following example:

TO_DATE('98-DEC-25:17:30','YY-MON-DD:HH24:MI')

The default date format for an Oracle date literal is specified by the initialization parameter NLS_DATE_FORMAT. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation.

Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions.

If you specify a date value without a time component, then the default time is 12:00:00 am (midnight). If you specify a date value without a date, then the default date is the first day of the current month.

Oracle DATE columns always contain both the date and time fields. If your queries use a date format without a time portion, then you must ensure that the time fields in the DATE column are set to zero (that is, midnight). Otherwise, Oracle may not return the query results you expect. Here are some examples that assume a table my_table with a number column row_num and a DATE column datecol:

INSERT INTO my_table VALUES (1, SYSDATE);
INSERT INTO my_table VALUES (2, TRUNC(SYSDATE));

SELECT * FROM my_table;

   ROW_NUM DATECOL
---------- ---------
         1 04-OCT-00
         2 04-OCT-00

SELECT * FROM my_table
   WHERE datecol = TO_DATE('04-OCT-00','DD-MON-YY');

   ROW_NUM DATECOL
---------- ---------
         2 04-OCT-00

If you know that the time fields of your DATE column are set to zero, then you can query your DATE column as shown in the immediately preceding example, or by using the DATE literal:

SELECT * FROM my_table WHERE datecol = DATE '2000-10-04';

However, if the DATE column contains nonzero time fields, then you must filter out the time fields in the query to get the correct result. For example:

SELECT * FROM my_table WHERE TRUNC(datecol) = DATE '2000-10-04';

Oracle applies the TRUNC function to each row in the query, so performance is better if you ensure the zero value of the time fields in your data. To ensure that the time fields are set to zero, use one of the following methods during inserts and updates:

The date function SYSDATE returns the current system date and time. The function CURRENT_DATE returns the current session date. For information on SYSDATE, the TO_* datetime functions, and the default date format, see Chapter 6, "Functions".

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 sample table employees 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:
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

TIMESTAMP Datatype

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values. Specify the TIMESTAMP datatype as follows:

TIMESTAMP [(fractional_seconds_precision)] 

where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value can be a number in the range 0 to 9. The default is 6. When you specify TIMESTAMP as a literal, the fractional_seconds_precision value can be any number of digits up to 9, as follows:

TIMESTAMP'1997-01-31 09:26:50.124'
See Also:

TO_TIMESTAMP for information on converting character data to TIMESTAMP data

TIMESTAMP WITH TIME ZONE Datatype

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone displacement in its value. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). This datatype is useful for collecting and evaluating date information across geographic regions.

Specify the TIMESTAMP WITH TIME ZONE datatype as follows:

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value can be a number in the range 0 to 9. The default is 6. When you specify TIMESTAMP WITH TIME ZONE as a literal, the fractional_seconds_precision value can be any number of digits up to 9. For example:

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data. For example,

TIMESTAMP '1999-04-15 8:00:00 -8:00'

is the same as

TIMESTAMP '1999-04-15 11:00:00 -5:00'

That is, 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time.

You can replace the UTC offset with the TZR (time zone region) format element. For example, the following example has the same value as the preceding example:

TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

To eliminate the ambiguity of boundary cases when the daylight savings time switches, use both the TZR and a corresponding TZD format element. The following example ensures that the preceding example will return a daylight savings time value:

TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If that parameter is set to FALSE, then Oracle interprets the ambiguous datetime as standard time.


Note:

Oracle's time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle's time zone data may not reflect the most recent data available at this site. Please refer to Oracle9i Database Globalization Support Guide for more information on Oracle time zone data.


See Also:

TIMESTAMP WITH LOCAL TIME ZONE Datatype

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone displacement in its value. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone displacement is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.

Specify the TIMESTAMP WITH LOCAL TIME ZONE datatype as follows:

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value can be a number in the range 0 to 9. The default is 6.

There is no literal for TIMESTAMP WITH LOCAL TIME ZONE.


Note:

Oracle's time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle's time zone data may not reflect the most recent data available at this site. Please refer to Oracle9i Database Globalization Support Guide for more information on Oracle time zone data.


See Also:

INTERVAL YEAR TO MONTH Datatype

INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. This datatype is useful for representing the precise difference between two datetime values.

Specify INTERVAL YEAR TO MONTH as follows:

INTERVAL YEAR [(year_precision)] TO MONTH

where year_precision is the number of digits in the YEAR datetime field. The default value of year_precision is 2.


Note:

You have a great deal of flexibility when specifying interval values as literals. Please refer to "Interval Literals" for detailed information on specify interval values as literals.


INTERVAL DAY TO SECOND Datatype

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This datatype is useful for representing the difference between two datetime values when only the year and month values are significant.

Specify this datatype as follows:

INTERVAL DAY [(day_precision)] 
   TO SECOND [(fractional_seconds_precision)]

where

Datetime/Interval Arithmetic

Oracle lets you derive datetime and interval value expressions. Datetime value expressions yield values of datetime datatype. Interval value expressions yield values of interval datatype. Table 2-5 lists the operators that you can use in these expressions.

Table 2-5  Operators in Datetime/Interval Value Expressions
Operand 1 Operator Operand 2 Result Type

Datetime

+

Interval

Datetime

Datetime

-

Interval

Datetime

Interval

+

Datetime

Datetime

Datetime

-

Datetime

Intervala

Interval

+

Interval

Interval

Interval

-

Interval

Interval

Interval

*

Numeric

Interval

Numeric

*

Interval

Interval

Interval

/

Numeric

Interval

For example, you can add an interval value expression to a start time. Consider the sample table oe.orders with a column order_date. The following statement adds 30 days to the value of the order_date column:

SELECT order_id, order_date + INTERVAL '30' DAY FROM orders;

Oracle performs all timestamp arithmetic in UTC time. For TIMESTAMP WITH LOCAL TIME ZONE, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP WITH TIME ZONE, the datetime value is always in UTC, so no conversion is necessary.

Support for Daylight Savings Times

Oracle automatically determines, for any given time zone region, whether daylight savings is in effect and returns local time values based accordingly. The datetime value is sufficient for Oracle to determine whether daylight savings time is in effect for a given region in all cases except boundary cases. A boundary case occurs during the period when daylight savings goes into or comes out of effect. For example, in the US-Pacific region, when daylight savings goes into effect, the time changes from 2:00 a.m. to 3:00 a.m. The one hour interval between 2 and 3 a.m. does not exist. When daylight savings goes out of effect, the time changes from 2:00 a.m. back to 1:00 a.m., and the one-hour interval between 1 and 2 a.m. is repeated.

To resolve these boundary cases, Oracle uses the TZR and TZD format elements, as described in Table 2-15. TZR represents the time zone region in datetime input strings. Examples are 'Australia/North', 'UTC', and 'Singapore'. TZD represents an abbreviated form of the time zone region with daylight savings information. Examples are 'PST' for US/Pacific standard time and 'PDT' for US/Pacific daylight time. To see a listing of valid values for the TZR and TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view.


Note:

Timezone region names are needed by the daylight savings feature. The region names are stored in two time zone files. The default time zone file is a small file containing only the most common time zones to maximize performance. If your time zone is not in the default file, then you will not have daylight savings support until you provide a path to the complete (larger) file by way of the ORA_TZFILE environment variable. Please refer to Oracle9i Database Administrator's Guide for more information about setting the ORA_TZFILE environment variable.



Note:

Oracle's time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle's time zone data may not reflect the most recent data available at this site. Please refer to Oracle9i Database Globalization Support Guide for more information on Oracle time zone data.


See Also:

Datetime and Interval Example

The following example shows how to declare some datetime and interval datatypes.

CREATE TABLE time_table (
   start_time      TIMESTAMP,
   duration_1      INTERVAL DAY (6) TO SECOND (5),
   duration_2      INTERVAL YEAR TO MONTH);

The start_time column is of type TIMESTAMP. The implicit fractional seconds precision of TIMESTAMP is 6.

The duration_1 column is of type INTERVAL DAY TO SECOND. The maximum number of digits in field DAY is 6 and the maximum number of digits in the fractional second is 5. The maximum number of digits in all other datetime fields is 2.

The duration_2 column is of type INTERVAL YEAR TO MONTH. The maximum number of digits of the value in each field (YEAR and MONTH) is 2.

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 Oracle Net (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, Oracle Net 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 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.

Restrictions on LOB Columns

LOB columns are subject to the following restrictions:

The following example shows how the sample table pm.print_media was created. (This example assumes the existence of the textdoc_tab object table, which is nested table in the print_media table.)

CREATE TABLE print_media
    ( product_id        NUMBER(6)
    , ad_id             NUMBER(6)
    , ad_composite      BLOB
    , ad_sourcetext     CLOB
    , ad_finaltext      CLOB
    , ad_fltextn        NCLOB
    , ad_textdocs_ntab  textdoc_tab
    , ad_photo          BLOB
    , ad_graphic        BFILE
    , ad_header         adheader_typ
    , press_release     LONG
    ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;
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.

You can change the filename and path of a BFILE without affecting the base table by using the BFILENAME function.

See Also:

BFILENAME for more information on this built-in SQL function

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 enables 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. However, 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 and multibyte 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. However, 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 Unicode data using the national character set. 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. However, 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.

See Also:

Oracle9i Database Globalization Support Guide for information on Unicode datatype support

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

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

Oracle9i Supplied PL/SQL Packages and Types 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 Oracle9i for backward compatibility, but all tables return rowids in the extended format.

See Also:

Oracle9i Database Migration Guide 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 table (that is, using the SELECT ROWID statement). If you wish to store the rowids of an index-organized table, then 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 that differs from the Oracle datatype name, 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-6 and Table 2-7.

Table 2-6  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)a

NUMBER(p,s)

INTEGER

INT

SMALLINT

NUMBER(38)

FLOAT(b)b

DOUBLE PRECISIONc

REALd

NUMBER

Table 2-7  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)a

NUMBER(p,s)

INTEGER

SMALLINT

NUMBER(38)

FLOAT(b)b

NUMBER

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

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

User-Defined Types

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 object view oc_orders in the sample schema oe, the column customer_ref is of type REF to type customer_typ, which has an attribute cust_email:

SELECT o.customer_ref.cust_email
   FROM oc_orders o 
   WHERE o.customer_ref 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, then Oracle will store it out of line, regardless of its size.

See Also:

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

Oracle-Supplied Types

Oracle Corporation provides SQL-based interfaces for defining new types when the built-in or ANSI-supported types are not sufficient. The behavior for these types can be implemented in C/C++, Java, or PL/ SQL. Oracle automatically provides the low-level infrastructure services needed for input-output, heterogeneous client-side access for new datatypes, and optimizations for data transfers between the application and the database.

These interfaces can be used to build user-defined (or object) types, and are also used by Oracle to create some commonly useful datatypes. Several such datatypes are supplied with the server, and they serve both broad horizontal application areas (for example, the "Any" types) and specific vertical ones (for example, the spatial type).

The Oracle-supplied types, along with cross-references to the documentation of their implementation and use, are described in the following sections:

"Any" Types

The "Any" types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These datatypes let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. These types have OCI and PL/SQL interfaces for construction and access.

SYS.ANYTYPE

This type can contain a type description of any named SQL type or unnamed transient type.

SYS.ANYDATA

This type contains an instance of a given type, with data, plus a description of the type. ANYDATA can be used as a table column datatype and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types.

SYS.ANYDATASET

This type contains a description of a given type plus a set of data instances of that type. ANYDATASET can be used as a procedure parameter datatype where such flexibility is needed. The values of the data instances can be of SQL built-in types as well as user-defined types.

See Also:

Oracle Call Interface Programmer's Guide, PL/SQL User's Guide and Reference, and Oracle9i Application Developer's Guide - Fundamentals for the implementation of these types and guidelines for using them

XML Types

Extensible Markup Language (XML) is a standard format developed by the World Wide Web Consortium (W3C) for representing structured and unstructured data on the Web. Universal Resource Identifiers (URIs) identify resources such as Web pages anywhere on the Web. Oracle provides types to handle XML and URI data, as well as a class of URIs called DBURIRefs to access data stored within the database itself. It also provides a new set of types to store and access both external and internal URIs from within the database.

XMLType

This Oracle-supplied type can be used to store and query XML data in the database. XMLType has member functions you can use to access, extract, and query the XML data using XPath expressions. XPath is another standard developed by the W3C committee to traverse XML documents. Oracle XMLType functions support many W3C XPath expressions. Oracle also provides a set of SQL functions and PL/SQL packages to create XMLType values from existing relational or object-relational data.

XMLType is a system-defined type, so you can use it as an argument of a function or as the datatype of a table or view column. You can also create tables and views of XMLType. When you create an XMLType column in a table, you can choose to store the XML data in a CLOB column or object relationally.

You can also register the schema (using the DBMS_XMLSCHEMA package) and create a table or column conforming to the registered schema. In this case Oracle stores the XML data in underlying object-relational columns by default, but you can specify storage in a CLOB column even for schema-based data.

Queries and DML on XMLType columns operate the same regardless of the storage mechanism.

URI Datatypes

Oracle supplies a family of URI types--URIType, DBURIType, XDBURIType, and HTTPURIType--which are related by an inheritance hierarchy. URIType is an object type and the others are subtypes of URIType. Since URIType is the supertype, you can create columns of this type and store DBURIType or HTTPURIType type instances in this column.

HTTPURIType

You can use HTTPURIType to store URLs to external Web pages or to files. Oracle accesses these files using the HTTP (Hypertext Transfer Protocol) protocol.

XDBURIType

You can use XDBURIType to expose documents in the XML database hierarchy as URIs that can be embedded in any URIType column in a table. The XDBURIType consists of a URL, which comprises the hierarchical name of the XML document to which it refers and an optional fragment representing the XPath syntax. The fragment is separated from the URL part by a pound sign (#). The following lines are examples of XDBURIType:

/home/oe/doc1.xml
/home/oe/doc1.xml#/orders/order_item
DBURIType

DBURIType can be used to store DBURIRefs, which reference data inside the database. Storing DBURIRefs lets you reference data stored inside or outside the database and access the data consistently.

DBURIRefs use an XPath-like representation to reference data inside the database. If you imagine the database as an XML tree, then you would see the tables, rows, and columns as elements in the XML document. For instance, the sample human resources user hr would see the following XML tree:

<HR> 
  <EMPLOYEES> 
    <ROW> 
      <EMPLOYEE_ID>205</EMPLOYEE_ID> 
      <LAST_NAME>Higgins</LAST_NAME> 
      <SALARY>12000</SALARY> 
      .. <!-- other columns --> 
    </ROW> 
    ... <!-- other rows --> 
  </EMPLOYEES> 
  <!-- other tables..--> 
</HR> 
<!-- other user schemas on which you have some privilege on..--> 

The DBURIRef is simply an XPath expression over this virtual XML document. So to reference the SALARY value in the EMPLOYEES table for the employee with employee number 205, we can write a DBURIRef as,

/HR/EMPLOYEES/ROW[EMPLOYEE_ID=205]/SALARY 

Using this model, you can reference data stored in CLOB columns or other columns and expose them as URLs to the external world.

URIFactory Package

Oracle also provides the URIFactory package, which can create and return instances of the various subtypes of the URITypes. The package analyzes the URL string, identifies the type of URL (HTTP, DBURI, and so on), and creates an instance of the subtype. To create a DBURI instance, the URL must start with the prefix /oradb. For example, URIFactory.getURI('/oradb/HR/EMPLOYEES') would create a DBURIType instance and URIFactory.getUri('/sys/schema') would create an XDBURIType instance.

See Also:

Spatial Type

The object-relational implementation of Oracle Spatial consists of a set of object data types, an index method type, and operators on these types.

MDSYS.SDO_GEOMETRY

The geometric description of a spatial object is stored in a single row, in a single column of object type SDO_GEOMETRY in a user-defined table. Any table that has a column of type SDO_GEOMETRY must have another column, or set of columns, that defines a unique primary key for that table. Tables of this sort are sometimes referred to as geometry tables.

See Also:

Oracle Spatial User's Guide and Reference for information on the implementation of this type and guidelines for using it

Media Types

Oracle interMedia uses object types, similar to Java or C++ classes, to describe multimedia data. An instance of these object types consists of attributes, including metadata and the media data, and methods. The Oracle interMedia types are:

ORDSYS.ORDAudio

The ORDAUDIO object type supports the storage and management of audio data.

ORDSYS.ORDImage

The ORDIMAGE object type supports the storage and management of image data.

ORDSYS.ORDImageSignature

The ORDSYS.ORDImageSignature type supports a compact representation of the color, texture, and shape information of image data.

ORDSYS.ORDVideo

The ORDVIDEO object type supports the storage and management of video data.

ORDSYS.ORDDoc

The ORDDOC object type supports storage and management of any type of media data, including audio, image and video data. Use this type when you want all media to be stored in a single column.

See Also:

Oracle interMedia User's Guide and Reference for information on the implementation of these types and guidelines for using them

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.

Blank-Padded Comparison Semantics

If the two values have different lengths, then 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, then 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.

The results of comparing two character values using different comparison semantics may vary. The table that follows 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

'ac' > 'ab'

'ac' > 'ab'

'ab' > 'a '

'ab' > 'a   '

'ab' > 'a'

'ab' > 'a'

'ab' = 'ab'

'ab' = 'ab'

'a ' = 'a'

'a ' > 'a'

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-8 and Table 2-9. 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-8  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-9  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 Oracle9i 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 Oracle9i.

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 and Explicit Data Conversion

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

Implicit Data Conversion

Oracle automatically converts a value from one datatype to another when such a conversion makes sense. Table 2-10 is a matrix of Oracle implicit conversions. The table shows all possible conversions, without regard to the direction of the conversion or the context in which it is made. The rules governing these details follow the table.

Table 2-10  Implicit Type Conversion Matrix
CHAR VARCHAR2 DATE DATETIME/ INTERVAL LONG NUMBER RAW ROWID CLOB BLOB NCHAR NVARCHAR NCLOB

CHAR

--

X

X

X

X

X

X

--

X

--

X

X

--

VARCHAR2

X

--

X

X

X

X

X

X

X

--

X

X

--

DATE

X

X

--

--

--

--

--

--

--

--

X

X

--

DATETIME/ INTERVAL

X

X

--

--

X

--

--

--

--

--

X

X

--

LONG

X

X

--

X

--

--

X

--

X

--

X

X

X

NUMBER

X

X

--

--

--

--

--

--

--

--

X

X

--

RAW

X

X

--

--

X

--

--

--

--

X

X

X

--

ROWID

X

X

--

--

--

--

--

--

--

--

X

X

--

CLOB

X

X

--

--

X

--

--

--

--

--

--

--

--

BLOB

--

--

--

--

--

--

X

--

--

--

--

--

--

NCHAR

X

X

X

X

X

X

X

X

--

--

--

X

X

NVARCHAR2

X

X

X

X

X

X

X

X

--

--

X

--

X

NCLOB

--

--

--

--

X

--

--

--

--

--

X

X

--

The following rules govern the direction in which Oracle makes implicit datatype conversions:

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 salary + '10'
   FROM employees;
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 '200' to 200:

SELECT last_name
    FROM employees 
    WHERE employee_id = '200';
Date Example

In the following statement, Oracle implicitly converts '03-MAR-97' to a DATE value using the default date format 'DD-MON-YY':

SELECT last_name
    FROM employees 
    WHERE hire_date = '03-MAR-97';
Rowid Example

In the following statement, Oracle implicitly converts the text literal 'AAAFYmAAFAAAAFGAAH' to a rowid value. (Rowids are unique within a database, so to use this example you must know an actual rowid in your database.)

SELECT last_name
    FROM employees
    WHERE ROWID = 'AAAFd1AAFAAAABSAAH';

Explicit Data Conversion

You can also explicitly specify datatype conversions using SQL conversion functions. The following table shows SQL functions that explicitly convert a value from one datatype to another.

Table 2-12  Explicit Type Conversion
to CHAR, VARCHAR2, NCHAR, NVARCHAR2 to NUMBER to Datetime/ Interval to RAW to ROWID to LONG, LONG RAW to CLOB, NCLOB, BLOB

from CHAR, VARCHAR2, NCHAR, NVARCHAR2

TO_CHAR (character)

TO_NCHAR (character)

TO_NUMBER

TO_DATE

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_YMINTERVAL

TO_DSINTERVAL

HEXTORAW

CHARTOROWID

--

TO_CLOB

TO_NCLOB

from NUMBER

TO_CHAR (number)

TO_NCHAR (number)

--

TO_DATE

NUMTOYMINTERVAL

NUMTODSINTERVAL

--

--

--

--

from Datetime/ Interval

TO_CHAR (date)

TO_NCHAR (datetime)

--

--

--

--

--

--

from RAW

RAWTOHEX

RAWTONHEX

--

--

--

--

--

TO_BLOB

from ROWID

ROWIDTOCHAR

--

--

--

--

--

--

from LONG / LONG RAW

--

--

--

--

--

--

TO_LOB

from CLOB, NCLOB, BLOB

TO_CHAR

TO_NCHAR

--

--

--

--

--

TO_CLOB

TO_NCLOB


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" of the SQL Reference for details on all of the explicit conversion functions