Oracle data types

Character Datatypes

Syntax	Oracle 10g / 11g	Explanation
char(size)	Max size of 2000 bytes	Fixed-length strings. Space padded. Where size is the number of characters to store.
nchar(size)	Max size of 2000 bytes	Fixed-length NLS string Space padded. Where size is the number of characters to store.
nvarchar2(size)	Max size of 4000 bytes	Variable-length NLS string. Where size is number of characters to store.
varchar2(size)	Max size of 4000 bytes	Variable-length string. Where size is number of characters to store.
long	Max size of 2GB	Variable-length strings. (backward compatible)
raw	Max size of 2000 bytes	Variable-length binary strings
long raw	Max size of 2GB	Variable-length binary strings. (backward compatible)

Numeric Datatypes

Syntax	Oracle 10g / 11g	Explanation
number(p,s)	Precision range from 1 to 38. Scale range from -84 to 127.	Where p is the precision and s is the scale. Ex: number(7,2) 5 digits before decimal, 2 digits after decimal.
numeric(p,s)	Precision range from 1 to 38.	Where p is the precision and s is the scale. Ex: numeric(7,2) 5 digits before decimal, 2 digits after decimal.
dec(p,s)	Precision range from 1 to 38.	Where p is the precision and s is the scale. Ex: dec(3,1) 2 digits before decimal, 1 digit after decimal.
decimal(p,s)	Precision range from 1 to 38.	Where p is the precision and s is the scale. Ex: decimal(3,1) 2 digits before decimal, 1 digit after decimal.
float
integer
int
smallint
real
double precision

Date/Time Datatypes

Syntax	Oracle 10g / 11g	Explanation
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example:      timestamp(6)
timestamp (fractional seconds precision) with time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example:       timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example:      timestamp(4) with local time zone
interval year (year precision) to month	year precision is the number of digits in the year. (default is 2)	Time period stored in years and months. For example:      interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example:      interval day(2) to second(6)

Large Object (LOB) Datatypes

Syntax	Oracle 10g	Oracle 11g	Explanation
bfile	Maximum file size of 232-1 bytes.	Maximum file size of 264-1 bytes.	File locators that point to a binary file on the server file system (outside the DB).
blob	Store up to (4 GB - 1) * (the value of the CHUNK parameter of LOB storage).		Stores unstructured binary large objects.
clob	Store up to (4 GB - 1) * (the value of the CHUNK parameter of LOB storage) of character data.		Stores single-byte and multi-byte character data.
nclob	Store up to (4 GB - 1) * (the value of the CHUNK parameter of LOB storage) of character text data.		Stores unicode data.

Rowid Datatypes

Syntax	Oracle 10g / 11g	Explanation
rowid	The format of the rowid is: BBBBBBB.RRRR.FFFFF BBBBBBB is the block in the database file RRRR is the row in the block FFFFF is the DB file	Fixed-length binary data. Every record in the DB has a physical address or rowid.
urowid(size)		Universal rowid.  Where size is optional.

Notes and Examples

VARCHAR2
Storing character data as Varchar2 will save space:

Store 'SMITH' not 'SMITH     '

Oracle9i and above allow Varchar2 columns to be defined as a number of bytes VARCHAR2(50 BYTE) or a number of characters VARCHAR2(50 CHAR), the latter is useful if the database is ever converted to run a double-byte character set (such as Japanese), you won't have to edit the column sizes. The default measure, normally BYTE, is set with nls_length_semantics.

CHAR
Over time, when varchar2 columns are updated they will sometimes create chained rows, 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 using the PL/SQL datatype: PLS_INTEGER for better performance.

LONG
Use 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

Storing Varchar2 Data

For VARCHAR2 variable whose maximum size is less than 2,000 bytes (or for a CHAR variable), PL/SQL allocates enough memory for the maximum size at compile time.

For a VARCHAR2 whose maximum size is 2,000 bytes or more, PL/SQL allocates enough memory to store the actual value at run time. In this way, PL/SQL optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use.

For example, if you assign the same 500-byte value to VARCHAR2(1999 BYTE) and VARCHAR2(2000 BYTE) variables, PL/SQL allocates 1999 bytes for the former variable at compile time and 500 bytes for the latter variable at run time.

Storing Numeric Data

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 business logic makes sense.

Select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
From cols Where table_name = 'Your_Table';

A common space-saving trick is storing boolean values as an Oracle CHAR, rather than NUMBER:

Create TABLE my_demo (accountcode NUMBER, postableYN CHAR check (postableYN in (0,1)) );

-- Standard Boolean values: False=0 and True=1
Insert into my_demo values(525, '1');
Insert into my_demo values(526, '0');

Select accountcode, decode(postableYN,1,'True',0,'False') FROM my_demo;
-- or in French:
Select accountcode, decode(postableYN,1,'Vrai',0,'Faux') FROM my_demo;

Comparison with other RDBMS's

	int10	int6	int1	char(n)	blob	XML
Oracle 11	NUMBER(10)	NUMBER(6)	NUMBER(1)	VARCHAR2(n)	BLOB	XMLType
SQL Server 2005	NUMERIC(10)	NUMERIC(6)	TINYINT	VARCHAR(n)	IMAGE	XML
Sybase 10	NUMERIC(10)	NUMERIC(6)	NUMERIC(1)	VARCHAR(n)	IMAGE
MS Access (Jet)	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
RedBrick	integer	int	int	char(n)	char(1024)
INGRES	INTEGER	INTEGER	INTEGER	VARCHAR(n)	VARCHAR(1500)

Also consider the maximum length of a table name (or column name) and the maximum size of an SQL statement - these limits vary considerably between products and versions.