Oracle Topics

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

Oracle 9i

Oracle 10g

Oracle 11g

Explanation

char(size)

Maximum size of 2000 bytes.

Maximum size of 2000 bytes.

Maximum size of 2000 bytes.

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

nchar(size)

Maximum size of 2000 bytes.

Maximum size of 2000 bytes.

Maximum size of 2000 bytes.

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

nvarchar2(size)

Maximum size of 4000 bytes.

Maximum size of 4000 bytes.

Maximum size of 4000 bytes.

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

varchar2(size)

Maximum size of 4000 bytes.

Maximum size of 32KB in PLSQL.

Maximum size of 4000 bytes.

Maximum size of 32KB in PLSQL.

Maximum size of 4000 bytes.

Maximum size of 32KB in PLSQL.

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

long

Maximum size of 2GB.

Maximum size of 2GB.

Maximum size of 2GB.

Variable-length strings. (backward compatible)

raw

Maximum size of 2000 bytes.

Maximum size of 2000 bytes.

Maximum size of 2000 bytes.

Variable-length binary strings

long raw

Maximum size of 2GB.

Maximum size of 2GB.

Maximum size of 2GB.

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: