Home > Oracle Database Usage Infor... > Data Types: Usage Information
The topic discusses the data types used with Oracle Database. It contains the following topics:
See Also:
|
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).
This topic contains the following topics:
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.
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.
This topic contains the following topics:
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.
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 |
|
123.89 |
123.89 |
|
124 |
123.89 |
|
123.89 |
123.89 |
|
123.9 |
123.89 |
|
exceeds precision |
123.89 |
|
exceeds precision |
123.89 |
|
100 |
.01234 |
|
.01234 |
.00012 |
|
.00012 |
.000127 |
|
.00013 |
.0000012 |
|
.0000012 |
.00000123 |
|
.0000012 |
1.2e-4 |
|
0.00012 |
1.2e-5 |
|
0.00001 |
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
is a 32-bit, single-precision floating-point number data type. Each BINARY_FLOAT
value requires 5 bytes, including a length byte.
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.
This topic contains the following topics:
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 onDATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , and TIMESTAMP WITH LOCAL TIME ZONE data types |
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.
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
.
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 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.
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.
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:
|