Oracle Data Types

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

Data type

A data type specifies a particular type of data, such as integer, floating-point, Boolean etc.
A data type also specifies the possible values for that type, the operations that can be performed on that type and the way the values of that type are stored.

Oracle data types

Each value which is manipulated by Oracle Database has a data type. The data type of a value associates a fixed set of properties with the value. Using these properties Oracle treats values of one data type differently from values of another. For example, you can add values of NUMBER data type, but not values of CHAR data type.

Oracle Database provides a number of built-in data types as well as several categories for user-defined types that can be used as data types.

Oracle Built-in Data Types

Following table summarizes Oracle built-in data types.

Types	Description	Size
VARCHAR2(size [BYTE \| CHAR])	Variable-length character string.	From 1 byte to 4KB.
NVARCHAR2(size)	Variable-length Unicode 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.
NUMBER [ (p [, s]) ]	Number having precision p and scale s. Range of p : From 1 to 38. Ranges of s : From -84 to 127. Both precision and scale are in decimal digits.	A NUMBER value requires from 1 to 22 bytes.
FLOAT [(p)]	A FLOAT value is represented internally as NUMBER. Range of p : From 1 to 126 binary digits.	A FLOAT value requires from 1 to 22 bytes.
LONG	Character data of variable length up to 2 gigabytes, used for backward compatibility.	2³¹ -1 bytes
DATE	Valid date range : From January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter.	The size is fixed at 7 bytes.
BINARY_FLOAT	32-bit floating point number.	This data type requires 4 bytes.
BINARY_DOUBLE	64-bit floating point number.	This data type requires 8 bytes.
TIMESTAMP [(fractional_seconds_precision)]	This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.	The size is 7 or 11 bytes, depending on the precision.
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.	The size is fixed at 13 bytes.
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.	The size is fixed at 5 bytes.
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.	The size is fixed at 11 bytes.
RAW(size)	Raw binary data of length size bytes.	Maximum size is 2000 bytes
LONG RAW	Raw binary data of variable.	Size up to 2 gigabytes.
ROWID	The unique address (base 64 string representing) of a row in its table.
UROWID [(size)]	The logical address of a row (base 64 string representing) of an index-organized table.	The maximum size and default is 4000 bytes.
CHAR [(size [BYTE \| CHAR])]	Fixed-length character data of length size bytes or characters.	Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.
NCHAR[(size)]	Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding.	Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
CLOB	A character large object containing single-byte or multibyte characters.	Maximum size is (4 gigabytes - 1) * (database block size).
NCLOB	A character large object containing Unicode characters.	Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
BLOB	A binary large object.	Maximum size is 4 gigabytes.
BFILE	Contains a locator to a large binary file stored outside the database.	Maximum size is 4 gigabytes.

Oracle Character Data Types

The CHAR data type specifies a fixed-length character string. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length and if the value is too long for the column, then Oracle returns an error. Following data types are used for character data :

Types	Description	Range in characters
NCHAR	The NCHAR data type is a Unicode-only data type. When you create a table with an NCHAR column, you define the column length in characters.	The maximum column size allowed is 2000 bytes.
NVARCHAR2	The NVARCHAR2 data type is a Unicode-only data type. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold.	he maximum column size allowed is : 32767 bytes if MAX_STRING_SIZE = EXTENDED 4000 bytes if MAX_STRING_SIZE = STANDARD
VARCHAR2	The VARCHAR2 data type 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 maximum length of the column.	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.
VARCHAR	Do not use the VARCHAR data type. Use the VARCHAR2 data type instead. Although the VARCHAR data type is currently synonymous with VARCHAR2.

Oracle NUMBER Data Type

NUMBER Data Type:

The NUMBER data type stores zero, positive and negative fixed numbers.

Fixed-point number format:

NUMBER(p,s)

Where p is the precision, of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.
s is the scale, the scale can range from -84 to 127.
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.

Examples:

Actual Data	Format	Stored As
123.79	NUMBER	123.79
123.88	NUMBER(3)	124
123.89	NUMBER(3,2)	exceeds precision
123.89	NUMBER(4,2)	exceeds precision
123.89	NUMBER(5,2)	123.89
123.89	NUMBER(6,1)	123.9
123.89	NUMBER(6,-2)	100
.05678	NUMBER(4,5)	.05678
.00013	NUMBER(4,5)	.00013
.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

FLOAT Data Type:

The FLOAT data type is a subtype of NUMBER. You can can specify it with or without precision. Scale cannot be specified, but is interpreted from the data. Each FLOAT value requires from 1 to 22 bytes.

The following example shows the difference between NUMBER and FLOAT:

SQL> CREATE TABLE test (numr NUMBER(5,2), flott FLOAT(5));
Table created.
SQL> INSERT INTO test VALUES (1.34, 1.34);
1 row created.
SQL> INSERT INTO test VALUES (6.89, 6.89);
1 row created.
SQL> INSERT INTO test VALUES (16.78, 16.78);
1 row created.
SQL> INSERT INTO test VALUES (126.45, 126.45);
1 row created.
SQL> SELECT * FROM test;
 numr       flott
 ---------- ----------
  1.34        1.3
  6.89        6.9
 16.78         17
126.45        130

In the above example, the FLOAT value returned cannot exceed 5 binary digits. Thus 123.45 is rounded to 120, which has only two significant decimal digits, requiring only 4 binary digits.

Floating-Point Numbers:

The term floating point is derived from the fact that there is no fixed number of digits before and after the decimal point; that is, the decimal point can float. An exponent may optionally be used following the number to increase the range, for example, 1.777 e^-20.

Floating Point Syntax

Example:

Floating Point example

In Oracle database there are two numeric data types exclusively for floating-point numbers:

BINARY_FLOAT:
BINARY_FLOAT is a 32-bit, single-precision floating-point number data type. Each BINARY_FLOAT value requires 4 bytes.

BINARY_DOUBLE:
BINARY_DOUBLE is a 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes.

Examples:

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

LONG Data Type
Use LOB columns (CLOB, NCLOB, BLOB) as LONG columns are supported only for backward compatibility.

LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 2³¹-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. LONG literals are formed as described for "Text Literals".

Datetime and Interval Data Types

Following are the datetime data types :

DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE

Values of datetime data types are sometimes called datetimes.

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	-12 to 14 (This range accommodates daylight saving time changes.) Not applicable for DATEor TIMESTAMP.	Not applicable
TIMEZONE_MINUTE	00 to 59. Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_REGION	Query the TZNAME column of the V$TIMEZONE_NAMES data dictionary view. Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_ABBR	Query the TZABBREV column of the V$TIMEZONE_NAMES data dictionary view. Not applicable for DATE or TIMESTAMP.	Not applicable

DATE Data Type:

The DATE data type stores date and time information (represented in both character and number data types). For each DATE value, Oracle stores year, month, day, hour, minute, and second.

Using Julian Days:

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

Default date values:

The year is the current year, as returned by SYSDATE.
The month is the current month, as returned by SYSDATE.
The day is 01 (the first day of the month).
The hour, minute, and second are all 0.

Example:

			
SQL> SELECT TO_DATE('2015', 'YYYY') FROM DUAL;
TO_DATE('
---------
01-JAN-15

TO_DATE function converts a character or numeric value to a date.

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. The following statement returns the Julian equivalent of January 1, 2015 :

 			
SQL> SELECT TO_CHAR(TO_DATE('01-01-2015', 'MM-DD-YYYY'),'J') FROM DUAL;
      TO_CHAR
      -------
      2457024

TIMESTAMP Data Type:
The TIMESTAMP data type is an extension of the DATE data type and stores the year, month, and day of the DATE data type, plus hour, minute, and second values. It is useful for storing precise time values and for collecting and evaluating date information across geographic regions.

Syntax:

TIMESTAMP [(fractional_seconds_precision)]

TIMESTAMP WITH TIME ZONE Data Type :
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. It is useful for preserving local time zone information.

Syntax:

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE Data Type :

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that is sensitive to time zone information. 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 information is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. This data type is useful for date information that is always to be displayed in the time zone of the client system in a two-tier application.

Syntax:

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH Data Type :

INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. This data type is useful for representing the difference between two datetime values when only the year and month values are significant.

Syntax:

INTERVAL YEAR [(year_precision)] TO MONTH

INTERVAL DAY TO SECOND Data Type :

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.

Syntax:

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

where

day_precision is the 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 datetime field. Accepted values are 0 to 9. The default is 6.

ANSI, DB2, and SQL/DS Data Types

Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name and it converts the data type to the equivalent Oracle data type. Following table shows the conversions:,

ANSI SQL Data Type	Oracle Data Type
CHARACTER(n) CHAR(n)	CHAR(n)
CHARACTER VARYING(n) CHAR VARYING(n)	VARCHAR2(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)] (Note 1)	NUMBER(p,s)
INTEGER INT SMALLINT	NUMBER(p,0)
FLOAT (Note 2) DOUBLE PRECISION (Note 3) REAL (Note 4)	FLOAT(126) FLOAT(126) FLOAT(63)

Oracle-Supplied Types

Oracle provides some new data types which are not present in built-in or ANSI-supported types. These types can be implemented in C/C++, Java, or PL/ SQL. Here is the details :

Any Types:
The Any types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These data types 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.

Data Type	Description
ANYTYPE	Contains a type description of any named SQL type or unnamed transient type.
ANYDATA	Contains an instance of a given type, with data, plus a description of the type. ANYDATA can be used as a table column data type 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.
ANYDATASET	Contains a description of a given type plus a set of data instances of that type. ANYDATASET can be used as a procedure parameter data type where such flexibility is needed. The values of the data instances can be of SQL built-in types as well as user-defined types..

XML Types:
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. XMLType is a system-defined type, so you can use it as an argument of a function or as the data type 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, as binary XML (stored internally as a CLOB), or object relationally.

URI Data Types

Oracle supplies a family of URI types—URIType, DBURIType, XDBURIType, and HTTPURIType—which are related by an inheritance hierarchy.

Data Type	Description
HTTPURIType	You can use HTTPURIType to store URLs to external Web pages or to files. Oracle accesses these files using HTTP (Hypertext Transfer 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 (#).
DBURIType	DBURIType can be used to store DBURIRef values, which reference data inside the database. Storing DBURIRef values lets you reference data stored inside or outside the database and access the data consistently.

Spatial Types

Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled applications, geographic information system (GIS) applications, and geoimaging applications. After the spatial data is stored in an Oracle Database, you can easily manipulate, retrieve, and relate it to all the other data stored in the database. The following data types are available only if you have installed Oracle Spatial.

Data Type	Description
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 called geometry tables.
SDO_TOPO_GEOMETRY	This type describes a topology geometry, which is stored in a single row, in a single column of object type SDO_TOPO_GEOMETRY in a user-defined table.
SDO_GEORASTER	In the GeoRaster object-relational model, a raster grid or image object is stored in a single row, in a single column of object type SDO_GEORASTER in a user-defined table. Tables of this sort are called GeoRaster tables.

Media Types

Oracle Multimedia 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 Multimedia data types are created in the ORDSYS schema. Public synonyms exist for all the data types, so you can access them without specifying the schema name.
Oracle Multimedia provides the following object types:

Data Type	Description
ORDAudio	Supports the storage and management of audio data.
ORDDicom	Supports the storage and management of Digital Imaging and Communications in Medicine (DICOM), the format universally recognized as the standard for medical imaging.
ORDDoc	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.
ORDImage	Supports the storage and management of image data.
ORDVideo	Supports the storage and management of video data.
ORDImageSignature	The ORDImageSignature object type has been deprecated and should no longer be introduced into your code. Existing occurrences of this object type will continue to function as in the past.

The ORDImageSignature object type has been deprecated and should no longer be introduced into your code. Existing occurrences of this object type will continue to function as in the past.

Data Type	Description
SI_AverageColor	Represents a feature that characterizes an image by its average color.
SI_Color	Encapsulates color values.
SI_ColorHistogram	Represents a feature that characterizes an image by the relative frequencies of the colors exhibited by samples of the raw image.
SI_FeatureList	A list containing up to four of the image features represented by the preceding object types (SI_AverageColor, SI_ColorHistogram, SI_PositionalColor, and SI_Texture), where each feature is associated with a feature weight.
SI_PositionalColor	Given an image divided into n by m rectangles, the SI_PositionalColor object type represents the feature that characterizes an image by the n by m most significant colors of the rectangles.
SI_StillImage	Represents digital images with inherent image characteristics such as height, width, and format.
SI_Texture	Represents a feature that characterizes an image by the size of repeating items (coarseness), brightness variations (contrast), and predominant direction (directionality).

Previous: Oracle Home
Next: Literals