How-to: Oracle Datatypes

Data types for Oracle 8 to Oracle 12c.

Datatype Description Max Size:
Oracle 8
Max Size:
Oracle 9i/10g/11g
Max Size:
Oracle
12c
Max Size:
PL/SQL
PL/SQL
Subtypes/
Synonyms
VARCHAR2(size) Variable length character string having maximum length size bytes.
You must specify size.
n.b. max is only 32,767 bytes or characters if MAX_STRING_SIZE = EXTENDED
4,000 bytes
minimum is 1
4,000 bytes
minimum is 1

Maximum size is:

32,767 bytes or characters if MAX_STRING_SIZE = EXTENDED

4,000 bytes or characters if MAX_STRING_SIZE = STANDARD

32,767 bytes
minimum is 1
STRING
VARCHAR
NVARCHAR2(size) Variable length national character set string having maximum length size bytes.
You must specify size
4,000 bytes
minimum is 1
4,000 bytes
minimum is 1

32,767 bytes if MAX_STRING_SIZE = EXTENDED

4,000 bytes if MAX_STRING_SIZE = STANDARD

The no. of bytes can be up to 2x size for AL16UTF16 encoding and 3x size for UTF8 encoding. (determined by the national character set definition.)

32,767 bytes
minimum is 1
STRING
VARCHAR
VARCHAR Now deprecated (provided for backward compatibility only)
VARCHAR is a synonym for VARCHAR2 but this usage might 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… 2,000 bytes
Default and minimum size is 1 byte.
2,000 bytes
Default and minimum size is 1 byte.
2,000 bytes
Default and minimum size is 1 byte.
32,767 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… 2,000 bytes
Default and minimum size is 1 byte.
2,000 bytes
Default and minimum size is 1 byte.
2,000 bytes
Default and minimum size is 1 byte.
32,767 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

Floating-Point:
DOUBLE PRECISION FLOAT

integers:
INTEGER
INT
SMALLINT

BOOLEAN
REAL

BINARY_FLOAT A 32-bit, single-precision floating-point number data type. Each BINARY_FLOAT value requires 4 bytes. Supports the special values infinity and NaN (not a number). -

Magnitude:
1.17549E-38F

3.40282E+38F

Magnitude:
1.17549E-38F

3.40282E+38F
   
BINARY_DOUBLE A 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes. Supports the special values infinity and NaN (not a number). - Magnitude:
2.22507485850720E-308

1.79769313486231E+308
Magnitude:
2.22507485850720E-308

1.79769313486231E+308
   
BOOLEAN True, False or NULL n/a
Use either Number or CHAR
n/a
Use either Number or CHAR
n/a
Use either Number or CHAR
BOOLEAN  
PLS_INTEGER signed integers
PLS_INTEGER values require less storage and provide better performance than NUMBER values.
PL/SQL only PL/SQL only PL/SQL only magnitude range is -2,147,483,647 .. 2,147,483,647

SIMPLE_INTEGER
a sub-type of PLS_INTEGER that is always NOT NULL. (11g)

BINARY_INTEGER signed integers (older slower version of PLS_INTEGER)   - - magnitude range is -2,147,483,647 .. 2,147,483,647 NATURAL
NATURALN
POSITIVE
POSITIVEN
SIGNTYPE
LONG Character data of variable length (A bigger version the VARCHAR2 datatype) 2 Gigabytes 2 Gigabytes - but now deprecated (provided for backward compatibility only). 2 Gigabytes - but now deprecated (provided for backward compatibility only). 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, 9999 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) 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) 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) 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 can be 0 to 9. (default = 2)

fractional_seconds_precision can be 0 to 9. (default = 6)

day_precision can be 0 to 9. (default = 2)

fractional_seconds_precision can 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 2,000 bytes Maximum size is 2,000 bytes

Maximum size is:

32,767 bytes if MAX_STRING_SIZE = EXTENDED

2,000 bytes if MAX_STRING_SIZE = STANDARD

32767 bytes  
LONG RAW Raw binary data of variable length. (not intrepreted by PL/SQL) 2 Gigabytes. 2 Gigabytes - but now deprecated (provided for backward compatibility only) 2 Gigabytes - but now deprecated (provided for backward compatibility only) 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.)
10 bytes 10 bytes Base 64 string representing the unique address of a row in its table. This data type is 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 The maximum size and default is 4,000 bytes The maximum size and default is 4,000 bytes

Base 64 string representing the logical address of a row of an index-organized table.

The optional size is the size of a column of type UROWID.

The maximum size and default is 4,000 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 4 Gigabytes

8 TB to 128 TB
(4 Gigabytes - 1) * (database block size)

8 TB in Oracle 9i/10g

Maximum size is (4 gigabytes - 1) * (database block size).    
NCLOB National Character Large Object 4 Gigabytes

8 TB to 128 TB
(4 Gigabytes - 1) * (database block size)

8 TB in Oracle 9i/10g

Maximum size is (4 gigabytes - 1) * (database block size).    
BLOB Binary Large Object 4 Gigabytes

8 TB to 128 TB
(4 Gigabytes - 1) * (database block size)

8 TB in Oracle 9i/10g

Maximum size is (4 gigabytes - 1) * (database block size).    
BFILE pointer to binary file on disk 4 Gigabytes

4 Gigabytes

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

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

64 K in Oracle 9i/10g

Now deprecated. Use binary XML storage instead. 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 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

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:

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.

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 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) .

Example

    The value 7456123.89 will display as follows

NUMBER        7456123.89 
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 
FLOAT         7456123.89 
FLOAT(12)     7456000.0

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

Varchar2 memory allocation

For a CHAR variable, PL/SQL allocates at compile time enough memory for the maximum size.

For a VARCHAR2 variable, memory allocation depends on the variables maximum size:

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(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

You can store boolean values as a one digit 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;

An old space-saving trick was storing 'Y' and 'N', this 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.

Comparison with other RDBMS's

  int10 int6 int1 char(n) blob XML
Oracle 11 NUMBER(10) NUMBER(6) NUMBER(1) VARCHAR2(n) BLOB XMLType
MS SQL Server 2008 NUMERIC(10) NUMERIC(6) TINYINT VARCHAR(max) IMAGE XML
MS SQL Server 2005 NUMERIC(10) NUMERIC(6) TINYINT VARCHAR(n) IMAGE XML
Sybase system 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.

“Computer programmers do it byte by byte”

Related

CREATE TABLE
Data Types - Oracle 11g Database SQL Language Reference
for older versions see the Oracle 'Database Concepts' Manuals at docs.oracle.com


 
Copyright © 1999-2024 SS64.com
Some rights reserved