Oracle Data Types Cheatsheet

In this post, we are going to learn more about the Oracle built-in data types and their properties.

These data types are available in Oracle/PLSQL, which includes character, numeric, date/time, LOB and rowid data types.

Datatype	Description	Max Size
VARCHAR2(size)	Variable length character string having maximum length size bytes.	From 1 byte to 4KB.
NVARCHAR2(size)	Variable length national character set string having maximum length sizebytes.	From 1 byte to 4KB.
CHAR(size)	Fixed length character data of length size bytes.	2000 bytes. Default and minimum size is 1 byte.
NCHAR(size)	Fixed length national character set data of length size bytes.	2000 bytes. Default and minimum size is 1 byte.
NUMBER(p,s)	Number having precision p and scale s.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.
BINARY_FLOAT	A 32-bit, single-precision floating-point number data type.	Each BINARY_FLOAT value requires 4 bytes.
BINARY_DOUBLE	A 64-bit, double-precision floating-point number data type.	Each BINARY_DOUBLE value requires 8 bytes.
LONG	Character data of variable length (A bigger version the VARCHAR2 datatype)	2 Gigabytes
DATE	Valid date range	from January 1, 4712 BC to December 31, 9999AD.
TIMESTAMP (fractional_seconds_precision)	the number of digits in the fractional part of the SECOND datetime field.	Accepted values of fractional_seconds_precisionare 0 to 9. (default = 6)
TIMESTAMP (fractional_seconds_precision) WITH {LOCAL} TIMEZONE	As above with time zone displacement value	Accepted values are 0 to 9. The default is 6.
INTERVAL YEAR (year_precision) TO MONTH	Time in years and months, where year_precision is the number of digits in the YEAR datetime field.	Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)	Time in days, hours, minutes, and seconds. day_precision is the maximum number of digits in ‘DAY’ fractional_seconds_precision is the max number of fractional digits in the SECOND field.	day_precision can be 0 to 9. (default = 2) fractional_seconds_precisioncan be 0 to 9. (default = 6)
RAW(size)	Raw binary data of length size bytes.	Maximum size is 2000bytes
LONG RAW	Raw binary data of variable length.	2 Gigabytes.
ROWID	Hexadecimal string representing the unique address of a row in its table.	10 bytes
UROWID	Hex string representing the logical address of a row of an index-organized table	The maximum size and default is 4000 bytes
CLOB	Character Large Object	4 Gigabytes
NCLOB	National Character Large Object	4 Gigabytes
BLOB	Binary Large Object	4 Gigabytes
BFILE	pointer to binary file on disk	4 Gigabytes

Looking for a good GUI tool to work with Oracle? Try TablePlus.

TablePlus is a modern, native client with intuitive GUI tools to create, access, query & edit multiple relational databases: MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Amazon Redshift, MariaDB, CockroachDB, Vertica, Cassandra, Oracle, and Redis.

It can’t be perfect for everone but perhaps you should try it yourself and see if it’s good for your database management tasks. It’s free anyway.