Oracle Datatypes

Datatype summary for Oracle 7, 8 & 9

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

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