Datatype summary for Oracle 7, 8 & 9
Datatype |
Description |
Max Size: |
Max Size: |
Max Size: |
Max Size: |
PL/SQL |
VARCHAR2(size) |
Variable length character string having maximum length size bytes. |
2000 bytes |
4000 bytes |
4000 bytes |
32767 bytes |
STRING |
NVARCHAR2(size) |
Variable length national character set string having maximum length
size bytes. |
N/A |
4000 bytes |
4000 bytes |
32767 bytes |
STRING |
VARCHAR |
Now deprecated - VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions. |
- |
- |
- |
|
|
CHAR(size) |
Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A100, B102... |
255 bytes |
2000 bytes |
2000 bytes |
32767 bytes |
CHARACTER |
NCHAR(size) |
Fixed length national character set data of length size bytes. This should be used for fixed length data. Such as codes A100, B102... |
N/A |
2000 bytes |
2000 bytes |
32767 bytes |
|
NUMBER(p,s) |
Number having precision p and scale s. |
The precision p can range from 1 to 38. |
The precision p can range from 1 to 38. |
The precision p can range from 1 to 38. |
Magnitude |
fixed-point numbers: |
PLS_INTEGER |
signed integers |
PL/SQL only |
PL/SQL only |
PL/SQL only |
magnitude range is -2147483647 .. 2147483647 |
|
BINARY_INTEGER |
signed integers (older slower version of PLS_INTEGER) |
|
|
|
magnitude range is -2147483647 .. 2147483647 |
|
LONG |
Character data of variable length (A bigger version the VARCHAR2 datatype) |
2 Gigabytes |
2 Gigabytes |
2 Gigabytes - but now deprecated |
32760 bytes |
|
DATE |
Valid date range |
from January 1, 4712 BC to December 31, 4712 AD. |
from January 1, 4712 BC to December 31, 9999 AD. |
from January 1, 4712 BC to December 31, 9999 AD. |
from January 1, 4712 BC to December 31, 9999 AD. |
|
TIMESTAMP (fractional_seconds_precision) |
the number of digits in the fractional part of the SECOND datetime field. |
- |
- |
Accepted values of fractional_seconds_precision are 0 to 9. (default = 6) |
|
|
TIMESTAMP (fractional_seconds_precision) WITH {LOCAL} TIMEZONE |
As above with time zone displacement value |
- |
- |
Accepted values of fractional_seconds_precision are 0 to 9. (default = 6) |
|
|
INTERVAL YEAR (year_precision) TO MONTH |
Time in years and months, where year_precision is the number of digits in the YEAR datetime field. |
- |
- |
Accepted values are 0 to 9. (default = 2) |
|
|
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision) |
Time in days, hours, minutes, and seconds. |
- |
- |
day_precision may be 0 to 9. (default = 2) fractional_seconds_precision may be 0 to 9. (default = 6) |
|
|
RAW(size) |
Raw binary data of length size bytes. |
Maximum size is 255 bytes. |
Maximum size is 2000 bytes |
Maximum size is 2000 bytes |
32767 bytes |
|
LONG RAW |
Raw binary data of variable length. (not intrepreted by PL/SQL) |
2 Gigabytes. |
2 Gigabytes. |
2 Gigabytes - but now deprecated |
32760 bytes |
|
ROWID |
Hexadecimal string representing the unique address of a row in its table. |
|
|
|
Hexadecimal string representing the unique address of a row in its table. |
|
UROWID |
Hex string representing the logical address of a row of an index-organized table |
N/A |
The maximum size and default is 4000 bytes |
The maximum size and default is 4000 bytes |
universal rowid - Hex string representing the logical address of a row of an index-organized table, either physical, logical, or foreign (non-Oracle) |
See CHARTOROWID and the package: DBMS_ROWID |
MLSLABEL |
Binary format of an operating system label.This datatype is used with Trusted Oracle7. |
|
|
|
|
|
CLOB |
Character Large Object |
4Gigabytes |
4Gigabytes |
4Gigabytes |
4Gigabytes |
|
NCLOB |
National Character Large Object |
|
4Gigabytes |
4Gigabytes |
4Gigabytes |
|
BLOB |
Binary Large Object |
|
4Gigabytes |
4Gigabytes |
4Gigabytes |
|
BFILE |
pointer to binary file on disk |
|
4Gigabytes |
4Gigabytes |
The size of a BFILE is system dependent but cannot exceed four gigabytes (2**32 - 1 bytes). |
|
Notes (pro's & cons)
INTEGER
This ANSI datatype will be accepted by Oracle - it is actually a
synonym for NUMBER(38)
VARCHAR2:
Storing character data as Varchar2 will save space:
Store 'SMITH' not 'SMITH '
CHAR:
Over time, when varchar2 columns are updated they will sometimes
create chained rows - because CHAR columns are fixed width they are
not affected by this - so less DBA effort is required to maintain performance.
NUMBER
When retrieving data for a NUMBER column, consider (if you can) using
the PL/SQL datatype: PLS_INTEGER for better performance.
LONG
You should start using BLOB instead of LONG
Comparison with other RDBMS's
|
int10 |
int6 |
int1 |
char(n) |
blob |
Oracle 8 |
NUMBER(10) |
NUMBER(6) |
NUMBER(1) |
VARCHAR2(n) |
BLOB |
Sybase system 10 |
NUMERIC(10) |
NUMERIC(6) |
NUMERIC(1) |
VARCHAR(n) |
IMAGE |
MS Access 97 |
Long Int or Double |
Single |
Byte |
TEXT(n) |
LONGBINARY |
TERADATA |
INTEGER |
DECIMAL(6) |
DECIMAL(1) |
VARCHAR(n) |
VARBYTE(20480) |
DB2 |
INTEGER |
DECIMAL(6) |
DECIMAL(1) |
VARCHAR(n) |
VARCHAR(255) |
RDB |
INTEGER |
DECIMAL(6) |
DECIMAL(1) |
VARCHAR(n) |
LONG VARCHAR |
INFORMIX |
INTEGER |
DECIMAL(6) |
DECIMAL(1) |
VARCHAR(n) |
BYTE |
SYBASE |
NUMERIC(10) |
NUMERIC(6) |
NUMERIC(1) |
VARCHAR(n) |
IMAGE |
MS SQL Server 6.0 |
NUMERIC(10) |
NUMERIC(6) |
NUMERIC(1) |
VARCHAR(n) |
IMAGE |
RedBrick |
integer |
int |
int |
char(n) |
char(1024) |
INGRES |
INTEGER |
INTEGER |
INTEGER |
VARCHAR(n) |
VARCHAR(1500) |