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