A data type is the name or label for a set of values. It specifies what type of value an attribute or a variable holds. Also, it specifies how the information will be stored in a computer. Each attribute or variable has a data type.
Character Data Type
The Oracle Database provides character data types to store character values. These data types are discussed next.
CHAR
The CHAR data type is used to store the fixed length character data. The maximum length of data that it can store is 2000 bytes or characters. The default value for CHAR data type is 1.
Syntax:
column_name CHAR(width)
NCHAR
The NCHAR data type is used to store the fixed length character string in the national character set of the database. This data type can hold up to 2000 characters. Defining national character set in the database determines the maximum length of the column. When you create a table with a column having NCHAR data type, you define the column length in characters.
Syntax:
column_name NCHAR(width)
VARCHAR
The VARCHAR data type is used to store a variable-length character string. The maximum width of the VARCHAR data type is 4000 bytes or characters. It is recommended to use the VARCHAR2 data type rather than the VARCHAR data type.
Syntax:
column_name VARCHAR(width)
VARCHAR2
The VARCHAR2 data type is also used to store a variable-length character string. While creating the VARCHAR2 column, you can specify the maximum number of bytes or characters of data that can be stored in this column. If you enter a value that is smaller than the column size, the Oracle database will store the actual value of the data and set the remaining space free. The maximum width of the VARCHAR2 data type is 4000 bytes.
Syntax:
column_name VARCHAR(width)
NVARCHAR2
The NVARCHAR2 data type is used to store variable-length or multibytes character set data. While creating the NVARCHAR2 column, you can specify the maximum number of bytes or characters of data that can be stored in this column. The maximum length of the column is determined by the national character set defined in the column and is up to 4000 bytes.
Syntax:
column_name NVARCHAR(width)
NUMBER Data Type
The NUMBER data type stores variable-length numeric data with a precision between 1 and 38, and the scale has a range between –84 and 127. It can store the zero, positive numbers, or negative fixed numbers with absolute values from 1.0 x 10-130 to 1.0 x 10126 digits as well as fixed and floating point numbers. The Oracle database provides three subtypes of the NUMBER data type: Fixed-point, Floating-point, and Integer.
Fixed-point Number
To define the Fixed-point number data type, you have to specify the values of both precision and scale.
Syntax:
Column_Name NUMBER(P,S)
P is the precision or the total number of digits with precision up to 38 digits and S is the scale or the number of digits on the right of the decimal point. The value of S can range from
-84 to 127.
Integer Numbers
An integer is a whole number with no digit on the right of the decimal point. You can define a column of integer data type by omitting the scale value.
Syntax:
Column_Name NUMBER(P)
P is the precision or the total number of digits with precision up to 38 digits.
Floating-point Number
The Floating-point numbers can have a decimal point anywhere between the first and the last digits, or it can be a number without any decimal point as there is no restriction for the decimal point.
Syntax:
Column_Name NUMBER
Oracle provides two numeric data types for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE.
BINARY_FLOAT
The BINARY_FLOAT data type is a single-precision floating-point number data type. Each BINARY_FLOAT value requires 5 bytes, including a length byte.
BINARY_DOUBLE
The BINARY_DOUBLE is a double-precision floating-point number data type. Each BINARY_DOUBLE value requires 9 bytes, including a length byte.
Datetime and Interval
The Oracle datetime data types store date and time values. The datetime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The interval data types are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. The datetime and interval data types are discussed next.
DATE
The DATE data type is used to store date and time. Oracle stores the following information for each date value: century, year, month, date, hour, minute, and second. You can represent the date and time in both character and number data types.
Syntax:
Column_Name DATE
TIMESTAMP
The TIMESTAMP data type stores all information that the DATE data type stores, including the fractional part of seconds. It is an expansion of the DATE data type. It stores century, year, month, day, hour, minute, second, and fractional seconds. This data type is useful for storing precise time values.
Syntax:
Column_Name TIMESTAMP [(Fractional_Seconds_Precision)]
Fractional_Seconds_Precision can range from 0 to 9 and its default value is 6.
TIMESTAMP WITH TIME ZONE
The TIMESTAMP WITH TIME ZONE data type is an alternative to the TIMESTAMP data type. The value stored by this data type includes time zone offset. This data type is useful for collecting and evaluating date information across geographic regions.
Syntax:
Column_Name TIMESTAMP [(Fractional_Seconds_Precision)] WITH TIME ZONE
Fractional_Seconds_Precision can range from 0 to 9 and its default value 6.
TIMESTAMP WITH LOCAL TIME ZONE
The TIMESTAMP WITH LOCAL TIME ZONE data type is another alternative to the TIMESTAMP data type. It also includes a time zone offset in its value. Unlike the TIMESTAMP WITH TIME ZONE data type, the TIMESTAMP WITH LOCAL TIME ZONE data type does not store the time zone offset as part of the column data.
Syntax:
Column_Name TIMESTAMP [(Fractional_Seconds_Precision)] WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
The INTERVAL YEAR TO MONTH data type is used to store the period of time that
represents year and month.
Syntax:
Column_Name INTERVAL YEAR [(year_precision)] TO MONTH
year_precision can range from 0 to 9 and its default value is 2.
INTERVAL DAY TO SECOND
The INTERVAL DAY TO SECOND data type is used to store the period of time that represents days, hours, minutes, and seconds with a fractional part.
Syntax:
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
day_precision can range from 0 to 9 and its default value 2. fractional_seconds_precision can range from 0 to 9 and its default is 6.
LOB
LOB stands for Large Object. It is a data type and stores unstructured information upto 4 gigabytes such as sound clips, video files, and so on. The LOB data types allow efficient, random, and easy access to the data. The values stored in this data type are known as locators. These locators store the locations of large objects and location may be inside or outside the database.
The LOB data types available in Oracle database are BLOB, CLOB, NCLOB, and BFILE.
BLOB
BLOB stands for Binary Large Objects. This data type is used to store binary data up to 4 GB in length.
Syntax:
Column_Name BLOB
CLOB
CLOB stands for Character Large Objects and can store character data up to 4 GB in length.
Syntax:
Column_Name CLOB
BFILE
BFILE stands for Binary FILE. It is a pointer (reference) to the external file. The files referenced by BFILE exist in the file system and enables you to access the binary file that are stored outside the Oracle database. The database only maintains a pointer to the file. The size of the external file is limited only by the operating system because the data is stored outside the database.
Syntax:
Column_Name BFILE
NCLOB
The NCLOB data type supports both fixed-width and variable-width character sets. The
NCLOB data type can store up to 4 gigabytes of character text data.
Syntax:
Column_Name CLOB
