The following is a list of datatypes available in Oracle and PLSQL.
We've tried to differentiate between datatypes available in Oracle 8i
versus Oracle 9i.
Data
Type
Syntax |
Oracle
8i |
Oracle
9i |
Explanation
(if applicable) |
| dec(p, s) |
The maximum precision is
38 digits. |
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. |
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. |
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. |
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 8i.
|
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 8i.
|
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. |
Up to 2 gigabytes. |
Variable-length strings. (backward
compatible) |
| raw |
Up to 32767 bytes in
PLSQL.
Up to 2000 bytes in Oracle 8i.
|
Up to 32767 bytes in PLSQL.
Up to 2000 bytes in Oracle 9i. |
Variable-length binary strings |
| long raw |
Up to 2 gigabytes. |
Up to 2 gigabytes. |
Variable-length binary strings.
(backward compatible) |
| 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. |
|
| timestamp (fractional seconds
precision) |
Not supported in Oracle 8i. |
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 |
Not supported in Oracle 8i. |
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 |
Not supported in Oracle 8i. |
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 |
Not supported in Oracle 8i. |
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) |
Not supported in Oracle 8i. |
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.
|
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. |
Up to 2000 bytes. |
Universal rowid.
Where size is optional.
|
| boolean |
Valid in PLSQL, but this datatype
does not exist in Oracle 8i. |
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 8i
|
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 8i.
|
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. |
Up to 4 gigabytes. |
File locators that point to a
read-only binary object outside of the database |
| blob |
U p to 4 gigabytes. |
Up to 4 gigabytes. |
LOB locators that point to a large
binary object within the database |
| clob |
Up to 4 gigabytes. |
Up to 4 gigabytes. |
LOB locators that point to a large
character object within the database |
| nclob |
Up to 4 gigabytes. |
Up to 4 gigabytes. |
LOB locators that point to a large
NLS character object within the database |