|
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 and Examples
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.
PL/SQL
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
INTEGER
This ANSI datatype will be accepted by Oracle - it is actually a synonym for
NUMBER(38)
The FLOAT datatype
This ANSI datatype will be accepted by Oracle - Very similar to NUMBER it stores
zero, positive, and negative floating-point numbers
The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers
Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.
Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)
Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38
Confusingly the Units of measure for PRECISION vary according to the datatype.
For NUMBER data types: precision p = Number of Digits
For FLOAT data types: precision p = Binary Precision (multiply by 0.30103 to
convert)
{So FLOAT = FLOAT (126) = 126 x 0.30103 = approx 37.9 digits of precision.}
Example
The value 7,456,123.89 will display as follows NUMBER(9) 7456124 NUMBER(9,1) 7456123.9 NUMBER(*,1) 7456123.9 NUMBER(9,2) 7456123.89 NUMBER(6) [not accepted exceeds precision] NUMBER(7,-2) 7456100 NUMBER 7456123.89 FLOAT 7456123.89 FLOAT(12) 7456000.0
Oracle stores all numeric data in variable length format.
Storage space is therefore dependent on the length of all the individual values stored in the table. Precision and scale settings do not affect storage requirements. DATA_SCALE may appear to be truncating data, but Oracle still stores the exact values as input. DATA_PRECISION can be used to constrain input values.
It is possible to save storage space by having an application truncate a fractional value before inserting into a table, but you have to be very sure the logic and maths still make sense. (This is unlikely to be the case)
select
COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
from cols
where table_name = 'YOUR_TABLE';
Comparison with other RDBMS's
int10 | int6 | int1 | char(n) | blob | |
Oracle 9 | 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) |
When converting from one database to another also consider the maximum table name/column name size and maximum SQL statement size - these vary considerably between products and versions.
"The further information has to be able to circulate,the more work is required to disentangle the information from the context of its production" - Berg & Goorman (Int J Med Inf 56 51-60, 1999)
See Also
CREATE TABLE Syntax