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.
Following table summarizes Oracle built-in data types.
|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.||231 -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.|
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.|
NUMBER Data Type :
The NUMBER data type stores zero, positive and negative fixed numbers.
Fixed-point number format :
|Actual Data||Format||Stored As|
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.
In Oracle database there are two numeric data types exclusively for floating-point numbers:
BINARY_FLOAT is a 32-bit, single-precision floating-point number data type. Each BINARY_FLOAT value requires 4 bytes.
BINARY_DOUBLE is a 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes.
|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 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer. LONG literals are formed as described for "Text Literals".
Following are the datetime data types :
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 :
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.
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.
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.
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.
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.
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
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|
|NATIONAL CHARACTER VARYING(n)
NATIONAL CHAR VARYING(n)
DECIMAL[(p,s)] (Note 1)
|FLOAT (Note 2)
DOUBLE PRECISION (Note 3)
REAL (Note 4)
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.
|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.
Oracle supplies a family of URI types—URIType, DBURIType, XDBURIType, and HTTPURIType—which are related by an inheritance hierarchy.
|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.|
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.
|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.|
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:
|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.
|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).|
Solve these problems: