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 which defines a set of characteristics for the value. These characteristics cause Oracle to treat values of one data type differently from values of another. For example, you can add values of the NUMBER data type, but not values of the RAW data type.
When you create 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:
|1||VARCHAR2(size [BYTE | CHAR])|
|2||NUMBER[(precision [, scale]])|
|96||CHAR [(size [BYTE | CHAR])]|
|181||TIMESTAMP [(fractional_seconds)] WITH TIME ZONE|
|182||INTERVAL YEAR [(year_precision)] TO MONTH|
|183||INTERVAL DAY [(day_precision)] TO SECOND[(fractional_seconds)]|
|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
Character data types
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
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 data type stores a locator to a large binary file which locates outside the database. The locator consists of the directory and file names.
BLOB stands for 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 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 is similar to CLOB except that it can store the Unicode characters.
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 to their corresponding data types in Oracle based on the following mapping table:
|ANSI SQL Datatype||Oracle Data Type|
|NATIONAL CHARACTER VARYING(n)||NVARCHAR2(n)|
|NATIONAL CHAR VARYING(n)|
|DOUBLE PRECISION (c)|
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.