{"id":1276,"date":"2017-11-02T13:15:07","date_gmt":"2017-11-02T06:15:07","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=1276"},"modified":"2025-05-30T18:14:19","modified_gmt":"2025-05-31T01:14:19","slug":"oracle-data-types","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-data-types\/","title":{"rendered":"Oracle Data Types"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the overview of the built-in Oracle data types.<\/p>\n\n\n\n

Introduction to Oracle data types #<\/a><\/h2>\n\n\n\n

In Oracle, every value has a data type that determines a set of characteristics. These characteristics instructs Oracle to handle the values of one data type differently from the values of another. <\/p>\n\n\n\n

For example, you can add values of the NUMBER<\/a> data type, but you can’t do the same with the RAW data type values.<\/p>\n\n\n\n

When creating a new table<\/a>, 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. <\/p>\n\n\n\n

The data type defines the allowed values that each column or argument can store. For example, a DATE<\/a> column cannot store a value of February 30, because this is not a valid date.<\/p>\n\n\n\n

Oracle has a number of built-in data types illustrated in the following table:<\/p>\n\n\n\n

Code<\/th>Data Type<\/th><\/tr><\/thead>
1<\/td>VARCHAR2(size [BYTE | CHAR])<\/td><\/tr>
1<\/td>NVARCHAR2(size)<\/td><\/tr>
2<\/td>NUMBER[(precision [, scale]])<\/a><\/td><\/tr>
8<\/td>LONG<\/td><\/tr>
12<\/td>DATE<\/td><\/tr>
21<\/td>BINARY_FLOAT<\/td><\/tr>
22<\/td>BINARY_DOUBLE<\/td><\/tr>
23<\/td>RAW(size)<\/td><\/tr>
24<\/td>LONG RAW<\/td><\/tr>
69<\/td>ROWID<\/td><\/tr>
96<\/td>CHAR [(size [BYTE | CHAR])]<\/td><\/tr>
96<\/td>NCHAR[(size)]<\/td><\/tr>
112<\/td>CLOB<\/td><\/tr>
112<\/td>NCLOB<\/td><\/tr>
113<\/td>BLOB<\/td><\/tr>
114<\/td>BFILE<\/td><\/tr>
180<\/td>TIMESTAMP [(fractional_seconds)]<\/td><\/tr>
181<\/td>TIMESTAMP [(fractional_seconds)] WITH TIME ZONE<\/td><\/tr>
182<\/td>INTERVAL YEAR [(year_precision)] TO MONTH<\/td><\/tr>
183<\/td>INTERVAL DAY [(day_precision)] TO SECOND[(fractional_seconds)]<\/td><\/tr>
208<\/td>UROWID [(size)]<\/td><\/tr>
231<\/td>TIMESTAMP [(fractional_seconds)] WITH LOCAL TIMEZONE<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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()<\/a><\/code> function.<\/p>\n\n\n\n

Character data types #<\/a><\/h2>\n\n\n\n

Character data types consist of CHAR<\/a>, NCHAR<\/a>, VARCHAR2<\/a>, NVARCHAR2<\/a>, and VARCHAR.<\/p>\n\n\n\n

The NCHAR<\/code> and NVARCHAR2<\/code> data types are for storing Unicode character strings.<\/p>\n\n\n\n

The fixed-length character data types are CHAR<\/code>, NCHAR<\/code> and the variable-length character data types are VARCHAR2<\/code>, NVARCHAR2<\/code>.<\/p>\n\n\n\n

VARCHAR<\/code> is the synonym of VARCHAR2<\/code>. However, you should not use VARCHAR<\/code> because Oracle may change its semantics in the future.<\/p>\n\n\n\n

For character data types, you can specify their sizes either in bytes or characters.<\/p>\n\n\n\n

Number data type #<\/a><\/h2>\n\n\n\n

The NUMBER<\/a> data type has precision p<\/strong> and scale s<\/strong>:<\/p>\n\n\n

NUMBER(p, s)<\/code><\/span><\/pre>\n\n\n
The precision ranges from 1 to 38 while the scale range from -84 to 127.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Datetime and Interval data types #<\/a><\/h2>\n\n\n\n
Datetime data types are DATE<\/a>, TIMESTAMP<\/a>, TIMESTAMP WITH TIME ZONE<\/a>, and TIMESTAMP WITH LOCAL TIME ZONE. The values of a datetime data type are datetimes.<\/p>\n\n\n\n
The interval data types are INTERVAL YEAR TO MONTH<\/a> and INTERVAL DAY TO SECOND<\/a>. The values of the interval data type are intervals.<\/p>\n\n\n\n
RAW and LONG RAW data types #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
The RAW data type can store up to 2000 bytes while the LONG RAW data type can store up to 2GB.<\/p>\n\n\n\n
BFILE Datatype #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
BLOB Datatype #<\/a><\/h2>\n\n\n\n
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).<\/p>\n\n\n\n
CLOB Datatype #<\/a><\/h2>\n\n\n\n
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).<\/p>\n\n\n\n
Note that CLOB supports both fixed-with and variable-with character sets.<\/p>\n\n\n\n
NCLOB Datatype #<\/a><\/h2>\n\n\n\n
NCLOB is similar to CLOB except that it can store the Unicode characters.<\/p>\n\n\n\n
UROWID Datatype #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Data Types: Oracle and ANSI #<\/a><\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
ANSI SQL Datatype<\/th>Oracle Data Type<\/th><\/tr><\/thead>
CHARACTER(n)<\/td>CHAR(n)<\/a><\/td><\/tr>
CHAR(n)<\/td><\/tr>
CHARACTER VARYING(n)<\/td>VARCHAR2(n)<\/a><\/td><\/tr>
CHAR VARYING(n)<\/td><\/tr>
NATIONAL CHARACTER(n)<\/td>NCHAR(n)<\/a><\/td><\/tr>
NATIONAL CHAR(n)<\/td><\/tr>
NCHAR(n)<\/td><\/tr>
NATIONAL CHARACTER VARYING(n)<\/td>NVARCHAR2(n)<\/a><\/td><\/tr>
NATIONAL CHAR VARYING(n)<\/td><\/tr>
NCHAR VARYING(n)<\/td><\/tr>
NUMERIC(p,s)<\/td>NUMBER(p,s)<\/a><\/td><\/tr>
DECIMAL(p,s)<\/td><\/tr>
INTEGER<\/td>NUMBER(38)<\/a><\/td><\/tr>
INT<\/td><\/tr>
SMALLINT<\/td><\/tr>
FLOAT<\/td>NUMBER<\/a><\/td><\/tr>
DOUBLE PRECISION<\/td><\/tr>
REAL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
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.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t