Oracle Data Types

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:

CodeData Type
1VARCHAR2(size [BYTE | CHAR])
1NVARCHAR2(size)
2NUMBER[(precision [, scale]])
8LONG
12DATE
21BINARY_FLOAT
22BINARY_DOUBLE
23RAW(size)
24LONG RAW
69ROWID
96CHAR [(size [BYTE | CHAR])]
96NCHAR[(size)]
112CLOB
112NCLOB
113BLOB
114BFILE
180TIMESTAMP [(fractional_seconds)]
181TIMESTAMP [(fractional_seconds)] WITH TIME ZONE
182INTERVAL YEAR [(year_precision)] TO MONTH
183INTERVAL DAY [(day_precision)] TO SECOND[(fractional_seconds)]
208UROWID [(size)]
231TIMESTAMP [(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 DatatypeOracle 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)
INTEGERNUMBER(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.

Was this tutorial helpful?