Datatype summary for Oracle 7, 8 & 9
| Datatype | Description | Max Size: Oracle 7 |
Max Size: Oracle 8 |
Max Size: Oracle 9 |
Max Size: PL/SQL |
PL/SQL Subtypes/ Synonyms |
| VARCHAR2(size) | Variable length character string having maximum length size
bytes. You must specify size |
2000 bytes minimum is 1 |
4000 bytes minimum is 1 |
4000 bytes minimum is 1 |
32767 bytes minimum is 1 |
STRING VARCHAR |
| NVARCHAR2(size) | Variable length national character set string having maximum
length size bytes. You must specify size |
N/A | 4000 bytes minimum is 1 |
4000 bytes minimum is 1 |
32767 bytes minimum is 1 |
STRING VARCHAR |
| 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 Default and minimum size is 1 byte. |
2000 bytes Default and minimum size is 1 byte. |
2000 bytes Default and minimum size is 1 byte. |
32767 bytes Default and minimum size is 1 byte. |
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 Default and minimum size is 1 byte. |
2000 bytes Default and minimum size is 1 byte. |
32767 bytes Default and minimum size is 1 byte. |
|
| NUMBER(p,s) | Number having precision p and scale s. | The precision p can range from 1 to 38. The scale s can range from -84 to 127. |
The precision p can range from 1 to 38. The scale s can range from -84 to 127. |
The precision p can range from 1 to 38. The scale s can range from -84 to 127. |
Magnitude 1E-130 .. 10E125 maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits The scale s can range from -84 to 127. For floating point don't specify p,s REAL has a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits |
fixed-point numbers: DEC DECIMAL NUMERIC integers: INTEGER INT SMALLINT floating-point: DOUBLE PRECISION FLOAT REAL |
| PLS_INTEGER | signed integers PLS_INTEGER values require less storage and provide better performance than NUMBER values. So use PLS_INTEGER where you can! |
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 Note this is smalller than the maximum width of a LONG column |
|
| 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. (in Oracle7 = 4712 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 is the maximum number of digits in 'DAY' fractional_seconds_precision is the max number of fractional digits in the SECOND field. |
- | - | 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. You must specify size for a RAW value. |
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 Note this is smalller than the maximum width of a LONG RAW column |
|
| ROWID | Hexadecimal string representing the unique address of a row
in its table. (primarily for values returned by the ROWID pseudocolumn.) |
Hexadecimal string representing the unique address of a row
in its table. (primarily for values returned by the ROWID pseudocolumn.) |
||||
| 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) |