Summary: in this tutorial, you will learn about the overview of the built-in Oracle data types.
Introduction to Oracle data types
In Oracle, every value has a data type that defines a set of characteristics for the value. These characteristics cause Oracle to treat the values of one data type differently from the values of another. For example, you can add values of the NUMBER data type, but not values of the RAW data type.
When creating a new table, you specify a data type for each of its columns. Similarly, when you create a new procedure, you specify a data type for each of its arguments.
The data type defines the allowed values that each column or argument can store. For example, a DATE column cannot store a value of February 30, because this is not a valid date.
Oracle has a number of built-in data types illustrated in the following table:
| Code | Data Type |
|---|---|
| 1 | VARCHAR2(size [BYTE | CHAR]) |
| 1 | NVARCHAR2(size) |
| 2 | NUMBER[(precision [, scale]]) |
| 8 | LONG |
| 12 | DATE |
| 21 | BINARY_FLOAT |
| 22 | BINARY_DOUBLE |
| 23 | RAW(size) |
| 24 | LONG RAW |
| 69 | ROWID |
| 96 | CHAR [(size [BYTE | CHAR])] |
| 96 | NCHAR[(size)] |
| 112 | CLOB |
| 112 | NCLOB |
| 113 | BLOB |
| 114 | BFILE |
| 180 | TIMESTAMP [(fractional_seconds)] |
| 181 | TIMESTAMP [(fractional_seconds)] WITH TIME ZONE |
| 182 | INTERVAL YEAR [(year_precision)] TO MONTH |
| 183 | INTERVAL DAY [(day_precision)] TO SECOND[(fractional_seconds)] |
| 208 | UROWID [(size)] |
| 231 | TIMESTAMP [(fractional_seconds)] WITH LOCAL TIMEZONE |
Each data type has a code managed internally by Oracle. To find the data type code of a value in a column, you use the DUMP() function.
Character data types
Character data types consist of CHAR, NCHAR, VARCHAR2, NVARCHAR2, and VARCHAR.
The NCHAR and NVARCHAR2 data types are for storing Unicode character strings.
The fixed-length character data types are CHAR, NCHAR and the variable-length character data types are VARCHAR2, NVARCHAR2.
VARCHAR is the synonym of VARCHAR2. However, you should not use VARCHAR because Oracle may change its semantics in the future.
For character data types, you can specify their sizes either in bytes or characters.
Number data type
The NUMBER data type has precision p and scale s. The precision ranges from 1 to 38 while the scale range from -84 to 127.
If you don’t specify the precision, the column can store values including fixed-point and floating-point numbers. The default value for the scale is zero.
Datetime and Interval data types
Datetime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The values of a datetime data type are datetimes.
The interval data types are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. The values of the interval data type are intervals.
RAW and LONG RAW data types
The RAW and LONG RAW data types are for storing binary data or byte strings e.g., the content of documents, sound files, and video files.
The RAW data type can store up to 2000 bytes while the LONG RAW data type can store up to 2GB.
BFILE Datatype
BFILE data type stores a locator to a large binary file which located outside the database. The locator consists of the directory and file names.
BLOB Datatype
BLOB stands for a binary large object. You use the BLOB data type to store binary objects with the maximum size of (4 gigabytes – 1) * (database block size).
CLOB Datatype
CLOB stands for character large object. You use CLOB to store single-byte or multibyte characters with the maximum size is (4 gigabytes – 1) * (database block size).
Note that CLOB supports both fixed-with and variable-with character sets.
NCLOB Datatype
NCLOB is similar to CLOB except that it can store the Unicode characters.
UROWID Datatype
The UROWID is primarily for values returned by the ROWID pseudo-column. Its values are base 64 strings that represent the unique address of rows in a table.
Data Types: Oracle and ANSI
When you use ANSI data types for the column definitions, Oracle will convert them to their corresponding data types in Oracle based on the following mapping table:
| ANSI SQL Datatype | Oracle Data Type |
|---|---|
| CHARACTER(n) | CHAR(n) |
| CHAR(n) | |
| CHARACTER VARYING(n) | VARCHAR2(n) |
| CHAR VARYING(n) | |
| NATIONAL CHARACTER(n) | NCHAR(n) |
| NATIONAL CHAR(n) | |
| NCHAR(n) | |
| NATIONAL CHARACTER VARYING(n) | NVARCHAR2(n) |
| NATIONAL CHAR VARYING(n) | |
| NCHAR VARYING(n) | |
| NUMERIC(p,s) | NUMBER(p,s) |
| DECIMAL(p,s) (a) | |
| INTEGER | NUMBER(38) |
| INT | |
| SMALLINT | |
| FLOAT (b) | NUMBER |
| DOUBLE PRECISION (c) | |
| REAL (d) |
In this tutorial, you have learned about the overview of built-in Oracle data types including Character, number, datetime, interval, BLOB, CLOB, BFILE, and ROWID.
