Data Type means the type of data. We (human beings) know the difference between 12345 and “abcdef”, but computer cannot know difference between those (which is integer or character or date). So, we have to define the type of data before storing.
Shortly we can say … The data type is the classification of data.
When we create TABLE in database, a data type must be mentioned with the column name. If we mention a data type named NUMBER with the table column named EMPLOYEEID, only numeric data can be stored in this EMPLOYEEID column. If you provide character or date or another type of data, system will show error message and define that as Invalid Data. So, we are bound to store numeric data into EMPLOYEEID column. In this way, we classify data during table creation.
Finally, we can say, DATATYPE is the classification of data that can define which type of data will be stored in the variable or table column.
Oracle uses various data types such as Character, Numeric, Date Time and Large Object. The description of these data types are listed below:
|1 Byte to 4 KB||The VARCHAR2 is used to store variable-length character data. It stores data in character set.
Variable-length means, suppose the size of a column is 20, so this column is capable to store 20 characters. If you store 8 characters, the remaining (20-8) =12 characters memory spaces will be free to store another characters.
So, we see that the VARCHAR2 will specify memory depending on characters that have been provided.
|1 Byte to 4 KB||The NVARCHAR2 is same as VARCHAR2. But it stores character data in Unicode character sets.|
|1 Byte to 2 KB||The CHAR is used to store fixed-length character data. It stores data in character set.
Fixed-length means, suppose the size of a column is 20. So, this column can store up to 20 characters. If you store 8 characters in this column, the remaining (20-8) =12 characters will be padded by spaces.
In result, we see that it will belong to full length that has been specified during column creation in table, although character data has not been provided equal to column size. It will consume full size.
|NUMBER||NUMBER(p , s)
p = precision
s = scale
|The precision range is from 1 to 38.
The scale range is from – 84 to 127.
|The NUMBER data type is used to numeric data.
It is a Variable-length numeric data type. It can also store fractional data. It can specify precision and scale.
Suppose, if we create a column by NUMBER(10,2) data type, then it can store up to 10 digits, 8 digits before decimal and 2 digits after decimal such as 12345678.96.
Here, 12345678 is precision and 96 is scale.
|FLOAT||FLOAT||The FLOAT data type can store floating point numeric data. It is ANSI data type. It is also Variable-length data type. It also accepts precision up to 38 deciamals.|
|INTEGER||INTERGER||The INTEGER data type can store numeric data like NUMBER data type. But it can not specify scale. It is also Variable-length numeric data type.|
|DATE||DATE||From 01 January, 4712 BC To 31 December,9999 AD||The DATE data type stores date and time data consisting of Century, Year, Month, Day, Hour, Minute, Second.|
|TIMESTAMP WITH TIMEZONE|
LARGE OBJECT DATATYPES
|CLOB||CLOB||(4 Gigabytes -1)* (database block size)||The CLOB- Character Large Object can contain single or multi byte characters. It stores character data in database character set.|
|NCLOB||NCLOB||(4 Gigabytes -1)* (database block size)||The NCLOB data type is same as CLOB, but supports Unicode character sets.|
|BLOB||BLOB||(4 Gigabytes -1)* (database block size)||The BLOB- Binary Large Object is a variable-length data type. It stores large files such as images, audio, video files in the database. The BLOB column can not contain character data.|
|BFILE||BFILE||4 Gigabytes||The BFILE data type contains a locator or a link of an external binary file that has been stored outside of the database as an operating system file. It cannot be replicated to other systems.|
|LONG||LONG||Maximum 2 GB||The LONG data type is used to store character data in database character set. This data type should not be used in modern database. We can use only one column for LONG data type. The LONG column can be converted to CLOB data type.|
|LONG RAW||LONG RAW||Maximum 2 GB||It is same as LONG data type. But it can contain binary data. The LONG RAW column can be converted to BLOB data type. It is also a variable-length data type.|
RAWID DATATYPE: ROWID
BINARY DATATYPE: RAW