|
|
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 |
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 |
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. |
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. |
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. |
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 | N/A - Obsolete | 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. (in Oracle7 = 4712 AD) |
||
| 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 | 32767 bytes | ||
| LONG RAW | Raw binary data of variable length. (not intrepreted by PL/SQL) | 2 Gigabytes. | 2 Gigabytes. | N/A - Obsolete | 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 | 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 | 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 | NUMBER(10) | NUMBER(6) | NUMBER(1) | VARCHAR2(n) | BLOB |
| Sybase system 10 | NUMERIC(10) | NUMERIC(6) | NUMERIC(1) | VARCHAR(n) | IMAGE |
| MS Access | Long Int or Double | Single | Byte | TEXT(n) | LONGBINARY |
| ODBC/TERADATA | INTEGER | DECIMAL(6) | DECIMAL(1) | VARCHAR(n) | VARBYTE(20480) |
| ODBC/DB2 | INTEGER | DECIMAL(6) | DECIMAL(1) | VARCHAR(n) | VARCHAR(255) |
| ODBC/RDB | INTEGER | DECIMAL(6) | DECIMAL(1) | VARCHAR(n) | LONG VARCHAR |
| ODBC/INFORMIX | INTEGER | DECIMAL(6) | DECIMAL(1) | VARCHAR(n) | BYTE |
| ODBC/SYBASE | NUMERIC(10) | NUMERIC(6) | NUMERIC(1) | VARCHAR(n) | IMAGE |
| ODBC/MS SQL Server 6.0 | NUMERIC(10) | NUMERIC(6) | NUMERIC(1) | VARCHAR(n) | IMAGE |
| RedBrick | integer | int | int | char(n) | char(1024) |
| ODBC/INGRES | INTEGER | INTEGER | INTEGER | VARCHAR(n) | VARCHAR(1500) |
