Binary format of an operating system label.This datatype is
used with Trusted Oracle7.

CLOB

Character Large Object

4 Gigabytes

8 TB

8 TB to 128 TB

(4 Gigabytes - 1) * (database block size)

NCLOB

National Character Large Object

4 Gigabytes

8 TB

8 TB to 128 TB

(4 Gigabytes - 1) * (database block size)

BLOB

Binary Large Object

4 Gigabytes

8 TB

8 TB to 128 TB

(4 Gigabytes - 1) * (database block size)

BFILE

pointer to binary file on disk

4 Gigabytes

8 TB

8 TB to 128 TB

(4 Gigabytes - 1) * (database block size)

XMLType

A system-defined type for storing binary XML data stored internally as a CLOB (starting with 11gR1 stored as a BLOB)

-

64K

In 11gR1 the limit is 2G/4G depending on DB character set.

Populate with XML from a CLOB, BLOB or VARCHAR2.

or query from another XMLType column.

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.

If you create a column as
Varchar2 (50) but only store 10 bytes, then Oracle will only save 10 bytes to disk.
This does not mean that you should just create Varchar2 (4000) columns 'just in case the space is needed', that is a really bad idea which will reduce the performance and maintainability of your application.

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 NUMBERNUMBER(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 NUMBERNUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)

PRECISION and SCALE

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. SCALE might appear to be truncating data, but Oracle does still store the entire number as typed.

PRECISION can be used to constrain input values.

If a column is defined like CREATE TABLE demo ( empid number(4) ); then an attempt to insert a 5 digit number 10045 will fail. If you need to constrain values to a specific range of numbers (e.g. <85 rather than the round number <100), then add a column constraint .

Note that the Units of measure for PRECISION are different if the datatype is FLOAT:

In theory it is possible to save storage space by having an application truncate a fractional value before inserting into a table, but you would have to be very sure the business logic made sense. This would have to be done at the application level.

If a table is created without any Precision/Scale CREATE TABLE demo ( empid number ); then the column will simply store values as given. When no scale is specified, the scale is zero.

Function parameters

Oracle does not allow a specific precision for function parameters, only type of the variable. This means that a parameter defined as integer or number(38) can accept fractional values. if you want to have an integer passed to a procedure/function, use pls_integer instead of integer.

Alternatively reassigning to a variable within the procedure will force Oracle to implicitly convert the datatype and precision to match the variable, thie requires a slightly different definition, e.g. number(37) .

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

Boolean data

An old space-saving trick was to store boolean values as an Oracle CHAR, rather than NUMBER. Storing Y and N requires no more (but also no less) than storing 0 and 1 but saves you the effort of writing a decode statement to display it. Of course if/when the application has to support other languages then you will need to add the decode anyway.