Tutorial by: Date: 20160415 03:59:08
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. Fixedlength 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. Fixedlength 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. Variablelength 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. Variablelength string. 
long 
Maximum size of 2GB. 
Maximum size of 2GB. 
Maximum size of 2GB. 
Variablelength strings. (backward compatible) 
raw 
Maximum size of 2000 bytes. 
Maximum size of 2000 bytes. 
Maximum size of 2000 bytes. 
Variablelength binary strings 
long raw 
Maximum size of 2GB. 
Maximum size of 2GB. 
Maximum size of 2GB. 
Variablelength binary strings. (backward compatible) 
Numeric Datatypes
The following are the Numeric Datatypes in Oracle/PLSQL:
Data Type Syntax 
Oracle 9i 
Oracle 10g 
Oracle 11g 
Explanation 
number(p,s) 
Precision can range from 1 to 38. 
Precision can range from 1 to 38. 
Precision can range from 1 to 38. 
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) 
Precision can range from 1 to 38. 
Precision can range from 1 to 38. 
Precision can range from 1 to 38. 
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) 
Precision can range from 1 to 38. 
Precision can range from 1 to 38. 
Precision can range from 1 to 38. 
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) 
Precision can range from 1 to 38. 
Precision can range from 1 to 38. 
Precision can range from 1 to 38. 
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 
Oracle 9i 
Oracle 10g 
Oracle 11g 
Explanation 
date 
A date between Jan 1, 4712 BC and Dec 31, 9999 AD. 
A date between Jan 1, 4712 BC and Dec 31, 9999 AD. 
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) 
fractional seconds precision must be a number between 0 and 9. (default is 6) 
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 (fractional seconds precision) with time zone 
fractional seconds precision must be a number between 0 and 9. (default is 6) 
fractional seconds precision must be a number between 0 and 9. (default is 6) 
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 (fractional seconds precision) with local time zone 
fractional seconds precision must be a number between 0 and 9. (default is 6) 
fractional seconds precision must be a number between 0 and 9. (default is 6) 
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: 
interval year 
year precision is the number of digits in the year. (default is 2) 
year precision is the number of digits in the year. (default is 2) 
year precision is the number of digits in the year. (default is 2) 
Time period stored in years and months. For example: 
interval day 
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) 
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) 
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: 
Large Object (LOB) Datatypes
The following are the LOB Datatypes in Oracle/PLSQL:
Data Type Syntax 
Oracle 9i 
Oracle 10g 
Oracle 11g 
Explanation 
bfile 
Maximum file size of 4GB. 
Maximum file size of 2^{32}1 bytes. 
Maximum file size of 2^{64}1 bytes. 
File locators that point to a binary file on the server file system (outside the database). 
blob 
Store up to 4GB of binary data. 
Store up to (4 gigabytes 1) * (the value of the CHUNK parameter of LOB storage). 
Store up to (4 gigabytes 1) * (the value of the CHUNK parameter of LOB storage). 
Stores unstructured binary large objects. 
clob 
Store up to 4GB of character data. 
Store up to (4 gigabytes 1) * (the value of the CHUNK parameter of LOB storage) of character data. 
Store up to (4 gigabytes 1) * (the value of the CHUNK parameter of LOB storage) of character data. 
Stores singlebyte and multibyte character data. 
nclob 
Store up to 4GB of character text data. 
Store up to (4 gigabytes 1) * (the value of the CHUNK parameter of LOB storage) of character text data. 
Store up to (4 gigabytes 1) * (the value of the CHUNK parameter of LOB storage) of character text data. 
Stores unicode data. 
Rowid Datatypes
The following are the Rowid Datatypes in Oracle/PLSQL:
Data Type Syntax 
Oracle 9i 
Oracle 10g 
Oracle 11g 
Explanation 
rowid 
The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; 
The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; 
The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; 
Fixedlength binary data. Every record in the database has a physical address or rowid. 
urowid(size) 



Universal rowid. Where size is optional. 