Datatypes in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Datatypes in Oracle Database with Examples

In this article, I am going to discuss Datatypes in Oracle Database with Examples. Please read our previous article, where we discussed how to work with Oracle Database.

Datatypes in Oracle

Datatype is an attribute that specifies what type of data is stored in a column. Oracle supports the following data types.

1. Numeric Datatypes
2. Character Datatypes/ String Datatypes
3. Long Datatype
4. Date Datatypes
5. Raw & Long Raw Datatypes
6. Lob Datatypes (Large Objects Datatypes)

1) Numeric Datatypes in Oracle:

They are categorized into two types.

1. Int
2. Number(P, S)

INT:

Storing integer format values only.

Sno int —————–> Sno Number(38)

Note: When we use INT datatype on the column at the time of table creation then internally oracle server will convert it into “number” datatype with a maximum size is 38 digits.

Number(P, S):

This data type is basically used for storing both integer & float format values. Here this datatype is having following two arguments i.e. precision(P), scale(S).

1. When we use —-> Number(P)——–> Sore integer values only
2. When we use —-> Number(P, S) —-> Store float values only

Precision(p):

Counting all digits including left & right sides of given float expression or counting all digits including left & right sides digits of a decimal point.
Example: 25.12 => Precision = 4
Example: 856.45 => Precision = 5
Example: 9999.99 => Precision = 6

Scale(s):

Counting only right digits of a float expression or counting the right-side digits of a decimal point
Example: 25.12
Scale = 2
Precision = 4

Example: 7456.123
Scale = 3
Precision = 7

2) Character Datatypes in Oracle:

These Datatypes are used for storing “string” format data only. In database string is represented with single quotes ‘ <string> ‘.
Example: Empname char(10)
Anurag ——-> error(not allowed)
‘Anurag’——> allowed

Character datatypes are storing two types of string format data. Those are

1. Characters only string data
2. Alphanumeric string data.

For a better understand, please have a look at the below diagram.

Note: Character Datatypes are again classified into two categories. Those are as follows.

1. Non-Unicode Datatypes:

Supporting to store localized data/ information (only English language). These are again two types.

i) Char(size):

1. It is a fixed-length datatype (static).
2. It will store non-Unicode char’s in the form of 1 char = 1 byte.
3. Maximum size of char datatype is 2000 bytes (2000 char’s).
4. Main drawback of this datatype is “memory wasted”.

Example: Empname char(5)

• ‘a’———-> a (5 – 1 byte = 4 bytes)
• ‘ab’——–> ab (5-2 = 3 bytes)
• ‘abc’—–> abc (5-3=2 bytes)
• ‘abcd’—-> abcd (5-4=1 byte)
• ‘abcde’—> abcde (5-5= 0 bytes)
• ‘abcdef’ —> error (not allowed)

ii) Varchar2(size):

1. It is a variable length datatype (dynamic).
2. It will store non-Unicode characters in the form of 1 char = 1 byte.
3. Maximum size of varchar2 datatype is 4000 bytes (4000 char’s).
4. Main advantage is “save memory”.

Example: Empname char(5)

• ‘a’———-> a (1 byte)
• ‘ab’——–> ab (2 bytes)
• ‘abc’—–> abc (3 bytes)
• ‘abcd’—-> abcd(4 bytes)
• ‘abcde’—> abcde(5 bytes)
• ‘abcdef’ —> error (not allowed)

Differences between fixed-length datatypes and variable-length datatypes in Oracle:

2. Unicode datatypes:

These data types are used for storing “globalized data/information” i.e. supporting “all national languages”. These are again classified into two types,

1. Nchar(size)
2. Nvarchar2(size)

Here,” N ” ———- National lang.

Nchar(size):

1. It is a fixed-length datatype (static).
2. It will store non-Unicode chars (all national languages) in the form of 1 char = 1 byte.
3. The maximum size of the Nchar datatype is 2000 bytes (2000 chars).
4. The main drawback of this datatype is “memory wasted”.

ii) Nvarchar2(size):

1. It is a variable-length datatype (dynamic).
2. It will store non-Unicode characters (all national languages) in the form of 1 char = 1 byte.
3. The maximum size of the Nvarchar2 datatype is 4000 bytes (4000 chars).
4. The main advantage is “save memory”.

3) Long Datatype in Oracle:

1. It is a Variable length datatype(dynamic)
2. It will Store both non-Unicode & Unicode char’s in the form of 1 char = 1 byte
3. Max size of the Long Data type is 2 GB.

Example: EAddress Long——>2gb(1gb=1024mb–> 1mb=1024kb–> 1kb=1024bytes)
‘a’———> a (2gb=2lb-1=1.99999b)
‘ab’——> ab(2lb-2=1.99998b)

4) Date Datatypes in Oracle:

It is used for storing the date & time information of a particular day. The range of date datatype is from ’01-Jan-4712′ BC to ‘ 31-dec-9999’ AD.

i) Date:

Storing date & time information but time is optional. When the user is not given time information then the oracle database server will take time by default ’12:00:00′ am / ’00:00:00’am

Default date & time format of Oracle database.

It occupied 7 bytes of memory (fixed memory)

ii) Timestamp:

Storing date & time information including ‘milliseconds’. Default format of timestamp datatype is ‘dd-mon-yyyy / yy hh: mi: ss.ms’. It occupied 11 bytes of memory (fixed memory).

Raw & Long Raw Datatypes in Oracle:

These data types are used to store image/audio /video files in the form of 01001010100010101 binary formats.

1. Raw ———> 2000 bytes
2. Long Raw —> 2gb

These data types are also called “binary datatypes”.

LOB Datatypes in Oracle:

LOB stands for Large Objects.

i) BLOB:

BLOB stands for Binary Large Object and this Data Type is used for storing image/audio /video files in the form 010010101001 binary format. The maximum size is 128 TB.

ii) CLOB:

CLOB stands for character large object and this Data Type is used for storing non-Unicode characters. The maximum size is 128 TB.

iii) NCLOB:

NCLOB stands for National characters large object and this data type is used for storing Unicode characters. The maximum size is 128 TB.

Oracle Data Type Size Summary:

In the next article, I am going to discuss Data Definition Language (DDL) commands in Oracle with Examples Here, in this article, I try to explain Datatypes in the Oracle database with Examples and I hope you enjoy this Datatype in the Oracle database with Examples article.