Skip Headers

Oracle® Database Application Developer's Guide - Fundamentals
10g Release 1 (10.1)

Part Number B10795-01
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
Previous
Go to next page
Next
View PDF

2
Selecting a Datatype

This chapter discusses how to use Oracle built-in datatypes in applications. Topics include:

Summary of Oracle Built-In Datatypes

A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle Database to treat values of one datatype differently from values of another datatype. For example, Oracle Database can add values of NUMBER datatype, but not values of RAW datatype.

Oracle supplies the following built-in datatypes:

Table 2-1 summarizes the information about each Oracle built-in datatype.

Table 2-1   Summary of Oracle Built-In Datatypes
Datatype Description Column Length / Default Values

CHAR
[(
size [BYTE | CHAR])]

Fixed-length character data of length size bytes or characters.

Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. When neither BYTE nor CHAR is specified, the setting of NLS_LENGTH_SEMANTICS at the time of column creation determines which is used. Consider the character set (single-byte or multibyte) before setting size.

VARCHAR2
(
size [BYTE | CHAR])

Variable-length character data, with maximum length size bytes or characters. BYTE or CHAR indicates that the column has byte or character semantics, respectively. A size must be specified.

Variable for each row, up to 4000 bytes per row. When neither BYTE nor CHAR is specified, the setting of NLS_LENGTH_SEMANTICS at the time of column creation determines which is used. Consider the character set (single-byte or multibyte) before setting size.

NCHAR [(size)]

Fixed-length Unicode character data of length size characters. The number of bytes is twice this number for the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.)

Fixed for every row in the table (with trailing blanks). The upper limit is 2000 bytes per row. Default size is 1 character.

NVARCHAR2 (size)

Variable-length Unicode character data of maximum length size characters. The number of bytes may be up to 2 times size for a the AL16UTF16 encoding and 3 times this number for the UTF8 encoding. A size must be specified.

Variable for each row. The upper limit is 4000 bytes per row.

CLOB

Single-byte or multibyte character data. Both fixed-width and variable-width character sets are supported, and both use the CHAR character set.

Up to 232 - 1 bytes * (database block size), or 4 gigabytes * block size.Foot 1 See Oracle Database Application Developer's Guide - Large Objects.

NCLOB

Unicode national character set (NCHAR) data. Both fixed-width and variable-width character sets are supported, and both use the NCHAR character set.

Up to 232 - 1 bytes * (database block size), or 4 gigabytes * block size. Footref 1 See Oracle Database Application Developer's Guide - Large Objects.

LONG

Variable-length character data. Provided for backward compatibility.

Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row.

BINARY_FLOAT

32-bit floating-point number.

4 bytes.

BINARY_DOUBLE

64-bit floating-point number.

8 bytes.

NUMBER
[(
prec | prec, scale)]

Variable-length numeric data. Precision prec is the the total number of digits; scale scale is the number of digits to the right of the decimal point. Precision can range from 1 to 38. Scale can range from -84 to 127. With precision specified, this is a floating-point number; with no precision specified, it is a fixed-point number.

Variable for each row. The maximum space available for a given column is 21 bytes per row.

DATE

Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 9999 C.E.

Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-RR) specified by the NLS_DATE_FORMAT parameter.

INTERVAL YEAR
[(
yr_prec)] TO MONTH

A period of time, represented as years and months. The yr_prec is the number of digits in the YEAR field of the date. The precision can be from 0 to 9, and defaults to 2 digits.

Fixed at 5 bytes.

INTERVAL DAY
[(
day_prec)] TO SECOND [(frac_sec_prec)]

A period of time, represented as days, hours, minutes, and seconds. The day_prec and frac_sec_prec are the number of digits in the DAY and the fractional SECOND fields of the date, respectively. These precision values can each be from 0 to 9, and they default to 2 digits for day_prec and 6 digits for frac_sec_prec.

Fixed at 11 bytes.

TIMESTAMP
[(
frac_sec_prec)]

A value representing a date and time, including fractional seconds. (The exact resolution depends on the operating system clock.)

The frac_sec_prec specifies the number of digits in the fractional second part of the SECOND date field. The frac_sec_prec can be from 0 to 9, and defaults to 6 digits.

Varies from 7 to 11 bytes, depending on the precision. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.

TIMESTAMP
[(
frac_sec_prec)] WITH TIME ZONE

A value representing a date and time, plus an associated time zone setting. The time zone can be an offset from UTC, such as '-5:0', or a region name, such as 'US/Pacific'.

The frac_sec_prec is as for datatype TIMESTAMP.

Fixed at 13 bytes. The default is determined by the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.

TIMESTAMP
[(
frac_sec_prec)] WITH LOCAL TIME ZONE

Similar to TIMESTAMP WITH TIME ZONE, except that the data is normalized to the database time zone when stored, and adjusted to match the client's time zone when retrieved.

The frac_sec_prec is as for datatype TIMESTAMP.

Varies from 7 to 11 bytes, depending on frac_sec_prec. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.

BLOB

Unstructured binary data.

Up to 232 - 1 bytes * (database block size), or 4 gigabytes * block size.Footref 1 See Oracle Database Application Developer's Guide - Large Objects.

BFILE

Address of a binary file stored outside the database. Enables byte-stream I/O access to external LOBs residing on the database server.

The referenced file can be up to 232 - 1 bytes * (database block size), or 4 gigabytes * block size.Footref 1 See Oracle Database Application Developer's Guide - Large Objects.

RAW (size)

Variable-length raw binary data. A size, which is the maximum number of bytes, must be specified. Provided for backward compatibility.

Variable for each row in the table, up to 2000 bytes per row.

LONG RAW

Variable-length raw binary data. Provided for backward compatibility.

Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row.

ROWID

Base 64 binary data representing a row address. Used primarily for values returned by the ROWID pseudocolumn.

Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.

UROWID [(size)]

Base 64 binary data representing the logical address of a row in an index-organized table. The optional size is the number of bytes in a column of type UROWID.

Maximum size and default are both 4000 bytes.

1 Prior to Oracle Database 10g, the limit was 4 gigabytes, not 4 gigabytes*blocksize.

Representing Character Data

Use the character datatypes to store alphanumeric data:

When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:

CHAR, VARCHAR2, and LONG data is automatically converted by the NLS_LANGUAGE parameter from the database character set to the character set defined for the user session, if these are different.

Column Lengths for Single-Byte and Multibyte Character Sets

The lengths of CHAR and VARCHAR2 columns can be specified as either bytes or characters.

The lengths of NCHAR and NVARCHAR2 columns are always specified in characters, making them ideal for storing Unicode data, where a character might consist of multiple bytes.

-- ID contains only single-byte data, up to 32 bytes.
ID VARCHAR2(32 BYTE);
-- NAME contains data in the database character set. The 32 characters might
-- be stored as more than 32 bytes, if the database character set allows
-- multibyte characters.
NAME VARCHAR2(32 CHAR);
-- BIOGRAPHY can represent 2000 characters in any Unicode-representable 
-- language.
-- The exact encoding depends on the national character set, but the column can 
-- contain multibyte values even if the database character set is single-byte.
BIOGRAPHY NVARCHAR2(2000);
-- The representation of COMMENT, as 2000 bytes or 2000 characters, depends
-- on the initialization parameter NLS_LENGTH_SEMANTICS.
COMMENT VARCHAR2(2000);

When using a multibyte database character encoding scheme, consider carefully the space required for tables with character columns. If the database character encoding scheme is single-byte, then the number of bytes and the number of characters in a column is the same. If it is multibyte, then there generally is no such correspondence. A character might consist of one or more bytes, depending upon the specific multibyte encoding scheme and whether shift-in/shift-out control codes are present. To avoid overflowing buffers, specify data as NCHAR or NVARCHAR2 if it might use a Unicode encoding that is different from the database character set.

See Also:

Implicit Conversion Between CHAR/VARCHAR2 and NCHAR/NVARCHAR2

In database releases prior to Oracle9i, the NCHAR and NVARCHAR2 types were difficult to use because they could not be interchanged with CHAR and VARCHAR2. For example, an NVARCHAR2 literal required special notation, such as N'string_value'. In releases after Oracle8i, you can specify NCHAR and NVARCHAR2 without the N notation, and you can mix them with CHAR and VARCHAR2 values in SQL statements and functions.

Comparison Semantics

Oracle Database compares CHAR and NCHAR values using blank-padded comparison semantics. If two values have different character lengths, then Oracle Database adds space characters at the end of the shorter value, until the two values are the same length. Oracle Database 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. Two values that differ only in the number of trailing blanks are thus considered equal.

Oracle Database compares VARCHAR2 and NVARCHAR2 values using non-padded comparison semantics. Two values are considered equal only if they have the same characters and are of equal length. Oracle Database compares the values character-by-character up to the first character that differs. The value with the greater character in that position is considered greater. If one value is a prefix of the other, then it is considered less ("abc" < "abcxyz")

Because Oracle Database blank-pads values stored in CHAR columns but not in VARCHAR2 columns, a value stored in a VARCHAR2 column may take up less space than if it were stored in a CHAR column. For this reason, a full table scan on a large table containing VARCHAR2 columns may read fewer data blocks than a full table scan on a table containing the same data stored in CHAR columns. If your application often performs full table scans on large tables containing character data, then you might be able to improve performance by storing this data in VARCHAR2 columns rather than in CHAR columns.

However, performance is not the only factor to consider when deciding which of these datatypes to use. Oracle Database uses different semantics to compare values of each datatype. You might choose one datatype over the other if your application is sensitive to the differences between these semantics. For example, if you want Oracle Database to ignore trailing blanks when comparing character values, then you must store these values in CHAR columns.

See Also:

Oracle Database SQL Reference for more information on comparison semantics for these datatypes

Representing Numeric Data with Number and Floating-Point Datatypes

The following SQL datatypes allow you to store numeric data:

The BINARY_FLOAT and BINARY_DOUBLE datatypes store floating-point data in the 32-bit IEEE 754 format and the double precision 64-bit IEEE 754 format respectively. Compared to the Oracle NUMBER datatype, arithmetic operations on floating-point data are usually faster for BINARY_FLOAT and BINARY_DOUBLE. Also, high-precision values require less space when stored as BINARY_FLOAT and BINARY_DOUBLE.

In client interfaces supported by Oracle Database, arithmetic operations on BINARY_FLOAT and BINARY_DOUBLE datatypes are performed by the native instruction set supplied by the hardware vendor. The term native floating-point datatypes is used here to refer to datatypes including BINARY_FLOAT and BINARY_DOUBLE, and to all implementations of these types in supported client interfaces.

Floating-Point Number System Concepts

The floating-point number system is a common way of representing and manipulating numeric values in computer systems. A floating-point number is characterized by these components: a binary-valued sign, a signed exponent, a significand, and a base. Its value is the signed product of its significand and the base raised to the power of its exponent:

(-1)sign . significand . base exponent

For example, the number 4.31 can be represented as (-1)0 . 431 . 10 -2, with sign 0, significand 431, base 10, and exponent -1.

A floating-point number format specifies how the components of a floating-point number are represented. The choice of representation determines the range and precision of the values the format can represent. By definition, the range is the interval bounded by the smallest and the largest values the format can represent and the precision is the number of digits in the significand.

Formats for floating-point values support neither infinite precision nor infinite range. There are a finite number of bits to represent a number and only a finite number of values that a format can represent. A floating-point number that uses more precision than available with a given format is rounded.

A floating-point number can be represented in a binary system (one that uses base 2), as in the IEEE 754 standard, or in a decimal system (one that uses base 10), such as Oracle NUMBER. The base affects many properties of the format, including how a numeric value is rounded.

For a decimal floating-point number format like Oracle NUMBER, rounding is done to the nearest decimal place (for example. 1000, 10, or 0.01). The IEEE 754 formats use a binary format for floating-point values and round numbers to the nearest binary place (for example: 1024, 512, or 1/64).

The native floating-point datatypes supported by the database round to the nearest binary place, so they are not satisfactory for applications that require decimal rounding. Use the Oracle NUMBER datatype for applications where decimal rounding is required on floating-point data.

About Floating-Point Formats

The value of a floating-point number that uses a binary format is determined by:

(-1)s 2E (b0 b1 b2 ... bp-1)

where

s = 0 or 1

E = any integer between Emin and Emax, inclusive (see Table 2-2)

bi = 0 or 1; the sequence of bits represents a number in base 2

The leading bit of the significand, b0, must be set (1), except for subnormal numbers (explained later). Consequently, the leading bit is not actually stored. Consequently, the formats provide N bits of precision, although only N-1 bits are stored.


Note:

The IEEE 754 specification also defines extended single-precision and extended double-precision formats, which are not supported by Oracle Database.


The parameters for these formats are listed in Table 2-2, and the storage parameters for the formats are listed in Table 2-3. The in-memory formats for single-precision and double-precision datatypes are specified by IEEE 754.

Table 2-2   Summary of Format Parameters
Parameter Single-precision (32-bit) Double-precision (64-bit)

p

24

53

Emin

-126

-1022

Emax

+127

+1023

Table 2-3   Summary of Storage Parameters
Datatype Sign bits Exponent bits Significand bits Total bits

single-precision

1

8

24 (23 stored)

32

double-precision

1

11

53 (52 stored)

64

A significand is normalized when the leading bit of the significand is set. IEEE 754 defines denormal or subnormal values as numbers that are too small to be represented with an implied leading set bit in the significand. The number is too small because its exponent would be too large if its significand were normalized to have an implied leading bit set. IEEE 754 formats support subnormal values. Subnormal values preserve the following property:

if: x - y == 0.0 (using floating-point subtraction)

then: x == y

Table 2-4 shows the range and precision of the required formats in the IEEE 754 standard and those of Oracle NUMBER. Range limits are expressed here in terms of positive numbers; they also apply to the absolute value of a negative number. (The notation "number e exponent" used here stands for number multiplied by 10 raised to the exponent power: number . 10 exponent.)

Table 2-4   Range and Precision of IEEE 754 formats
Range and Precision Single-precision 32-bitFoot 1 Double-precision 64-bit1 Oracle NUMBER Datatype

Max positive normal number

3.40282347e+38

1.7976931348623157e+308

< 1.0e126

Min positive normal number

1.17549435e-38

2.2250738585072014e-308

1.0e-130

Max positive subnormal number

1.17549421e-38

2.2250738585072009e-308

not applicable

Min positive subnormal number

1.40129846e-45

4.9406564584124654e-324

not applicable

Precision (decimal digits)

6 - 9

15 - 17

38 - 40

1 These numbers are quoted from the IEEE Numerical Computation Guide.
See Also:

Oracle Database SQL Reference

Representing Special Values with Native Floating-Point Formats

IEEE 754 allows special values to be represented. These special values are positive infinity (+INF), negative infinity (-INF), and not-a-number (NaN). IEEE 754 also distinguishes between positive zero (+0) and negative zero (-0). NaN is used to represent results of operations that are undefined.

There are many bit patterns in IEEE 754 that represent NaN. Bit patterns can represent NaN with and without the sign bit set. IEEE 754 distinguishes between signalling NaNs and quiet NaNs. IEEE 754 specifies behavior for when exceptions are enabled and disabled. Oracle Database does not allow exceptions to be enabled; the database behavior is that specified by IEEE 754 for when exceptions are disabled. In particular, no distinction is made between signalling NaNs and quiet NaNs. Programmers using Oracle Call Interface can retrieve NaN values from Oracle Database; whether a retrieved NaN value is signalling or quiet is dependent on the client platform and beyond the control of Oracle Database.

IEEE 754 does not define the bit pattern for either type of NaN. Positive infinity, negative infinity, positive zero, and negative zero are each represented by a specific bit pattern.

Ignoring signs, there are five classes of values: zero, subnormal, normal, infinity and NaN. The first four classes are ordered as:

zero < subnormal < normal < infinity

In IEEE 754, NaN is unordered with other classes of special values and with itself.

Behavior of Special Values for Native Floating-Point Datatypes

When used with the database, special values of native floating-point datatypes behave as follows:

Rounding of Native Floating-Point Datatypes

IEEE 754 defines four rounding modes. The rounding modes are: round to nearest (default), round to positive infinity, round to negative infinity, and round to zero. Oracle Database supports only round to nearest mode.

Comparison Operators for Native Floating-Point Datatypes

Comparison operators are defined for equal to, not equal to, greater than, greater than or equal to, less than, less than or equal to, and unordered. There are special cases:

Arithmetic Operators for Native Floating-Point Datatypes

Arithmetic operators are defined for multiplication, division, addition, subtraction, remainder, and square root. The mode used to round the result of the operation can be defined. Exceptions can be raised when operations are performed. Exceptions can also be disabled.

Until recently, Java required floating-point arithmetic to be exactly reproducible. IEEE 754 does not require such behavior. IEEE 754 allows for the result of operations, including arithmetic, to be delivered to a destination that uses a range greater than that used by the operands to the operation. The result of a double-precision multiplication can be computed at an extended double-precision destination. When this is done, the result must be rounded as if the destination were single-precision or double-precision. However, the range of the result (the number of bits used for the exponent) can use the range supported by the wider (extended double-precision) destination. This may result in a double-rounding error in which the least significant bit of the result is incorrect.

This can only occur for double-precision multiplication and division on hardware that implements the IA-32 and IA-64 instruction set architecture. Thus, with the exception of this case, arithmetic for these datatypes will be reproducible across platforms. When the result of a computation is NaN, all platforms will produce a value for which IS NAN is true. However, all platforms do not have to use the same bit pattern.

Conversion Functions for Native Floating-Point Datatypes

Functions are defined that convert between floating-point and other formats, including string formats that use decimal precision. Precision may be lost during the conversion. Exceptions can be raised during conversion. The following conversions can be done:

Exceptions for Native Floating-Point Datatypes

The IEEE 754 specification defines the following exceptions that can be thrown: invalid, inexact, divide by zero, underflow, and overflow. Oracle Database does not raise these exceptions for native floating-point datatypes. Generally, situations that would raise an exception produce the following values:

Exception Value

Underflow

0

Overflow

-INF, +INF

Invalid Operation

NaN

Divide by Zero

-INF, +INF, NaN

Inexact

any value - rounding was performed


Client Interfaces for Native Floating-Point Datatypes

Support for native floating-point datatypes is implemented in the following client interfaces:

SQL Native Floating-Point Datatypes

The SQL datatypes BINARY_FLOAT and BINARY_DOUBLE implement native floating-point datatypes in the SQL environment. A number of SQL functions are provided that operate on these datatypes. BINARY_FLOAT and BINARY_DOUBLE are supported wherever an expression (expr) appears in SQL syntax.

See Also:

Oracle Database SQL Reference for details on SQL functions and the implementation of these datatypes

OCI Native Floating-Point Datatypes SQLT_BFLOAT and SQLT_BDOUBLE

The Oracle Call Interface (OCI) application programming interface (API) implements the IEEE 754 single precision and double precision native floating-point datatypes with the datatypes SQLT_BFLOAT and SQLT_BDOUBLE respectively.

Conversions between these types and the SQL types BINARY_FLOAT and BINARY_DOUBLE are exact on platforms that implement the IEEE 754 standard for the C datatypes float and double.

See Also:

Oracle Call Interface Programmer's Guide

Native Floating-Point Datatypes Supported in Oracle OBJECT Types

The SQL datatypes BINARY_FLOAT and BINARY_DOUBLE are supported as attributes of Oracle OBJECT types.

Pro*C/C++ Support for Native Floating-Point Datatypes

Storing Data Using the NUMBER Datatype

Use the NUMBER datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle Database platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10-130 through 9.99 x10125, as well as zero, in a NUMBER column.

You can specify that a column contains a floating-point number, for example:

distance NUMBER

Or, you can specify a precision (total number of digits) and scale (number of digits to the right of the decimal point):

price NUMBER (8, 2)

Although not required, specifying precision and scale helps to identify bad input values. If a precision is not specified, the column stores values as they are provided. Table 2-5 shows examples of how data different scale factors affect storage.

Table 2-5   How Scale Factors Affect Numeric Data Storage
Input Data Specified As Stored As

7,456,123.89

NUMBER

7456123.89

7,456,123.89

NUMBER (9)

7456124

7,456,123.89

NUMBER (9,2)

7456123.89

7,456,123.89

NUMBER (9,1)

7456123.9

7,456,123.89

NUMBER (6)

(not accepted; value exceeds precision)

7,456,123.89

NUMBER (7, -2)

7456100

See Also:

Oracle Database Concepts for information about the internal format for the NUMBER datatype

Representing Date and Time Data

Use the DATE datatype to store point-in-time values (dates and times) in a table. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds.

Use the TIMESTAMP datatype to store values that are precise to fractional seconds. For example, an application that must decide which of two events occurred first might use TIMESTAMP. An application that needs to specify the time for a job to execute might use DATE.

Because TIMESTAMP WITH TIME ZONE can also store time zone information, it is particularly suited for recording date information that must be gathered or coordinated across geographic regions.

Use TIMESTAMP WITH LOCAL TIME ZONE when the time zone is not significant. For example, you might use it in an application that schedules teleconferences, where participants each see the start and end times for their own time zone.

The TIMESTAMP WITH LOCAL TIME ZONE type is appropriate for two-tier applications where you want to display dates and times using the time zone of the client system. It is generally inappropriate in three-tier applications such as those involving a Web server, because data displayed in a Web browser is formatted according to the time zone of the Web server, not the time zone of the browser. (The Web server is the database client, so its local time is used.)

Use INTERVAL DAY TO SECOND to represent the precise difference between two DATETIME values. For example, you might use this value to set a reminder for a time 36 hours in the future, or to record the time between the start and end of a race. To represent long spans of time, including multiple years, with high precision, you can use a large value for the days portion.

Use INTERVAL YEAR TO MONTH to represent the difference between two DATETIME values, where the only significant portions are the year and the month. For example, you might use this value to set a reminder for a date 18 months in the future, or check whether 6 months have elapsed since a particular date.

Oracle Database uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

See Also:

Oracle Call Interface Programmer's Guide for a complete description of the Oracle Database internal date format

Date Format

For input and output of dates, the standard Oracle Database default date format is DD-MON-RR. For example:

'13-NOV-92'

To change this default date format on an instance-wide basis, use the NLS_DATE_FORMAT parameter. To change the format during a session, use the ALTER SESSION statement. To enter dates that are not in the current default date format, use the TO_DATE function with a format mask. For example:

TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')
See Also:

Oracle Database Concepts for information about Julian dates. Oracle Database Julian dates might not be compatible with Julian dates generated by other date algorithms.

Be careful using a date format like DD-MON-YY. The YY indicates the year in the current century. For example, 31-DEC-92 is December 31, 2092, not 1992 as you might expect. If you want to indicate years in any century other than the current one, use a different format mask, such as the default RR.

Checking If Two DATE Values Refer to the Same Day

To compare dates that have time data, use the SQL function TRUNC to ignore the time component.

Displaying the Current Date and Time

Use the SQL function SYSDATE to return the system date and time.

Setting SYSDATE to a Constant Value

The FIXED_DATE initialization parameter lets you set SYSDATE to a constant, which can be useful for testing.

Printing a Date with BC/AD Notation

SQL> -- By default, the date is printed without any BC or AD qualifier.
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
24-JAN-02
SQL> -- Adding BC to the format string prints the date with BC or AD
SQL> -- as appropriate.
SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY BC') FROM DUAL;

TO_CHAR(SYSDAT
--------------
24-JAN-2002 AD

Time Format

Time is stored in 24-hour format, HH24:MI:SS. By default, the time in a DATE column is 12:00:00 A.M. (midnight) if no time portion is entered, or if the DATE is truncated. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, use the TO_DATE function with a format mask indicating the time portion, as in:

INSERT INTO Birthdays_tab (bname, bday) VALUES
    ('ANNIE',TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.'));

Note:

You may need to set up the following data structures for certain examples to work:

CREATE TABLE Birthdays_tab (Bname VARCHAR2(20),Bday DATE)

Performing Date Arithmetic

Oracle Database provides a number of features to help with date arithmetic, so that you do not need to perform your own calculations on the number of seconds in a day, the number of days in each month, and so on.

Some useful functions include:

Converting Between Datetime Types

Some useful functions include:

Handling Time Zones

Oracle Database provides a number of functions to help with calculations involving time zones. For example, TO_DATE does not work with values of type TIMESTAMP WITH TIME ZONE; you must use TO_TIMESTAMP_TZ instead.

Some useful functions include:

Importing and Exporting Datetime Types

TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE values are always stored in normalized format, so that you can export, import, and compare them without worrying about time zone offsets. DATE and TIMESTAMP values do not store an associated time zone, and you must adjust them to account for any time zone differences between source and target databases.

Establishing Year 2000 Compliance

An application must satisfy the following criteria to meet the requirements for Year 2000 (Y2K) compliance:

These criteria are a superset of the Year 2000 conformance requirements set out by the British Standards Institute in DISC PD-2000-1, A Definition of Year 2000 Conformity Requirements.

You can warrant your application as Y2K compliant only if you have validated its conformance at all three of the following system levels:

Oracle Server Year 2000 Compliance

The Oracle Server is Year 2000 compliant. Oracle's Development Organization has conducted tests of various Year 2000 operational scenarios to verify that there is no impact to users with respect to the year 2000. These scenarios included tests of replication, point-in-time recovery, distributed transactions. System management and networking features across time zones / datelines / centuries have also been tested.

Oracle's Year 2000 product compliance does not eliminate the need for you to test your own applications. Most importantly, your application software must be tested on Oracle Database to ensure that operations having to do with the year 2000 perform as promised. This test is critical even if the application software is certified to be Year 2000 compliant, because there are no universal protocol definitions that can guarantee conformance without such testing.

Centuries and the Year 2000

Oracle Database stores year data with the century information. For example, it stores 1996 or 2001, and not just 96 or 01. The DATE datatype always stores a four-digit year internally, and all other dates stored internally in the database also have four digit years. Oracle Database utilities such as import, export, and recovery also deal properly with four-digit years.

Applications that use Oracle Database (version 7 or later) and exploit the DATE datatype (for dates or dates with time values) need have no concerns about their stored data and the year 2000. Beginning with Oracle Database version 7, the DATE datatype stores date and time data to a precision that includes a four digit year and a time component down to seconds (typically 'YYYY:MM:DD:HH24:MI:SS')

However, some applications might be written with an assumption about the year (such as assuming that everything is 19xx). Such an application might hand over a two-digit year to the database, and the procedures that Oracle Database uses for determining the century could be different from what the programmer expects (see "Troubleshooting Y2K Problems in Applications"). For this reason, you should review and test your code with regard to years in different centuries.

Examples of The RR Date Format

The RR date format element of the TO_DATE and TO_CHAR functions allows a database site to default the century to different values depending on the two-digit year, so that years 50 to 99 default to 19xx and years 00 to 49 default to 20xx. Therefore, regardless of the current century at the time the data is entered, the RR format will ensure that the year stored in the database is as follows:

The RR date format is available for inserting and updating DATE data in the database. It is not required for retrieval or query of data already stored in the database as Oracle Database has always stored the YEAR component of a date in its four-digit form.

Here is an example of the RR usage:

INSERT INTO employees (employee_id, department_id, hire_date) VALUES 
   (9999, 20, TO_DATE('01-jan-03', 'DD-MON-RR'));

 INSERT INTO employees (employee_id, department_id, hire_date) VALUES
    (8888, 20, TO_DATE('01-jan-67',  'DD-MON-RR'));

SELECT employee_id, department_id,
   TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date
FROM employees;


Examples of The CC Date Format

The CC date format element of the TO_CHAR function returns the century of a given date. For example:

SELECT TO_CHAR(TO_DATE('01-JAN-2000','DD-MON-YYYY'),'CC') CENTURY FROM DUAL;

CENTURY
-------
20

SELECT TO_CHAR(TO_DATE('01-JAN-2001','DD-MON-YYYY'),'CC') CENTURY FROM DUAL;

CENTURY
-------
21

The CC date format element of the TO_CHAR function sets the century value to one greater than the first two digits of a four-digit year (for example, 20 from 1900). For years that are a multiple of 100, this is not the true century. Strictly speaking, the century of year 1900 is not the twentieth century (which began in 1901) but rather the nineteenth century.

The following workaround computes the correct century for any Common Era (CE, formerly known as AD) date. If Hiredate is a CE date for which you want the true century, use the following expression:

SELECT DECODE (TO_CHAR (Hiredate, 'YY'),
 '00', TO_CHAR (Hiredate - 366, 'CC'), 
 TO_CHAR (Hiredate, 'CC'))  FROM Emp_tab;

This expression works as follows: Get the last two digits of the year. If these are 00, then this is a year in which the Oracle Database century is one year too large, so compute a date in the preceding year (whose Oracle Database century is the desired true century). Otherwise, use the Oracle Database century.

See Also:

Oracle Database SQL Reference for more information about date format codes

Storing Dates in Character Datatypes

Where applications store date values in CHAR or VARCHAR2 datatypes, and the century information is not maintained. You will need to modify the application to include routines to ensure that such dates are treated appropriately when affected by the change in century. You can do this by changing the strings to maintain century information or, with certain constraints, by using the RR date format when interpreting the string as a date.

If you are creating a new application, or if you are modifying an application to ensure that dates stored as character strings are Year 2000 compliant, convert character datatype dates to the DATE datatype. If this is not feasible, store the dates in a form that is language- and format-independent, and that handles full years. For example, use SYYYY/MM/DD plus the time element as HH24:MI:SS if necessary. Note that dates stored in this form must be converted to the correct external format whenever they are received or displayed.

The format SYYYY/MM/DD HH24:MI:SS has the following advantages:

The S format mask prefixes BC dates with "-".

Viewing Date Settings

The following views let you verify what your date settings are:

To see the available values for time zone region and time zone abbreviation, you can query the view V$TIMEZONE_NAMES.

A format mask is a character that describes the format of DATE or NUMBER data stored in a character string. You may use the format model as an argument of the TO_CHAR or TO_DATE function for one of the following:

Altering Date Settings

You may set the date format in your environment or the default date format for the entire database. If you set the format in your environment, it will override any initialization settings.

Change the NLS_DATE_FORMAT parameter settings in the following order:

  1. Set the Client side, such as the Windows NT registry and Unix environment variables.
  2. Set the session using ALTER SESSION SET NLS_DATE_FORMAT. To change the date format for the session, issue the following SQL command:
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR'
    
    
  3. Set the Server using the NLS_DATE_FORMAT parameter in your initialization file, init.ora. To change the default date format for the entire database, edit file init.ora to include the following
    NLS_DATE_FORMAT = DD-MON-RR
    
    

The NLS_DATE_FORMAT setting relies on this order. For a client/server application, NLS_DATE_FORMAT must be set on both the server and the client.


Caution:

Changing this parameter at the database level will change all existing date fields, as described. Make changes at the session level, unless all users and all currently running applications process dates in the range 1950-2049.


Troubleshooting Y2K Problems in Applications

In this section we describe some common programming problems around Y2K compliance. These problems may seem to derive from incorrect Year 2000 processing by the database engine, but on closer inspection they are seen to arise from incorrect use of Oracle Database technology.

Y2K Example: Date Columns Too Short

Your application may have defined the year of a date using a column of CHAR(2) or NUMBER(2) in order to save disk space. This can lead to unpredictable results when 20xx dates are mixed with 19xx dates. To resolve this, modify your application to use the full 4-digit year.

Y2K Example: 4-Digit Years Mixed with 2-Digit Years

Your application may be designed to store a 4-digit year, but the code may allow for the incorrect storage of 2-digit year rows with the 4-digit year rows. This will lead to unpredictable results for queries by date if the date columns contains dates earlier than 1900. To deal with this problem, have your application check for rows that contain dates earlier than 1900, and then adjust for this.

Y2K Example: Wide Range of Years Stored as Two Digits

Examine your applications to determine if it processes dates prior to 1950 or later than 2049, and stores the year as only two digits. If both conditions are met, your application should not use the RR format, but should instead expand the 2-digit year YY into a 4-digit year YYYY, and store the 4-digit year in the database.

Y2K Example: Handling Feb. 29, 2000

The following unusual error helps illuminate the interaction between NLS_DATE_FORMAT and the Oracle Database RR format mask. The following is a syntactically correct statement, but it contains a logical flaw:

SELECT TO_CHAR(TO_DATE(LAST_DAY('01-FEB-00'),'DD-MON-RR'),'MM/DD/RRRR') 
FROM DUAL;

This query returns 02/28/2000. This is consistent with the defined behavior of the RR format mask, but it is incorrect because the year 2000 is a leap year.

The problem is that the operation is using the default NLS_DATE_FORMAT, which is DD-MON-YY. If the NLS_DATE_FORMAT is changed to DD-MON-RR, then the same select returns 02/29/2000, which is the correct value.

Let us evaluate the query asOracle Database does. The first function processed is the innermost function, LAST_DAY. Because NLS_DATE_FORMAT is YY, this correctly returns 2/28, because it is using the year 1900 to evaluate the expression. The value 2/28 is then returned to the next outer function. So, the TO_DATE and TO_CHAR functions format the value 02/28/00 using the RR and RRRR format masks, and display the result as 02/28/2000.

If SELECT LAST_DAY('01-FEB-00') FROM DUAL is issued, the result changes depending on the NLS_DATE_FORMAT. With YY, the LAST_DAY returned is 28-Feb-00 because the year is interpreted as 1900. With RR, the LAST_DAY returned is 29-Feb-00 because the year is interpreted as 2000. The year 1900 is not a leap year, but the year 2000 is.

Y2K Example: Implicit Date Conversion within DECODE

If you use the DECODE function with a third argument that is NULL or of datatype datatype CHAR or VARCHAR2, Oracle Database converts the return value to datatype VARCHAR2. Therefore, the following statement inserts date 31.12.1900:

INSERT INTO destination_table (date_column) 
    SELECT DECODE('31.12.2000', '00000000', NULL, 
        TO_DATE('31.12.2000','DD.MM.YYYY')) 
    FROM DUAL; 

This statement inserts date 04.10.1901:

INSERT INTO destination_table (date_column) 
    SELECT DECODE('01.11.1999', '00000000', NULL, sysdate+1000) 
FROM DUAL; 

In these examples, the third argument in the DECODE argument list is a NULL value, so Oracle Database implicitly converted the DATE value to a VARCHAR2 string using the default format mask. This is DD-MON-YY, which drops the first two digits of the year.


Note:

When inserting the record into a table, Oracle Database implicitly converts the string into a date, using the last two digits of the current year. To ensure the correct year is interpreted, set NLS_DATE_FORMAT using RR or YYYY.


Y2K Example: Partitioning Tables Based on DATE Columns

When you create a partitioned table using a DATE datatype column in the partition key, use a 4-digit year to specify date ranges. For example:

CREATE TABLE stock_xactions (stock_symbol CHAR(5),
    stock_series CHAR(1), 
    num_shares NUMBER(10), 
    price NUMBER(5,2), 
    trade_date DATE) 
    STORAGE (INITIAL 100K NEXT 50K) LOGGING 
    PARTITION BY RANGE (trade_date) 
      (PARTITION sx1992 VALUES LESS THAN (TO_DATE('01-JAN-1993','DD-MON-YYYY')) 
    TABLESPACE ts0 
        NOLOGGING, 
        PARTITION sx1993 VALUES LESS THAN (TO_DATE('01-JAN-1994','DD-MON-YYYY')) 
    TABLESPACE ts1, 
      PARTITION sx1994 VALUES LESS THAN (TO_DATE('01-JAN-1995','DD-MON-YYYY')) 
    TABLESPACE ts2);

Y2K Example: Views Defined Using 2-Digit Years

Oracle Database views depend on the session state. In particular, a predicate with a 2-digit year is allowed in a view. For example:

WHERE col > '12-MAY-99'

Interpretation of the full 4-digit year depends on the setting of NLS_DATE_FORMAT.

Representing Conditional Expressions as Data

The Oracle Expression Filter feature lets you store conditional expressions as data in the database. The Expression Filter provides a mechanism that you use to place a constraint on a VARCHAR2 column to ensure that the values stored are valid SQL WHERE clause expressions. This mechanism also identifies the set of attributes that can be referenced in the conditional expressions.

For example, suppose each row of a table Traders holds data for a stock trading account holder. You can define a column that stores information about stocks each trader is interested in as a conditional expression. To do so, you use the following PL/SQL commands to create an attribute set Ticker with a list of required elementary attributes for the trading symbol, limit price, and amount of change in the stock price:

CREATE OR REPLACE TYPE Ticker AS OBJECT 
   (Symbol VARCHAR2(20), Price NUMBER, Change NUMBER);

BEGIN 
  dbms_expfil.create_attribute_set(attr_set  => 'Ticker',
                                   from_type => 'YES');
END;

Next, you associate the attribute set with the expression set stored in the database column TRADER.INTEREST as follows:

BEGIN
  dbms_expfil.assign_attribute_set (attr_set => 'Ticker',
                                    expr_tab => 'Traders',
                                    expr_col => 'Interest');
END;

This places a constraint on the INTEREST column that ensures the column stores valid conditional expressions. You can then populate the table with trader names, email addresses and conditional expressions that represents a stock the trader is interested in at a particular price:

INSERT INTO Traders (Name, Email, Interest) 
  VALUES ('Scott', 'scott@abc.com', 'SYMBOL = ''ABC'' and PRICE > 25'); 

At this point, you can use the EVALUATE operator to identify the conditional expressions that evaluate to TRUE for a given data item. For example, the following query can be issued to return all the traders who are interested in a given stock quote (Symbol='ABC', Price=31, Change=5.2):

SELECT Name, Email FROM Traders
  WHERE EVALUATE (Interest,
                  'Symbol=>''ABC'', Price=>31, Change=>5.2') = 1;

To speed up a query like this one, you can optionally create an Oracle Expression Filter index on the INTEREST column.

See Also:

Oracle Database Application Developer's Guide - Expression Filter for details on Oracle Expression Filter

Representing Geographic Coordinate Data

To represent Geographic Information System (GIS) or spatial data in the database, you can use Oracle Spatial features, including the type MDSYS.SDO_GEOMETRY. You can store the data in the database using either an object-relational or a relational model, and manipulate and query the data using a set of PL/SQL packages.

For more information, see Oracle Spatial User's Guide and Reference.

Representing Image, Audio, and Video Data

Whether you store such multimedia data inside the database as BLOB or BFILE values, or store it externally on a Web server or other kind of server, you can use interMedia to access the data using either an object-relational or a relational model, and manipulate and query the data using a set of object types.

For more information, see Oracle interMedia Reference.

Representing Searchable Text Data

Rather than writing low-level code to do full-text searches, you can use Oracle9i Text, formerly known as ConText and interMedia Text. It stores the search data in a special kind of index, and lets you query the data with operators and PL/SQL packages. This makes it simple to create your own search engine using data from tables, files, or URLs, and combine the search logic with relational queries. You can also search XML data this way, using XPath notation.

For more information, see Oracle Text Application Developer's Guide.

Representing Large Amounts of Data

The database provides several datatypes for representing large amounts of data. These datatypes are grouped under the general category of Large Objects (LOBs); they are described in Table 2-6:

Table 2-6   Large Object Datatypes
Datatype Description

BLOB

Binary Large Object
Suitable for representing large amounts of binary data such as images, video, or other multimedia data.

CLOB

Character Large Object
Suitable for representing large amounts of character data. CLOB types are stored using the database character set.

NCLOB

National Character Set Large Object
Suitable for representing large amounts of character data in National Character Set format.

BFILE

Datatype for storing Large Objects in the operating system's file system, outside of the database files or tablespace. Note that the BFILE type is read-only; other LOB types are read/write. BFILE objects are also sometimes referred to as external LOBs.

An instance of type BLOB, CLOB, or NCLOB can exist as either a persistent LOB instance or a temporary LOB instance. Persistent and temporary instances differ as follows:

With the exception of declaring, freeing, creating, and committing, operations on persistent and temporary LOB instances are performed the same way.

For more details on using LOBs in applications, see the Oracle Database Application Developer's Guide - Large Objects.


Note:

In earlier releases, the LONG, RAW, and LONG RAW datatypes were typically used to store large amounts of data. Use of these types is no longer recommended for new development. If your application still uses these types, migrate your application to use LOB types. See the Oracle Database Application Developer's Guide - Large Objects


Using RAW and LONG RAW Datatypes


Note:

Oracle recommends that you convert LONG RAW columns to binary LOB (BLOB) columns and convert LONG columns to character LOB (CLOB or NCLOB) columns. LOB columns are subject to far fewer restrictions than LONG and LONG RAW columns.


See Also:

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

Oracle Net and the Export and Import utilities do not perform character conversion when transmitting RAW or LONG RAW data. When Oracle Database automatically converts RAW or LONG RAW data to and from CHAR data (as is the case when entering RAW data as a literal in an INSERT statement), the data is represented as one hexadecimal character representing the bit pattern for every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as CB.

LONG RAW data cannot be indexed, but RAW data can be indexed.

See Also:

Oracle Database SQL Reference for restrictions on LONG and LONG RAW datatypes

Addressing Rows Directly with the ROWID Datatype

Every row in an Oracle Database table is assigned a ROWID that corresponds to the physical address of a row. If the row is too large to fit within a single data block, the ROWID identifies the initial row piece. Although ROWIDs are usually unique, different rows can have the same ROWID if they are in the same data block but in different clustered tables.

Each table in Oracle Database has a pseudocolumn named ROWID.

See Also:

Oracle Database Concepts for general information about the ROWID pseudocolumn and the ROWID datatype

Extended ROWID Format

Oracle Database uses an extended ROWID format, which supports features such as table partitions, index partitions, and clusters.

The extended ROWID includes the following information:

The data object identifier is an identification number that Oracle Database assigns to schema objects, such as nonpartitioned tables or partitions. For example:

SELECT DATA_OBJECT_ID FROM ALL_OBJECTS
       WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMP_TAB';

This query returns the data object identifier for the EMP_TAB table in the SCOTT schema.

See Also:

PL/SQL Packages and Types Reference for information about using the DBMS_ROWID package functions to get the data object identifier in other ways

Different Forms of the ROWID

Oracle Database documentation uses the term ROWID in different ways, depending on context.

ROWID Pseudocolumn

Each table and nonjoined view has a pseudocolumn called ROWID. For example:

CREATE TABLE T_tab (col1 Rowid);
INSERT INTO T_tab SELECT Rowid FROM Emp_tab WHERE Empno = 7499;

This command returns the ROWID pseudocolumn of the row of the EMP_TAB table that satisfies the query, and inserts it into the T1 table.

Internal ROWID

The internal ROWID is an internal structure that holds information that the server code needs to access a row. The restricted internal ROWID is 6 bytes on most platforms; the extended ROWID is 10 bytes on these platforms.

External Character ROWID

The extended ROWID pseudocolumn is returned to the client in the form of an 18-character string (for example, "AAAA8mAALAAAAQkAAA"), which represents a base 64 encoding of the components of the extended ROWID in a four-piece format, OOOOOOFFFBBBBBBRRR:

There is no need to decode the external ROWID; you can use the functions in the DBMS_ROWID package to obtain the individual components of the extended ROWID.

See Also:

PL/SQL Packages and Types Reference for information about the DBMS_ROWID package

The restricted ROWID pseudocolumn is returned to the client in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.

External Binary ROWID

Some client applications use a binary form of the ROWID. For example, OCI and some precompiler applications can map the ROWID to a 3GL structure on bind or define calls. The size of the binary ROWID is the same for extended and restricted ROWIDs. The information for the extended ROWID is included in an unused field of the restricted ROWID structure.

The format of the extended binary ROWID, expressed as a C struct, is:

struct riddef {
    ub4    ridobjnum; /* data obj#--this field is 
                      unused in restricted ROWIDs */
    ub2    ridfilenum;
    ub1    filler;
    ub4    ridblocknum;
    ub2    ridslotnum;
}

ROWID Migration and Compatibility Issues

For backward compatibility, the restricted form of the ROWID is still supported. These ROWIDs exist in Oracle Database version 7 data, and the extended form of the ROWID is required only in global indexes on partitioned tables. New tables always get extended ROWIDs.

See Also:

Oracle Database Administrator's Guide

It is possible for a client of Oracle Database version 7 to access a more recent database, and vice versa. A client in this sense could be a remote database accessing a server using database links, or a client 3GL or 4GL application accessing a server.

See Also:

PL/SQL Packages and Types Reference and Oracle Database Upgrade Guide for more information on the ROWID_TO_EXTENDED function

Accessing Oracle Database Version 7 from an Oracle9i Client

The ROWID values that are returned are always restricted ROWIDs. Also, Oracle9i returns restricted ROWID values to a database server for Oracle Database version 7.

The following ROWID functionality works when accessing a server for Oracle Database version 7:

Accessing an Oracle9i Database from a Client of Oracle Database Version 7

Oracle9i returns ROWIDs in the extended format. This means that you can only:

Import and Export

It is not possible for a client of Oracle Database version 7 to import a table from a later version that has a ROWID column (not the ROWID pseudocolumn), if any row of the table contains an extended ROWID value.

ANSI/ISO, DB2, and SQL/DS Datatypes

You can define columns of tables in Oracle Database using ANSI/ISO, DB2, and SQL/DS datatypes. Oracle Database internally converts such datatypes to Oracle datatypes.

The ANSI datatype conversions are shown in Table 2-7. The ANSI/ISO datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s defaults to 0.

Table 2-7   ANSI Datatype Conversions to Oracle Datatypes
ANSI SQL Datatype Oracle Datatype

CHARACTER (n), CHAR (n)

CHAR (n)

NUMERIC (p,s), DECIMAL (p,s), DEC (p,s)

NUMBER (p,s)

INTEGER, INT, SMALLINT

NUMBER (38)

FLOAT (p)

FLOAT (p)

REAL

FLOAT (63)

DOUBLE PRECISION

FLOAT (126)

CHARACTER VARYING(n), CHAR VARYING(n)

VARCHAR2 (n)

TIMESTAMP

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

Table 2-8 shows the DB2 and SQL/DS conversions.

Table 2-8   SQL/DS, DB2 Datatype Conversions to Oracle Datatypes
DB2 or SQL/DS Datatype Oracle Datatype

CHARACTER (n)

CHAR (n)

VARCHAR (n)

VARCHAR2 (n)

LONG VARCHAR

LONG

DECIMAL (p,s)

NUMBER (p,s)

INTEGER, SMALLINT

NUMBER (38)

FLOAT (p)

FLOAT (p)

DATE

DATE

TIMESTAMP

TIMESTAMP

The datatypes TIME, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC of IBM products SQL/DS and DB2 have no corresponding Oracle datatype, and they cannot be used.

How Oracle Database Converts Datatypes

In some cases, Oracle Database allows data of one datatype where it expects data of a different datatype. Generally, an expression cannot contain values with different datatypes. However, Oracle Database can use the following functions to automatically convert data to the expected datatype:

Implicit datatype conversions work according to the rules explained in "Datatype Conversion During Assignments".

See Also:

Oracle Database SQL Reference for details about datatype conversion

Datatype Conversion During Assignments

For assignments, Oracle Database can automatically convert the following:

The assignment succeeds if Oracle Database can convert the datatype of the value used in the assignment to that of the assignment target.

For the examples in the following list, assume a package with a public variable and a table declared as in the following statements:

CREATE PACKAGE Test_Pack AS var1 CHAR(5); END;
CREATE TABLE Table1_tab (col1 NUMBER); 

Datatype Conversion During Expression Evaluation

For expression evaluation, Oracle Database can automatically perform the same conversions as for assignments. An expression is converted to a type based on its context. For example, operands to arithmetic operators are converted to NUMBER, and operands to string functions are converted to VARCHAR2.

Oracle Database can automatically convert the following:

Character to NUMBER conversions succeed only if the character string represents a valid number. Character to DATE conversions succeed only if the character string satisfies the session default format, which is specified by the initialization parameter NLS_DATE_FORMAT.

Some common types of expressions follow:

In general, Oracle Database uses the rule for expression evaluation when a datatype conversion is needed in places not covered by the rule for assignment conversions.

In assignments of the form:

variable := expression

Oracle Database first evaluates expression using the conversion rules for expressions; expression can be as simple or complex as desired. If it succeeds, then the evaluation of expression results in a single value and datatype. Then, Oracle Database tries to assign this value to the target variable using the conversion rules for assignments.

Representing Dynamically Typed Data

You might be familiar with features in some languages that allow datatypes to change at runtime, or let a program check the type of a variable. For example, C has the union keyword and the void * pointer, and Java has the typeof operator and wrapper types such as Number. Oracle9i includes features that let you create variables and columns that can hold data of any type, and test such data values to see their underlying representation. Using these features, a single table column can represent a numeric value in one row, a string value in another row, and an object in another row.

You can use the built-in type SYS.ANYDATA to represent values of any scalar or object type. This type is an object type with methods to bring in a scalar value of any type, and turn the value back into a scalar or object.

In the same way, you can use the built-in type SYS.ANYDATASET to represent values of any collection type.

To manipulate and check type information, you can use the built-in type SYS.ANYTYPE in combination with the DBMS_TYPES package. For example, the following program represents data of different underlying types in a table, then interprets the underlying type of each row and processes each value appropriately:

-- This example defines and executes a PL/SQL procedure that 
-- uses methods built into SYS.ANYDATA to access information about 
-- data stored in a SYS.ANYDATA table column.

DROP TYPE Employee FORCE;
DROP TABLE mytab;
CREATE OR REPLACE TYPE Employee AS OBJECT ( empno NUMBER, 
  ename VARCHAR2(10) ); 
/ 
CREATE TABLE mytab ( id NUMBER, data SYS.ANYDATA ); 

INSERT INTO mytab VALUES (1, SYS.ANYDATA.ConvertNumber(5)); 
INSERT INTO mytab VALUES (2, SYS.ANYDATA.ConvertObject(Employee(5555, 'john'))); 
commit; 

CREATE OR REPLACE procedure P IS 
  CURSOR cur IS SELECT id, data FROM mytab; 
  v_id           mytab.id%TYPE; 
  v_data         mytab.data%TYPE; 
  v_type         SYS.ANYTYPE; 
  v_typecode     PLS_INTEGER; 
  v_typename     VARCHAR2(60);
  v_dummy        PLS_INTEGER; 
  v_n            NUMBER;
  v_employee     Employee; 
  non_null_anytype_for_NUMBER exception; 
  unknown_typename            exception; 
BEGIN 
  OPEN cur; 
  LOOP 
    FETCH cur INTO v_id, v_data; 
    EXIT WHEN cur%NOTFOUND; 

/* The typecode is a number that signifies what type is represented by v_data.
   GetType also produces a value of type SYS.AnyType with methods you can call
   to find precision and scale of a number, length of a string, and so on. */
    v_typecode := v_data.GetType ( v_type /* OUT */ ); 

/* Now we compare the typecode against constants from DBMS_TYPES to see what
   kind of data we have, and decide how to display it. */
    CASE v_typecode 

      WHEN Dbms_Types.Typecode_NUMBER THEN 
        IF v_type IS NOT NULL 
-- This condition should never happen, but we check just in case.
          THEN RAISE non_null_anytype_for_NUMBER; END IF; 
-- For each type, there is a Get method.
        v_dummy := v_data.GetNUMBER ( v_n /* OUT */ ); 
        Dbms_Output.Put_Line ( 
          To_Char(v_id) || ': NUMBER = ' || To_Char(v_n) ); 

      WHEN Dbms_Types.Typecode_Object THEN 
        v_typename := v_data.GetTypeName(); 
-- An object type's name is qualified with the schema name.
        IF v_typename NOT IN ( 'SCOTT.EMPLOYEE' ) 
-- If we encounter any object type besides EMPLOYEE, raise an exception.
          THEN RAISE unknown_typename; END IF; 
        v_dummy := v_data.GetObject ( v_employee /* OUT */  ); 
        Dbms_Output.Put_Line ( 
          To_Char(v_id) || ': user-defined type = ' || v_typename || 
            ' ( ' || v_employee.empno || ', ' || v_employee.ename || ' )' ); 
    END CASE; 
  END LOOP; 
  CLOSE cur; 
EXCEPTION 
  WHEN non_null_anytype_for_NUMBER THEN 
    RAISE_Application_Error ( -20000, 
      'Paradox: the return AnyType instance FROM GetType ' || 
      'should be NULL for all but user-defined types' ); 
  WHEN unknown_typename THEN 
    RAISE_Application_Error ( -20000, 'Unknown user-defined type ' ||
      v_typename || ' - program written to handle only SCOTT.EMPLOYEE' ); 
END; 
/
-- The query and the procedure P in the preceding code sample 
-- produce output like the following:

SQL> SELECT t.data.gettypename() FROM mytab t;
 
T.DATA.GETTYPENAME()
--------------------------------------------------------------------------------
SYS.NUMBER
SCOTT.EMPLOYEE
 
SQL> EXEC P;
1: NUMBER = 5
2: user-defined type = SCOTT.EMPLOYEE ( 5555, john )


You can access the same features through the OCI interface, using the OCIType, OCIAnyData, and OCIAnyDataSet interfaces.

See Also:

PL/SQL Packages and Types Reference for details about the DBMS_TYPES package

Oracle Database Application Developer's Guide - Object-Relational Features for information and examples using the ANYDATA, ANYDATASET, and ANYTYPE types

Oracle Call Interface Programmer's Guide for details about the OCI interfaces

Representing XML Data

If you have information stored as files in XML format, or if you want to take an object type and store it as XML, you can use the XMLType built-in type.

XMLType columns store their data as CLOBs. You can take an existing CLOB, VARCHAR2, or any object type, and call the XMLType constructor to turn it into an XML object.

Once an XML object is inside the database, you can use queries to traverse it (using the XML XPath notation) and extract all or part of its data.

You can also produce XML output from existing relational data, and split XML documents across relational tables and columns. You can use the DBMS_XMLQUERY, DBMS_XMLGEN, and DBMS_XMLSAVE packages, and the SYS_XMLGEN and SYS_XMLAGG functions to transfer XML data into and out of relational tables.

See Also: