A Guide to the Oracle Data Types

In this article, I explain what the different data types are in Oracle database and everything you need to know about them.

 

What You’ll Learn about Oracle SQL Data Types

There are many data types in the Oracle database. There are character, number, date, and other data types.

When you create a table, you need to specify a data type for each column. Each data type has a certain storage format, range of valid values, and constraints.

Knowing what they are, what they are good for, and their limitations will help you when you run your SQL queries.

In this article, you’ll learn:

• All of the Oracle data types
• How they differ between Oracle 9i, Oracle 10g, Oracle 11g, and Oracle 12c
• Some of the reasoning behind data types
• When to use them, and when not to use them

So, now let’s look at the data types that Oracle has.

For information on other database data types, refer to this guide.

Character Data Types

Character data types are those that store alphanumeric values, or strings. They store data based on the database’s character set.

This table shows the different character data types in each Oracle version

Data Type	Description	MAX Size – Oracle 11g	MAX Size – Oracle 12c	MAX Size – PL/SQL
CHAR(size)	Fixed length character with a length of size.	2000 bytes. Default and minimum is 1 byte	2000 bytes. Default and minimum is 1 byte	32,767 bytes. Default and minimum is 1 byte
NCHAR(size)	Fixed length national character with a length of size.	2000 bytes. Default and minimum is 1 byte	2000 bytes. Default and minimum is 1 byte	32,767 bytes. Default and minimum is 1 byte
VARCHAR	Deprecated and only used for backward compatibity.
VARCHAR2(size)	Variable length character string with a maximum length of size bytes	4000 bytes. Minimum is 1 byte	32,767 bytes	32,767 bytes. Minimum is 1 byte
NVARCHAR2(size)	Variable length national character string with a maximum length of size bytes	4000 bytes. Minimum is 1 byte	32,767 bytes	32,767 bytes. Minimum is 1 byte
LONG	Variable length character string. Larger than VARCHAR2. Deprecated	2 GB	2 GB	32,760 bytes.
RAW(size)	Raw binary data of length size	2000 bytes	32,767 bytes	32,767 bytes
LONG RAW	Raw binary data of variable length. Deprecated	2 GB	2 GB	32,760 bytes.

Let’s look at these data types in more detail.

 

CHAR Data Type

The CHAR data type stores strings that have a fixed length. This means that the value takes up the same number of bytes, regardless of the value it holds.

When you create a table with a column defined as CHAR, you need to specify a length. The length must be between 1 and 2000. This length is the number of characters to store in the field.

The main thing to know about CHAR values is that if you insert or update a value that is less than this length, then the value is padded with blank characters up to the length of the column.

For example, let’s say you have a column with a data type of CHAR(10). You insert the value of “Tree”, which is only 4 characters long.

The field will contain the value of “Tree” but it will be padded with six space characters on the right-hand side, turning it into a value of “Tree      “.

If you wanted to insert a value of “Large Oak Tree”, Oracle would return an error. This is because the value inserted is too large for the column (14-character value into a 10-character column).

When to use CHAR: There should be no reason to use the CHAR data type, as it is similar to a VARCHAR2 and it’s better to be consistent.

 

VARCHAR2 Data Type

The VARCHAR2 data type stores alphanumeric values in variable-length strings. This means, unlike the CHAR data type, the length is variable.

When you create a column with the VARCHAR2 data type, you specify a maximum length between 1 and 4000 bytes. Only the characters in the value you insert or update are stored.

For example, if a column is defined as VARCHAR2(10), then it can store up to 10 characters. If I insert a value of “Tree”, which is 4 characters, then the value stored is “Tree”. No spaces are added, and the value does not need to be 10 characters.

Using a VARCHAR2 column will often save space when compared to CHAR columns.

VARCHAR2 is, in my experience, the most common of the character data types in Oracle. There are some uses for CHAR columns, but most of the time when I am working with strings, I use the VARCHAR2 column.

Why is it called VARCHAR2 and not VARCHAR?

Well, the VARCHAR is a standard data type and isn’t used in Oracle SQL. When Oracle wanted to implement their own version of VARCHAR, rather than changing the implementation of VARCHAR, they created VARCHAR2.

So, if you come from another database system such as SQL Server or MySQL, don’t use VARCHAR – use VARCHAR2.

When to use VARCHAR2: If you’re storing text values that can vary in length (which is probably most of the time).

 

NCHAR Data Type

The NCHAR data type is similar to the CHAR data type, except that it stores Unicode data. It allows multi-byte characters to be stored in a fixed-length field.

With the NCHAR field, it can store up to 2000 bytes and must be shorter than 2000 characters. Both of these constraints are applied, as it’s possible for one character to be stored as multiple bytes.

When you define an NCHAR data type, you specify the size in characters.

When to use NCHAR: If you need to store text values of a fixed length, but need Unicode data.

 

NVARCHAR2 Data Type

The NVARCHAR2 is similar to the VARCHAR2 data type, in the same way that NCHAR is similar to CHAR.

NVARCHAR2 stores Unicode data and allows multi-byte characters to be stored in a variable-length field.

When you define an NVARCHAR2 data type, you specify the size in characters.

When to use NVARCHAR2: If you need to store text data (like VARCHAR2), but you need to store Unicode characters.

 

LONG Data Type

Columns that have a data type of LONG can store data of up to 2 gigabytes, and contain text data.

However, it’s not recommended to create columns with the LONG data type. This is because LONG is one of the Oracle data types that is deprecated and only supported for backwards compatibility.

If you want to store a large amount of data in a single field, it’s better to use LOB data types (such as CLOB or BLOB). These fields have more functionality than LONG columns.

I’ve detailed the LOB data types further down in this article.

When to use LONG: Never. It’s been deprecated, and you should use CLOB or BLOB instead.

 

RAW Data Type

The RAW data type is similar to VARCHAR2 but is used when you don’t want the data to be converted between systems.

It stores a variable-length string, like VARCHAR2. However, when performing import or export functions, the character conversion is not performed.

When to use RAW: To store text values of variable length that are often converted between systems.

 

LONG RAW data Type

The LONG RAW data type is similar to the LONG data type, except like the RAW data type, the characters are not converted when working between different systems.

Also, like the LONG data type, Oracle recommends that you use the BLOB (or BFILE) data type instead of LONG RAW. LONG RAW is only supported for backward compatibility and BLOB and BFILE have more functionality and fewer restrictions.

When to use LONG RAW: Never. It’s been deprecated, and you should use BLOB or BFILE instead.

 

Numeric Data Types

Oracle has several numeric data types. These data types are useful for storing number values, both whole numbers and decimal values.

The numeric Oracle data types are:

Data Type	Description	MAX Size – Oracle 11g	MAX Size – Oracle 12c	MAX Size – PL/SQL
NUMBER(p,s)	Numeric data, with a precision of p and scale of s.	Precision p ranges from 1 to 38, and scale ranges from -84 to 127	Precision p ranges from 1 to 38, and scale ranges from -84 to 127	Precision p ranges from 1 to 38, and scale ranges from -84 to 127
BINARY_FLOAT	32-bit, single-precision floating point number	From 1.17549E-38F to 3.40282E+38F	From 1.17549E-38F to 3.40282E+38F	n/a
BINARY_DOUBLE	64-bit, double-precision floating point number	From 2.22507485850720E-308 to 1.79769313486231E+308	From 2.22507485850720E-308 to 1.79769313486231E+308	n/a
BOOLEAN	True, False, or NULL	n/a	n/a	n/a
PLS_INTEGER	Signed integer.	n/a	n/a	From -2,147,483,647 to 2,147,483,647
BINARY_INTEGER	Signed integer. Older, slower version of PLS_INTEGER	n/a	n/a	From -2,147,483,647 to 2,147,483,647
INTEGER	Translated to NUMBER(38)	n/a	n/a
FLOAT	Translated to NUMBER	1 to 126 binary digits, and up to 22 bytes	1 to 126 binary digits, and up to 22 bytes
DECIMAL	Translated to NUMBER	n/a	n/a

Let’s take a look at these data types in more detail.

 

NUMBER Data Type

The NUMBER data type is used to store fixed and floating-point numbers. It has a large range of values, from -9.99.. (38 9’s) x 10^125 to 9.99.. (38 9’s) x 10^125.

It can store positive numbers, and negative numbers, and can store up to 38 digits.

When defining a number, you specify two things: a precision and a scale.

• Precision is the total number of digits in a number.
• Scale is the number of digits to the right of the decimal point.

You can define a number using these two parameters, which are optional:

NUMBER (p, s);

If the precision is not specified, then the column stores the numbers that are inserted as they are provided.

NUMBER;

If the scale is not specified, then the scale is 0.

You can specify the scale and no precision if you like:

NUMBER (*, s);

However, it’s recommended that you provide both numbers as an additional validation when inserting data.

You can also specify a negative scale. This means that any values are rounded to this number of decimal places to the left of the decimal place.

For example, defining a number column like this will round all values to the nearest thousand:

NUMBER (12, 3);

So, with all of these different ways you can specify a NUMBER field, how would a value look with each of these possibilities?

Let’s take a look. What if we tried to insert the value 12,345,678.9012 into a NUMBER column?

Number Column Specified	Value Stored As
NUMBER	12,345,678.9012
NUMBER(10,2)	12,345,678.90
NUMBER(*,1)	12,345,678.9
NUMBER(10)	12,345,678
NUMBER(5)	Error – exceeds precision
NUMBER(10,1)	12,345,678.9
NUMBER(10,-3)	12,346,000

So, you can see that the same number value can be stored in many different ways, depending on how you define your column.

When to use NUMBER: If you need to store an exact number (which may be in most cases).

 

BINARY_FLOAT Data Type

There are two Oracle data types for storing floating-point numbers. One is BINARY_FLOAT, and the other is BINARY_DOUBLE which is detailed below.

The difference between BINARY_FLOAT and number is:

• NUMBER uses decimal precision and BINARY_FLOAT uses binary precision.
• BINARY_FLOAT can perform arithmetic calculations faster and is usually smaller to store.
• NUMBER is an exact definition, whereas BINARY_FLOAT is an approximate definition.

The main difference between these two types is the third point – BINARY_FLOAT is approximate. This means the data type stores approximate representations of decimal values, rather than the exact value.

It’s similar to a FLOAT data type in other languages. BINARY_FLOAT is a 32-bit single precision value.

When to use BINARY_FLOAT: If performance is important, or if you need to store a large floating-point number.

 

BINARY_DOUBLE Data Type

Just like the BINARY_FLOAT data type, the BINARY_DOUBLE stores approximate values rather than exact values, and is faster at calculations than NUMBER.

However, because it doesn’t store exact values, it has a specialised need. It works like a DOUBLE in other languages.

The difference between BINARY_FLOAT and BINARY_DOUBLE is that BINARY_DOUBLE is that the BINARY_DOUBLE is a 64-bit double precision value.

When to use BINARY_DOUBLE: If performance is important, or if you need to store a floating-point number larger than what BINARY_FLOAT can handle.

 

BOOLEAN Data Type

BOOLEAN values are used to store true or false. They work in a similar way to many other programming languages.

There isn’t a lot to say about these data types. They are simple. They store TRUE or FALSE. And they don’t take up a lot of room in a database.

However, it is only available in PL/SQL. To store booleans in SQL, you can use a NUMBER(1) field as identified in this guide to the SQL boolean.

When to use BOOLEAN: If you need to store a TRUE/FALSE value or ON/OFF value.

 

PLS_INTEGER Data Type

The PLS_INTEGER data type is only available to PL/SQL code. It works in a similar way to the NUMBER value in Oracle SQL.

PLS_INTEGER is an integer data type, which means it can only store whole numbers. It’s signed, which means it can store positive and negative numbers.

The range is from -2,147,483,647 to 2,147,483,647.

If you’re using PL/SQL, you can use this data type. They perform better and require less storage than NUMBER data types.

When to use PLS_INTEGER: If you’re using PL/SQL and you need to use integer values.

 

BINARY_INTEGER Data Type

The BINARY_INTEGER is a similar data type to the PLS_INTEGER. It has the same range (from -2,147,483,647 to 2,147,483,647), and can only take whole numbers.

However, it’s older, and slower version of the PLS_INTEGER. So, for any programs in PL/SQL you want to write, you should use PLS_INTEGER instead of the BINARY_INTEGER data type.

When to use BINARY_INTEGER: Never. You should use PLS_INTEGER instead.

 

INTEGER Data Type

The INTEGER data type is an ANSI standard data type, which means it is in all SQL databases.

However, in Oracle, it’s a synonym for NUMBER(38).

This means that if you declare an INTEGER column, Oracle is actually declaring a NUMBER column with 38 digits and 0 decimal places.

When to use INTEGER: You should use the NUMBER data type instead.

 

FLOAT Data Type

This Oracle FLOAT data type is also an ANSI standard data type. FLOAT stores positive, negative, and zero floating point values.

In Oracle, the FLOAT data type is a synonym for the NUMBER data type. It doesn’t have the rounding issues that the BINARY_FLOAT data type may have.

When to use FLOAT: I would suggest using NUMBER rather than FLOAT just to be clearer.

 

DECIMAL Data Type

The DECIMAL data type in Oracle is an ANSI standard data type, but it is treated internally as a NUMBER data type.

The main difference is that the scale is 0 by default for DECIMAL, which means it is then created as an INTEGER data type.

So, if you declare a column as a DECIMAL with a scale, it is stored as NUMBER, but if there is no scale, it is stored as INTEGER.

When to use DECIMAL: I would suggest using a NUMBER data type rather than DECIMAL.

 

Date/Time Data Types

Date/time data types in Oracle are used to store date and time values. Some of them are simple, such as the DATE data type. Others involve time zones such as TIMESTAMP WITH TIME ZONE, and others store a period of time such as INTERVAL YEAR TO MONTH.

Here is the full list of Oracle Date/Time data types:

Data Type	Description	MAX Size – Oracle 11g	MAX Size – Oracle 12c
DATE	A date value that stores time as well	From Jan 1, 4712 BC to Dec 31 9999 AD	From Jan 1, 4712 BC to Dec 31 9999 AD
TIMESTAMP	A date value that stores time and fractional seconds	From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9	From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
TIMESTAMP WITH TIME ZONE	A timestamp that stores data in the specified timezone	From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9	From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
TIMESTAMP WITH LOCAL TIME ZONE	A timestamp that stores data in your local timezone	From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9	From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
INTERVAL YEAR TO MONTH	Stores a period of time in years and months	Up to 2 digits in the year	Up to 2 digits in the year
INTERVAL DAY TO SECOND	Stores a period of time in days, hours, minutes, and seconds	Between 0 and 9 digits in the day, between 0 and 9 digits fractional seconds	Between 0 and 9 digits in the day, between 0 and 9 digits fractional seconds

Let’s take a look at these data types in more detail.

 

DATE Data Type

The DATE data type stores a point in time and includes both the date and time component.

It stores:

• The year (including century)
• Month
• Day
• Hours
• Minutes
• Seconds

So, even though the name of the data type is DATE, and there is no DATETIME data type, this data type stores dates and times.

The default input and output format of a DATE value is DD-MON-YY. For example, for 12th October 2016:

’12-OCT-16′

This default format comes from the parameter NLS_DATE_FORMAT. It can be changed during the user session with ALTER SESSION.

Oracle stores all dates with a four-digit year, and includes four digits when importing and exporting, so there should be no confusion when viewing data as to which century the date belongs to.

You can retrieve the database server’s date using the SYSDATE function and the current session’s date using the CURRENT_DATE function.

When to use DATE: If you want to store dates and times but fractional seconds and time zones are not important.

 

TIMESTAMP Data Type

The TIMESTAMP data type is very similar to the DATE data type, except that it allows you to store fractions of a second.

Declaring a TIMESTAMP column means you specify the precision:

TIMESTAMP(p).

The precision must be a number between 0 and 9. If no precision is specified, the default value is 6.

This data type allows for more granular time storage than the DATE function.

You can retrieve the database server’s timestamp using the SYSTIMESTAMP function and the current session’s date using the CURRENT_TIMESTAMP function.

When to use TIMESTAMP: If you need sub-second times, such as for log files or tracking changes, and time zones are not important.

 

TIMESTAMP WITH TIME ZONE Data Type

The TIMESTAMP WITH TIME ZONE data type is similar to the TIMESTAMP data type in that it stores date and time down to the fractional second.

However, it also stores the timezone value. This is stored as a numerical offset representing the hours and minutes relative to GMT/UTC.

An example of a TIMESTAMP WITH TIME ZONE value is:

‘2016-10-12 09:12:00 -7:00’.

This indicates that the value is -7 hours from GMT/UTC.

To find the timezone of the database, you can use the DBTIMEZONE function.

When to use TIMESTAMP WITH TIME ZONE: When time zones are important but don’t need to be tailored to each user.

 

TIMESTAMP WITH LOCAL TIME ZONE Data Type

The TIMESTAMP WITH LOCAL TIME ZONE data type also stores a date, time, and timezone, similar to the TIMESTAMP WITH TIME ZONE data type.

However, it is different because when you select a value from it, the data will be displayed in the user’s session time zone. The other data type (TIMESTAMP WITH TIME ZONE) displays the value in the database time zone.

To find the timezone of your session, you can use the SESSIONTIMEZONE function.

When to use TIMESTAMP WITH LOCAL TIME ZONE: When time zones are important and they need to be tailored to each user (such as forum post dates and times)

 

INTERVAL YEAR TO MONTH Data Type

The interval data types in Oracle are used to store a period of time. This is different to the data types we’ve looked at so far which store a point in time.

An example of a period of time is “3 years” or “5 months”. This is best represented as an INTERVAL data type.

The data type you use depends on the value you want to store. If you want to store a value in years and months, use this INTERVAL YEAR TO MONTH data type.

Values can be entered in as two digits, then a hyphen, then two digits. For example, ’02-07′ represents 2 years and 7 months.

When to use INTERVAL YEAR TO MONTH: If you need to store a period of time, such as frequency for performance reviews.

 

INTERVAL DAY TO SECOND Data Type

Just like the INTERVAL YEAR TO MONTH data type, the INTERVAL DAY TO SECOND is used to store a period of time. It’s different to the other date data types because it stores a period of time rather than a point in time.

The INTERVAL DAY TO SECOND allows you to store days, hours, minutes and seconds. They are stored in this format: ‘D HH:MI:SS’.

This means a value of 120 days, 6 hours, 31 minutes and 14 seconds is displayed as ‘120 6:31:14’.

When to use INTERVAL DAY TO SECOND: When you need to store a period of time that involves days, minutes, hours or seconds, such as time delays.

 

Large Object (LOB) Data Types

There are several data types in Oracle that relate to large objects, or more commonly referred to as LOBs.

These data types are:

Data Type	Description	MAX Size – Oracle 11g	MAX Size – Oracle 12c
BFILE	A file locator that points to a file on the server	4GB of data	4GB of data
BLOB	Stores large unstructured binary objects	8 TB to 128 TB (4 GB -1 * (database block size)	8 TB to 128 TB (4 GB -1 * (database block size)
CLOB	Stores large single-byte and multi-byte objects	8 TB to 128 TB (4 GB -1 * (database block size)	8 TB to 128 TB (4 GB -1 * (database block size)
NCLOB	Stores unicode data	8 TB to 128 TB (4 GB -1 * (database block size)	8 TB to 128 TB (4 GB -1 * (database block size)

Let’s take a look at them in more detail.

 

BLOB Data Type

The BLOB data type is used to store unstructured binary large objects. This means it can store a lot of data in a single file, but it cannot store character data.

It has a maximum size of 4 GB.

When specifying a BLOB column, you can specify the size:

BLOB [ ( length [{K|M|G|}]

The length is the number of characters or the size of the field. You can use K, M, or G to represent kilobytes, megabytes, or gigabytes. If no size is specified, the default value is 4 GB.

When to use BLOB: It’s useful for storing images or audio files.

 

CLOB Data Type

The CLOB data type is used to store large amounts of character data. Unlike the BLOB data type, it can store characters, but it cannot store binary data.

It also has a maximum size of 4 GB.

When specifying a CLOB column, you can specify the size:

CLOB [ ( length [{K|M|G|}]

The length is the number of characters or the size of the field. You can use K, M, or G to represent kilobytes, megabytes, or gigabytes. If no size is specified, the default value is 4 GB.

When to use CLOB: Storing large amounts of text that cannot fit into a VARCHAR2 column.

 

NCLOB Data Type

The NCLOB data type is just like the CLOB data type, except that it can store multi-byte characters.

It also has a 4 GB maximum size and default size.

NCLOB [ ( length [{K|M|G|}]

The length is the number of characters or the size of the field. You can use K, M, or G to represent kilobytes, megabytes, or gigabytes. If no size is specified, the default value is 4 GB.

When to use NCLOB: If you need to store text data, and need to store it in multi-byte characters.

 

BFILE Data Type

The BFILE data type is used to refer to other files on the server, which are outside of the database system. It stores a pointer to a file.

They are read-only to Oracle. The maximum size is 4 GB.

When to use BFILE: If you need to refer to files outside of the database server, and would prefer to keep the file intact rather than import it into a CLOB or BLOB column.

 

ROWID Data Type

There are two other data types that are used to identify rows in Oracle.

Data Type	Description	MAX Size – Oracle 11g	MAX Size – Oracle 12c
ROWID	Fixed length binary data for a database row ID	10 bytes	10 bytes
UROWID	Universal RowID	4000 bytes	4000 bytes

Let’s look at these in more detail.

 

ROWID Data Type

The ROWID Oracle data type is used to store the address of every row in the database. It contains alphanumeric data, but is mostly used by the ROWID pseudocolumn.

The ROWID pseudocolumn is used to display the unique ROWID value for each record. It’s not able to be updated or specified during an INSERT statement.

If you create a new table and specify a column with the ROWID data type, you can add values to this new column that adhere to the constraints of the column (i.e. they are alphanumeric). However, Oracle doesn’t guarantee that it is a valid ROWID value (it refers to an actual row’s ROWID).

When to use ROWID: When you want to store a ROWID value for later reference.

 

UROWID Data Type

The UROWID data type is used to store ROWID values that come from non-Oracle databases. Because it is unknown what kind of ROWID values and format that other databases use, this data type can be used to store those kind of values.

When to use UROWID: When you need to store the ROWID-equivalent from a non-Oracle database.

 

Summary

So, as you’ve seen, there are many Oracle data types. They can hold a wide range of values and each have their advantages and limitations. Choosing the correct data type for your tables and columns will improve the quality and performance of your database.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!