Oracle Data Types

Each column within a table is defined as an Oracle data type and instructs Oracle how to store the data, the SQL data types can be categorized by the following

Character Data Types
char can have a length specification after the data type (default = 1) once created they are fixed. The column will always use the amount of space specified and will use padding if the values are less than the specified amount.
varchar2 and varchar

can have a length specification after the data type, this data type is more efficient than a char as the space used will only be the length of the data entered, up to a maximum value that was stated during creation.

varchar is the same as varchar2

varchar2, varchar have a limit of 2,000 characters

nchar and nvarchar2

can be used to use character data from many different languages, including chinese or japanese, they require 2 bytes of storage for each character. Oracle will allocate the appropriate amount of storage required when you create the column.

nchar and nvarchar2 have a limit of 4,000

String is a subtype of varchar2 and stores variable length strings in bytes and characters up to 4,000 characters in length.
long The above character types have limits were the limit of a long is 2GB of data in a single column. However long has now been superceded by clob and nclob.
blob, clob and nclob

blob holds binary information (images), clob holds character information (text) and nclob holds double byte information (languages text like nvarchar2).

They can hold upto 128TB's of data in a single column in oracle 10g.

See Large OBjects for more information


a bfile data type points to a storage location outside the oracle database internal storage, it can point to jpg's, giff's ,etc

See Large OBjects for more information

Numeric Data Types

basic number data type, it has up to 38 digits of precision. the syntax is number(p,s)

p = precision upto 38
s = can be positive (number of digits to the right of the decimal point) or negative (number of digits to the left of the decimal point)

binary You can used either binary_float or binary_double these data types have no limits however they may lose some accuracy.
float data type for ANSI compliance.
Date Data Types
date Includes day, month, year, hour, minute and seconds
timestamp includes the above and fractional seconds.
timestamp with local timezone same as timestamp but includes the local timezone of either the database server or client.
interval year to month can store an interval of years and months
interval day to second can store an interval of days, hours, minutes and seconds.
Raw Data Types
raw used to keep the data in its raw format as oracle normally converts a value to an internal representation for storage.
long raw used to keep the data in its raw format as oracle normally converts a value to an internal representation for storage.
Row Data Type
hexadecimal string representing the unique address of a row in it's table
hexadecimal string representing the logical address of a row of an index-organized table
Other Data Types
refcursor stores a cursor returned by a PL/SQL block, which can contain an array of a structure. The structure can be dynamic and may implement a structure defined in the data catalog or in query.
own data types you have the ability to create your own data types and object types
special data types there a number of special data types that deal with XML, spatial data and media data.