Oracle PL/SQL Datatypes

Data Type
Syntax

Oracle 9i

Explanation
(if applicable)

dec(p, s)

The maximum precision is 38 digits.

Where p is the precision and s is the scale.

For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.

decimal(p, s)

The maximum precision is 38 digits.

Where p is the precision and s is the scale.

For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.

double precision

float

int

integer

numeric(p, s)

The maximum precision is 38 digits.

Where p is the precision and s is the scale.

For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.

number(p, s)

The maximum precision is 38 digits.

Where p is the precision and s is the scale.

For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.

real

smallint

char (size)

Up to 32767 bytes in PLSQL.

Up to 2000 bytes in Oracle 9i.

Where size is the number of characters to store. Fixed-length strings. Space padded.

varchar2 (size)

Up to 32767 bytes in PLSQL.

Up to 4000 bytes in Oracle 9i.

Where size is the number of characters to store. Variable-length strings.

long

Up to 2 gigabytes.

Variable-length strings. (backward compatible)

raw

Up to 32767 bytes in PLSQL.

Up to 2000 bytes in Oracle 9i.

Variable-length binary strings

long raw

Up to 2 gigabytes.

Variable-length binary strings. (backward compatible)

date

A date between Jan 1, 4712 BC and Dec 31, 9999 AD.

timestamp (fractional seconds precision)

fractional seconds precision must be a number between 0 and 9. (default is 6)

Includes year, month, day, hour, minute, and seconds.

For example:
timestamp(6)

timestamp (fractional seconds precision) with time zone

fractional seconds precision must be a number between 0 and 9. (default is 6)

Includes year, month, day, hour, minute, and seconds; with a time zone displacement value.

For example:
timestamp(5) with time zone

timestamp (fractional seconds precision) with local time zone

fractional seconds precision must be a number between 0 and 9. (default is 6)

Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone.

For example:
timestamp(4) with local time zone

interval year
(year precision)
to month

year precision must be a number between 0 and 9. (default is 2)

Time period stored in years and months.

For example:
interval year(4) to month

interval day
(day precision)
to second (fractional seconds precision)

day precision must be a number between 0 and 9. (default is 2)

fractional seconds precision must be a number between 0 and 9. (default is 6)

Time period stored in days, hours, minutes, and seconds.

For example:
interval day(2) to second(6)

rowid

The format of the rowid is: BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

Fixed-length binary data. Every record in the database has a physical address or rowid.

urowid [size]

Up to 2000 bytes.

Universal rowid.

Where size is optional.

boolean

Valid in PLSQL, but this datatype does not exist in Oracle 9i.

nchar (size)

Up to 32767 bytes in PLSQL.

Up to 2000 bytes in Oracle 9i.

Where size is the number of characters to store. Fixed-length NLS string

nvarchar2 (size)

Up to 32767 bytes in PLSQL.

Up to 4000 bytes in Oracle 9i.

Where size is the number of characters to store. Variable-length NLS string

bfile

Up to 4 gigabytes.

File locators that point to a read-only binary object outside of the database

blob

Up to 4 gigabytes.

LOB locators that point to a large binary object within the database

clob

Up to 4 gigabytes.

LOB locators that point to a large character object within the database

nclob

Up to 4 gigabytes.

LOB locators that point to a large NLS character object within the database