Monday , September 23 2019
Home / SQL / SQL Data Types

SQL Data Types

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:

CHARACTER DATATYPES

Data Type Syntax Length Explanation
VARCHAR2 VARCHAR2(size)

Example:

VARCHAR2(20)

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.

NVARCHAR2 NVARCHAR2(size)

Example:

NVARCHAR2(20)

1 Byte to 4 KB The NVARCHAR2 is same as VARCHAR2. But it stores character data in Unicode character sets.
CHAR CHAR(size)

Example:

CHAR(20)

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.

NUMERIC DATATYPES

Data Type Syntax Length Explanation
NUMBER NUMBER(p , s)

here,

p = precision

s = scale

Example:

NUMBER(10,2)

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/TIME DATATYPES

Data Type Syntax Length Explanation
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
TIMESTAMP WITH TIMEZONE

LARGE OBJECT DATATYPES

Data Type Syntax Length Explanation
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

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Leave a Reply

Your email address will not be published. Required fields are marked *