Oracle SQL Data Types

Oracle database provide built-in SQL data types also you can make user define types for letter use as data types. Every database define a column data type for each and every tables.

SQL data types are two type scaler type or non scalar type. Scalar SQL data types haven't internal components. It's like linear data types. Whereas non scalar SQL data types have internal component to store multiple values and easily you can manipulate the data.

Oracle SQL Data types

Built-in Oracle SQL data types are following,

SQL Data Types

Number Datatypes

Following are numeric data types in SQL.

Datatype Description
NUMBER [ ( precision [, scale ] ) NUMBER data type use to store numeric data.
NUMBER data type have precision and scale.
Storage Range: Precision range(p) : 1 to 38 and Scale range(s) : -84 to 127
NUMBER Subtypes: This sub type supported ANSI, DB2, and SQL data type define different type storage range.
ANSI, DB2 Datatypes Maximum Precision Oracle Data types
INTEGER 38 digits NUMBER(p,0)
INT 38 digits
SMALLINT 38 digits
FLOAT [ (size) ] 126 binary digits FLOAT(126)
DOUBLE PRECISION 126 binary digits FLOAT(126)
REAL 63 binary digits FLOAT(63)
DECIMAL[(precision [, scale ])] 38 digits NUMBER(p,s)
NUMERIC[(precision [, scale ])] 38 digits
FLOAT [ ( precision ) ] FLOAT data type is subtype of NUMBER datatype.
Storage Range: Precision range(p): 1 to 126
Example: col1 FLOAT(2)
BINARY_FLOAT BINARY_FLOAT datatype use binary precision (32-bit).
This data type requires 5 bytes including length byte.
Advantages: Arithmetic calculations fast and reduces the storage requirements.
BINARY_DOUBLE BINARY_DOUBLE datatype use double binary precision (64-bit).
This data type requires 9 bytes including length byte.

Character Datatypes

Character Data type use to store alphabetic/alphanumeric, following are character data types in Oracle SQL.

Datatype Description Storage(Maximum)
CHAR [ (size) ] CHAR data type use to store character data within predefined length. 2000 bytes
NCHAR [ (size) ] NCHAR data type use to store national character data within predefined length. 2000 bytes
VARCHAR2(size) VARCHAR2 data type use to store variable strings data within predefined length.
You have to must specify the size of VARCHAR2 datatype.
VARCHAR2 Subtypes: This sub type define same length value.
Sub Data type Description
VARCHAR(size) You can also use this data type.
4000 bytes
NVARCHAR2(size) NVARCHAR2 data type use to store Unicode string data within predefined length.
You have to must specify the size of NVARCHAR2 datatype.		 4000 bytes

LONG and ROW Datatypes

LONG and ROW data type store variable strings data within predefined length, This datatypes use for backward compatibility. Following are LONG and ROW datatypes in Oracle SQL.

Datatype Description Storage(Maximum)
RAW(size) RAW data type use to store binary data such as image, graphics etc.
You have to must specify the size of RAW column data type.		 2000 bytes
LONG LONG data type use to store variable strings data within predefined length, This data type use for backward compatibility. Please use CLOB instead of LONG type. upto 2 gigabytes
LONG RAW LONG RAW data type same as LONG type use to store variable strings data within predefined length, This data type use for backward compatibility. Please use BLOB instead of LONG RAW type. upto 2 gigabytes

ROWID Datatypes

ROWID data type represent actual storage address of a row. Following are ROWID datatypes in Oracle SQL.

Datatype Description Storage(Maximum)
ROWID ROWID data type represent actual storage address of a row. and table index identities as a logical rowid. This data type use for backward compatibility. Recommended to use UROWID data type.
UROWID[(size)] UROWID data type identify as universal rowid. Same as ROWID type and use newer developing applications use UROWID data type.
You can specify optional size of UROWID column type.		 4000 bytes

Date/Time Datatypes

Variable that has date/time data type hold value call datetimes. Oracle SQL automatically converts character value in to default date format ('DD-MON-YY') TO_DATE values. Following are Date/Time data types in Oracle SQL.

Datatype Description Range
DATE DATE data type to store valid date-time format with fixed length. Starting date from Jan 1, 4712 BC to Dec 31, 9999 AD. Jan 1, 4712 BC to
Dec 31, 9999 AD
TIMESTAMP TIMESTAMP data type to store valid date (year, month, day) with time (hour, minute, second).
Type TIMESTAMP Type
1 Syntax: TIMESTAMP [(fractional_seconds_precision)]
Example: TIMESTAMP '2014-04-13 18:10:52.124'
fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field. Range 0 to 9. The default is 6.
2 Syntax: TIMESTAMP [(fractional_seconds_precision) ] WITH TIME ZONE
Example: TIMESTAMP '2014-04-13 18:10:52.124 +05:30'
WITH TIME ZONE specify the UTC time zone. Following two values represent same instant in UTC.
TIMESTAMP '1999-04-15 8:00:00 -8:00' (8.00 AM Pacific Standard Time) or
TIMESTAMP '1999-04-15 11:00:00 -5:00' (11:00 AM Eastern Standard Time) both are same.
3 Syntax: TIMESTAMP [(fractional_seconds_precision) ] WITH LOCAL TIME ZONE
Example: COL_NAME TIMESTAMP(3) WITH LOCAL TIME ZONE;
WITH LOCAL TIME ZONE specify when you insert value into database column, value is store to the database time zone, and the time-zone displacement is not stored in the column. When you retrieve value oracle database returns it in your UTC local time zone.
Example: 
CREATE TABLE time_table(
  start_time  TIMESTAMP,
    time1 INTERVAL DAY (6) TO SECOND (5),
    time2   INTERVAL YEAR TO MONTH
);

Variable that has interval data type hold value call intervals. Following are Interval data types in SQL.

Datatype Description
INTERVAL YEAR TO MONTH INTERVAL YEAR TO MONTH data type to store and manipulate intervals of year and month.
Syntax: INTERVAL YEAR [ (year_precision) ] TO MONTH
Note: precision specifies number of digits in years field range from 0 to 9 and default is 2.
Size of datatype 5 bytes fixed.
INTERVAL DAY TO SECOND INTERVAL DAY TO SECOND data type to store and manipulate intervals of days, hours, minutes, and seconds.
Syntax: INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]
Note: day_precision specifies number of digits in days field range from 0 to 9. The default is 2.
fractional_seconds_precision specifies number of digits in days field range from 0 to 9. The default is 6.
Size of datatype 11 bytes fixed.

Large Object Datatypes (LOB types)

LOB data types use to store large object such as image, video, graphics, text, audio. Maximum size up to 4 Gigabytes. Following are LOB data types in SQL.

Datatype Description Storage(Maximum)
BFILE BFILE data type to store large binary object into Operating System file. This data type variable store full file locator's path which point to a stored binary object with in server. BFILE data type read only, you can't modify them. Size: up to 4GB (232 - 1 bytes)
Directory name: 30 character
File name: 255 characters
BLOB BLOB data type same as BFILE data type to store unstructured binary object into Operating System file. BLOB type fully supported transactions are recoverable and replicated. Size: 8 TB to 128 TB
(4GB - 1) * DB_BLOCK_SIZE
CLOB CLOB data type to store large blocks of character data into Database. Store single byte and multi byte character data. CLOB type fully supported transactions are recoverable and replicated. Size: 8 TB to 128 TB
(4GB - 1) * DB_BLOCK_SIZE
NCLOB NCLOB data type to store large blocks of NCHAR data into Database. Store single byte and multi byte character data. NCLOB type fully supported transactions are recoverable and replicated. Size: 8 TB to 128 TB
(4GB - 1) * DB_BLOCK_SIZE