totn Oracle / PLSQL

Oracle / PLSQL: Data Types

The following is a list of datatypes available in Oracle/PLSQL, which includes character, numeric, date/time, LOB and rowid datatypes.

Character Datatypes

The following are the Character Datatypes in Oracle/PLSQL:

Data Type Syntax Explanation
char(size) Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size) Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size) Where size is the number of characters to store. Variable-length NLS string.
varchar2(size) Where size is the number of characters to store. Variable-length string.
long Variable-length strings. (backward compatible)
raw Variable-length binary strings
long raw Variable-length binary strings. (backward compatible)

Numeric Datatypes

The following are the Numeric Datatypes in Oracle/PLSQL:

Data Type Syntax Explanation
number(p,s)

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.

numeric(p,s)

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.

float  
dec(p,s)

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)

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.

integer  
int  
smallint  
real  
double precision  

Date/Time Datatypes

The following are the Date/Time Datatypes in Oracle/PLSQL:

Data Type Syntax Explanation
date  
timestamp (fractional seconds precision)

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

For example:
timestamp(6)

timestamp (fractional seconds precision) with time zone

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

Time period stored in years and months.

For example:
interval year(4) to month

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

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

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

Large Object (LOB) Datatypes

The following are the LOB Datatypes in Oracle/PLSQL:

Data Type Syntax Explanation
bfile File locators that point to a binary file on the server file system (outside the database).
blob Stores unstructured binary large objects.
clob Stores single-byte and multi-byte character data.
nclob Stores Unicode data.

Rowid Datatypes

The following are the Rowid Datatypes in Oracle/PLSQL:

Data Type Syntax Explanation
rowid Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size)

Universal rowid.

Where size is optional.