Oracle datatypes

Datatype summary for Oracle 7, 8 & 9

Datatype

Description

Max Size:
Oracle 7

Max Size:
Oracle 8

Max Size:
Oracle 9

Max Size:
PL/SQL

PL/SQL
Subtypes/
Synonyms

VARCHAR2(size)

Variable length character string having maximum length size bytes.
You must specify size

2000 bytes
minimum is 1

4000 bytes
minimum is 1

4000 bytes
minimum is 1

32767 bytes
minimum is 1

STRING
VARCHAR

NVARCHAR2(size)

Variable length national character set string having maximum length size bytes.
You must specify size

N/A

4000 bytes
minimum is 1

4000 bytes
minimum is 1

32767 bytes
minimum is 1

STRING
VARCHAR

VARCHAR

Now deprecated - VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions.

-

-

-

CHAR(size)

Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...

255 bytes
Default and minimum size is 1 byte.

2000 bytes
Default and minimum size is 1 byte.

2000 bytes
Default and minimum size is 1 byte.

32767 bytes
Default and minimum size is 1 byte.

CHARACTER

NCHAR(size)

Fixed length national character set data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...

N/A

2000 bytes
Default and minimum size is 1 byte.

2000 bytes
Default and minimum size is 1 byte.

32767 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.

The precision p can range from 1 to 38.

The scale s can range from -84 to 127.

The precision p can range from 1 to 38.

The scale s can range from -84 to 127.

Magnitude
1E-130 .. 10E125

maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits

The scale s can range from -84 to 127.

For floating point don't specify p,s

REAL has a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits

fixed-point numbers:
DEC
DECIMAL
NUMERIC

integers:
INTEGER
INT
SMALLINT

floating-point:
DOUBLE PRECISION FLOAT

REAL

PLS_INTEGER

signed integers
PLS_INTEGER values require less storage and provide better performance than NUMBER values.
So use PLS_INTEGER where you can!

PL/SQL only

PL/SQL only

PL/SQL only

magnitude range is -2147483647 .. 2147483647

BINARY_INTEGER

signed integers (older slower version of PLS_INTEGER)

magnitude range is -2147483647 .. 2147483647

LONG

Character data of variable length (A bigger version the VARCHAR2 datatype)

2 Gigabytes

2 Gigabytes

2 Gigabytes - but now deprecated

32760 bytes
Note this is smalller than the maximum width of a LONG column

DATE

Valid date range

from January 1, 4712 BC to December 31, 4712 AD.

from January 1, 4712 BC to December 31, 9999 AD.

from January 1, 4712 BC to December 31, 9999 AD.

from January 1, 4712 BC to December 31, 9999 AD.
(in Oracle7 = 4712 AD)

TIMESTAMP (fractional_seconds_precision)

the number of digits in the fractional part of the SECOND datetime field.

-

-

Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)

TIMESTAMP (fractional_seconds_precision) WITH {LOCAL} TIMEZONE

As above with time zone displacement value

-

-

Accepted values of fractional_seconds_precision are 0 to 9. (default = 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. (default = 2)

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 may be 0 to 9. (default = 2)

fractional_seconds_precision may be 0 to 9. (default = 6)

RAW(size)

Raw binary data of length size bytes.
You must specify size for a RAW value.

Maximum size is 255 bytes.

Maximum size is 2000 bytes

Maximum size is 2000 bytes

32767 bytes

LONG RAW

Raw binary data of variable length. (not intrepreted by PL/SQL)

2 Gigabytes.

2 Gigabytes.

2 Gigabytes - but now deprecated

32760 bytes
Note this is smalller than the maximum width of a LONG RAW column

ROWID

Hexadecimal string representing the unique address of a row in its table.
(primarily for values returned by the ROWID pseudocolumn.)

Hexadecimal string representing the unique address of a row in its table.
(primarily for values returned by the ROWID pseudocolumn.)

UROWID

Hex string representing the logical address of a row of an index-organized table

N/A

The maximum size and default is 4000 bytes

The maximum size and default is 4000 bytes

universal rowid - Hex string representing the logical address of a row of an index-organized table, either physical, logical, or foreign (non-Oracle)

See CHARTOROWID and the package: DBMS_ROWID

MLSLABEL

Binary format of an operating system label.This datatype is used with Trusted Oracle7.

CLOB

Character Large Object

4Gigabytes

4Gigabytes

4Gigabytes

4Gigabytes

NCLOB

National Character Large Object

4Gigabytes

4Gigabytes

4Gigabytes

BLOB

Binary Large Object

4Gigabytes

4Gigabytes

4Gigabytes

BFILE

pointer to binary file on disk

4Gigabytes

4Gigabytes

The size of a BFILE is system dependent but cannot exceed four gigabytes (2**32 - 1 bytes).

Notes (pro's & cons)

INTEGER
This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38)

VARCHAR2:
Storing character data as Varchar2 will save space:

Store 'SMITH' not 'SMITH     '

CHAR:
Over time, when varchar2 columns are updated they will sometimes create chained rows - because CHAR columns are fixed width they are not affected by this - so less DBA effort is required to maintain performance.

NUMBER
When retrieving data for a NUMBER column, consider (if you can) using the PL/SQL datatype: PLS_INTEGER for better performance.

LONG
You should start using BLOB instead of LONG

Comparison with other RDBMS's

int10

int6

int1

char(n)

blob

Oracle 8

NUMBER(10)

NUMBER(6)

NUMBER(1)

VARCHAR2(n)

BLOB

Sybase system 10

NUMERIC(10)

NUMERIC(6)

NUMERIC(1)

VARCHAR(n)

IMAGE

MS Access 97

Long Int or Double

Single

Byte

TEXT(n)

LONGBINARY

TERADATA

INTEGER

DECIMAL(6)

DECIMAL(1)

VARCHAR(n)

VARBYTE(20480)

DB2

INTEGER

DECIMAL(6)

DECIMAL(1)

VARCHAR(n)

VARCHAR(255)

RDB

INTEGER

DECIMAL(6)

DECIMAL(1)

VARCHAR(n)

LONG VARCHAR

INFORMIX

INTEGER

DECIMAL(6)

DECIMAL(1)

VARCHAR(n)

BYTE

SYBASE

NUMERIC(10)

NUMERIC(6)

NUMERIC(1)

VARCHAR(n)

IMAGE

MS SQL Server 6.0

NUMERIC(10)

NUMERIC(6)

NUMERIC(1)

VARCHAR(n)

IMAGE

RedBrick

integer

int

int

char(n)

char(1024)

INGRES

INTEGER

INTEGER

INTEGER

VARCHAR(n)

VARCHAR(1500)

Datatype	Description	Max Size: Oracle 7	Max Size: Oracle 8	Max Size: Oracle 9	Max Size: PL/SQL	PL/SQL Subtypes/ Synonyms
VARCHAR2(size)	Variable length character string having maximum length size bytes. You must specify size	2000 bytes minimum is 1	4000 bytes minimum is 1	4000 bytes minimum is 1	32767 bytes minimum is 1	STRING VARCHAR
NVARCHAR2(size)	Variable length national character set string having maximum length size bytes. You must specify size	N/A	4000 bytes minimum is 1	4000 bytes minimum is 1	32767 bytes minimum is 1	STRING VARCHAR
VARCHAR	Now deprecated - VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions.	-	-	-
CHAR(size)	Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...	255 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	32767 bytes Default and minimum size is 1 byte.	CHARACTER
NCHAR(size)	Fixed length national character set data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...	N/A	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	32767 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.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.	Magnitude 1E-130 .. 10E125 maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits The scale s can range from -84 to 127. For floating point don't specify p,s REAL has a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits	fixed-point numbers: DEC DECIMAL NUMERIC integers: INTEGER INT SMALLINT floating-point: DOUBLE PRECISION FLOAT REAL
PLS_INTEGER	signed integers PLS_INTEGER values require less storage and provide better performance than NUMBER values. So use PLS_INTEGER where you can!	PL/SQL only	PL/SQL only	PL/SQL only	magnitude range is -2147483647 .. 2147483647
BINARY_INTEGER	signed integers (older slower version of PLS_INTEGER)				magnitude range is -2147483647 .. 2147483647
LONG	Character data of variable length (A bigger version the VARCHAR2 datatype)	2 Gigabytes	2 Gigabytes	2 Gigabytes - but now deprecated	32760 bytes Note this is smalller than the maximum width of a LONG column
DATE	Valid date range	from January 1, 4712 BC to December 31, 4712 AD.	from January 1, 4712 BC to December 31, 9999 AD.	from January 1, 4712 BC to December 31, 9999 AD.	from January 1, 4712 BC to December 31, 9999 AD. (in Oracle7 = 4712 AD)
TIMESTAMP (fractional_seconds_precision)	the number of digits in the fractional part of the SECOND datetime field.	-	-	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
TIMESTAMP (fractional_seconds_precision) WITH {LOCAL} TIMEZONE	As above with time zone displacement value	-	-	Accepted values of fractional_seconds_precision are 0 to 9. (default = 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. (default = 2)
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 may be 0 to 9. (default = 2) fractional_seconds_precision may be 0 to 9. (default = 6)
RAW(size)	Raw binary data of length size bytes. You must specify size for a RAW value.	Maximum size is 255 bytes.	Maximum size is 2000 bytes	Maximum size is 2000 bytes	32767 bytes
LONG RAW	Raw binary data of variable length. (not intrepreted by PL/SQL)	2 Gigabytes.	2 Gigabytes.	2 Gigabytes - but now deprecated	32760 bytes Note this is smalller than the maximum width of a LONG RAW column
ROWID	Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)				Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)
UROWID	Hex string representing the logical address of a row of an index-organized table	N/A	The maximum size and default is 4000 bytes	The maximum size and default is 4000 bytes	universal rowid - Hex string representing the logical address of a row of an index-organized table, either physical, logical, or foreign (non-Oracle)	See CHARTOROWID and the package: DBMS_ROWID
MLSLABEL	Binary format of an operating system label.This datatype is used with Trusted Oracle7.
CLOB	Character Large Object	4Gigabytes	4Gigabytes	4Gigabytes	4Gigabytes
NCLOB	National Character Large Object		4Gigabytes	4Gigabytes	4Gigabytes
BLOB	Binary Large Object		4Gigabytes	4Gigabytes	4Gigabytes
BFILE	pointer to binary file on disk		4Gigabytes	4Gigabytes	The size of a BFILE is system dependent but cannot exceed four gigabytes (2**32 - 1 bytes).

	int10	int6	int1	char(n)	blob
Oracle 8	NUMBER(10)	NUMBER(6)	NUMBER(1)	VARCHAR2(n)	BLOB
Sybase system 10	NUMERIC(10)	NUMERIC(6)	NUMERIC(1)	VARCHAR(n)	IMAGE
MS Access 97	Long Int or Double	Single	Byte	TEXT(n)	LONGBINARY
TERADATA	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARBYTE(20480)
DB2	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARCHAR(255)
RDB	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	LONG VARCHAR
INFORMIX	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	BYTE
SYBASE	NUMERIC(10)	NUMERIC(6)	NUMERIC(1)	VARCHAR(n)	IMAGE
MS SQL Server 6.0	NUMERIC(10)	NUMERIC(6)	NUMERIC(1)	VARCHAR(n)	IMAGE
RedBrick	integer	int	int	char(n)	char(1024)
INGRES	INTEGER	INTEGER	INTEGER	VARCHAR(n)	VARCHAR(1500)