Data Types: Usage Information

The topic discusses the data types used with Oracle Database. It contains the following topics:


See Also:

  • Oracle Data Types for more information about data types, especially the options when you create columns in a table

  • Oracle Database Concepts to learn about Oracle built-in data types


Overview of Data Types

A data type 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 data type differently from values of another data type. For example, Oracle Database can use the addition operator on values of NUMBER data type, but not with values of some other data types. The data types supported by Oracle Database include characters, numbers, and dates and times (known as datetime data types).

To view the data types contained in a database table, such as the employees table, you can use Connections navigator or DESCRIBE in SQL*Plus (see SQL*Plus DESCRIBE Command).

Storing Character Data

This topic contains the following topics:

What are the Character Data Types?

You can use the following SQL data types to store alphanumeric data:

  • VARCHAR2 and NVARCHAR2 data types store variable-length character literals.

  • NCHAR and NVARCHAR2 data types store variable-length Unicode character data only.

  • CHAR and NCHAR data types store fixed-length character literals.

Choosing Between the Character Data Types

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

  • Space usage

    To store data more efficiently, use the VARCHAR2 data type. The CHAR data type blank-pads and stores trailing blanks up to a fixed column length for all column values, whereas the VARCHAR2 data type does not add extra blanks.

  • Comparison semantics

    Use the CHAR data type when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the VARCHAR2 when trailing blanks are important in string comparisons.

  • Future compatibility

    The CHAR and VARCHAR2 data types are fully supported.

Storing Numeric Data

This topic contains the following topics:

What Are the Numeric Data Types?

The following SQL data types store numeric data:

  • NUMBER

  • BINARY_FLOAT

  • BINARY_DOUBLE

Use the NUMBER data type to store integers and real numbers in a fixed-point or floating-point format. Numbers using this data type are guaranteed to be portable among different Oracle Database platforms. For nearly all cases where you need to store numeric data, you would use the NUMBER data type.

Oracle Database provides the numeric BINARY_FLOAT and BINARY_DOUBLE data types exclusively for floating-point numbers. They support all of the basic functionality provided by the NUMBER data type. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.

Using NUMBER Data Types

The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.

When you specify a fixed-point number use the following form to specify the precision and scale of the number:

NUMBER(precision, scale)

Precision and scale are defined as follows:

  • Precision is the total number of significant decimal digits, where the most significant digit is the leftmost nonzero digit, and the least significant digit is the rightmost known digit. For examples, see Table: Storage of Scale and Precision.

  • Scale is the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127. For examples, see Table: Storage of Scale and Precision.

    • Positive scale is the number of significant digits to the right of the decimal point to and including the least significant digit.

    • Negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For negative scale the least significant digit is on the left side of the decimal point, because 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 can be greater than precision, most commonly when e notation is used. When scale is greater than precision, the precision specifies the maximum number of significant digits to the right of the decimal point. 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.

It is good practice to 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.

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.

Table: Storage of Scale and Precision show how Oracle stores data using different values for precision and scale.

Storage of Scale and Precision

Actual Data Specified As Stored As

123.89

NUMBER

123.89

123.89

NUMBER(3)

124

123.89

NUMBER(6,2)

123.89

123.89

NUMBER(6,1)

123.9

123.89

NUMBER(3)

exceeds precision

123.89

NUMBER(4,2)

exceeds precision

123.89

NUMBER(6,-2)

100

.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

1.2e-4

NUMBER(2,5)

0.00012

1.2e-5

NUMBER(2,5)

0.00001


Using Floating-Point Number Formats

The BINARY_FLOAT and BINARY_DOUBLE data types 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 data type, 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.

The floating-point number system is a common way of representing and manipulating numeric values in computer systems. The value 4.32682E-21F is an example of a BINARY_FLOAT data type.

Floating-point numbers 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.

Binary floating-point numbers differ from NUMBER in the way the values are stored internally by Oracle Database. Values are stored using decimal precision for NUMBER. All literals that are within the range and precision supported by NUMBER are stored exactly as NUMBER. Literals are stored exactly because literals are expressed using decimal precision (the digits 0 through 9). Binary floating-point numbers are stored using binary precision (the digits 0 and 1). Such a storage scheme cannot represent all values using decimal precision exactly. Frequently, the error that occurs when converting a value from decimal to binary precision is undone when the value is converted back from binary to decimal precision. The literal 0.1 is such an example.

BINARY_FLOAT

BINARY_FLOAT is a 32-bit, single-precision floating-point number data type. Each BINARY_FLOAT value requires 5 bytes, including a length byte.

BINARY_DOUBLE

BINARY_DOUBLE is a 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 9 bytes, including a length byte.

In a NUMBER column, floating point numbers have decimal precision. In a BINARY_FLOAT or BINARY_DOUBLE column, floating-point numbers have binary precision. The binary floating-point numbers support the special values infinity and NaN (not a number).

You can specify floating-point numbers within the limits listed in Table: Floating Point Number Limits.

Floating Point Number Limits

Value Binary-Float Binary-Double

Maximum positive finite value

3.40282E+38F

1.79769313486231E+308

Minimum positive finite value

1.17549E-38F

2.22507485850720E-308


Storing Datetime Data

This topic contains the following topics:

Using DATE and TIMESTAMP Data Types

Oracle Database supports the following datetime data types:

  • DATE

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE


See Also:

Oracle Database SQL Language Reference for information on DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types

Using the DATE Data Type

Use the DATE data type to store point-in-time values (dates and times) in a table. An application that specifies the time for a job might use the DATE data type.

The DATE data type stores the century, year, month, day, hours, minutes, and seconds. The valid date range is from January 1, 4712 BC to December 31, 9999 AD.

Using the TIMESTAMP Data Type

Use the TIMESTAMP data type to store values that are precise to fractional seconds. An application that must decide which of two events occurred first might use TIMESTAMP.

Using the TIMESTAMP WITH TIME ZONE Data Type

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.

Using the TIMESTAMP WITH LOCAL TIME ZONE Data Type

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 in which you want to display dates and times that use the time zone of the client system. It is generally inappropriate in three-tier applications 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.

Representing the Difference Between Datetime Values

Use the INTERVAL DAY TO SECOND data type 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 with high precision, you can use a large value for the days portion.

Use the INTERVAL YEAR TO MONTH data type 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 stores dates in its own internal format which is fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

Manipulating the DATE and TIME Formats

For input and output of dates, the standard Oracle Database default date format is DD-MON-RR. The RR datetime format element enables you store 20th century dates in the 21st century by specifying only the last two digits of the year.

Time is stored in a 24-hour format as 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.

You can change the current default date or time format for a specific date or timestamp with the use the TO_DATE or TO_TIMESTAMP function with a format mask, such as:


TO_DATE('27-OCT-98', 'DD-MON-RR')
TO_DATE('15-NOV-05 10:56 A.M.','DD-MON-YY HH:MI A.M.')
TO_TIMESTAMP ('10-Sep-05 14:10:10.123000',
              'DD-Mon-RR HH24:MI:SS.FF')

Oracle Database provides various functions for calculating and converting datetime data. For examples in SQL statements, see Using Date Functions.

Be careful when using a date format such as 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 format mask such as the default RR.

You can use the following techniques to change the default date format on a more global level:

  • To change on an instance-wide basis, use the NLS_DATE_FORMAT parameter.

  • To change during a session, use the ALTER SESSION statement.

For more information on these techniques, see Working in a Global Environment.


See Also:

  • Oracle Database SQL Language Reference for more information about date and time formats

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