In oracle, each ‘column value’ and constant in SQL expression has a ‘DataType’ which is related with a distinct storage format, constraints and well founded range of values of that datatype. The datatype associates with specific value in the table. So by this Oracle is able to treat values of one datatype differently from values of another datatype.
It is easy for humans to distinguish between different types of data. A datatype says what type of value it will take. We have to decide what types of data will be stored inside each and every table column when creating a SQL table, so it is very important to decide what types of datatypes will store in a column.
For example, assume we declare a column VARCHAR2 with a maximum size of 50 characters in our table. If only 20 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 20 characters (20 bytes) only. So what we have assigned that byte of character will take the space of what we haven't declared.
There are followings types of built-in datatypes in Oracle, which is explained below:
Character Datatypes
- CHAR - Char datatypes is fixed-length character data of length size bytes or characters and the maximum size is 2000 bytes per row, default size is 1 byte per row.
- NCHAR - The NCHAR datatype stores fixed-length character strings. The maximum length of an NCHAR column is 2000 bytes.
- VARCHAR2 & VARCHAR - The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior always use the VARCHAR2 datatype to store variable-length character strings.
- NVARCHAR2 - The NVARCHAR2 datatype stores variable length character strings and the maximum length of an NVARCHAR2 column is 4000 bytes.
- CLOB - Single-byte character data. Up to 232 - 1 bytes or 4 gigabytes.
- NCLOB - Unicode national character set (NCHAR) data. Up to 232 - 1 bytes, or 4 gigabytes.
- LONG - LONG datatypes is variable-length character data. Variable for each row in the table up to 232 - 1 bytes or 2 gigabytes, per row. Provided for backward compatibility.
Numeric Datatypes- NUMBER - The NUMBER datatype stores fixed and floating-point numbers. 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.
Time and date Datatypes- DATE - The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes and the seconds (after midnight). Oracle date format is DD-MON-YY. And the size Fixed at 7 bytes for each row in the table.
- INTERVAL DAY TO SECOND - A period of time which is represented as days, hours, minutes, and seconds. The precision values specify the number of digits in the DAY and the fractional SECOND fields of the date. The precision can be from 0 to 9 and defaults to 2 for days and 6 for seconds. And the size of this fixed at 11 bytes.
- INTERVAL YEAR TO MONTH - A period of time which is represented as years and months. The precision value specifies the number of digits in the YEAR field of the date. The precision can be from 0 to 9 and defaults to 2 for years and the size of this fixed at 5 bytes.
- TIMESTAMP - A specific value which representing a date and time, including fractional seconds and the value of this is 7 to 11 bytes which is depends on the precision.
- TIMESTAMP WITH TIME ZONE - A value representing a date and time, plus an associated time zone setting. The time zone can be an offset from UTC, such as '-5:0' or a region name, such as 'US/Pacific' and the size of this fixed at 13 bytes.
- TIMESTAMP WITH LOCAL TIME ZONE - This is Similar to TIMESTAMP WITH TIME ZONE, except that the data is normalized to the database time zone when it is stored and adjusted to match the client's time zone when retrieved and the values varies from 7 to 11 bytes which is depends on precision.
Binary Datatypes
- BLOB - It is unstructured binary data in the database, which can store up to 128 terabytes of binary data in the database.
- BFILE - It is the variable-length raw binary data in the database and variable for each row in the table up to 2000 bytes per row. A maximum size of the BFILE must be specified. Provided for backward compatibility.
- RAW - It is also the variable-length raw binary data in the database and the variable for each row in the table up to 231 - 1 bytes, or 2 gigabytes per row.
- LONG RAW - It is the Binary data representing row addresses, which is fixed at 10 bytes or 6 bytes for each row in the table.