Oracle Data Types

DATA TYPES IN ORACLE

A data type specifies type of the data allowed in a column and also amount of memory allocated for that column.

CHAR (Size): - It allows character data and it is recommended only for fixed length columns.

EXAMPLE: -

Name char(20)

S

A

C

H

I

N

H

A

R

I

In the above example there is memory wastage, so don’t use char data type for variable length fields like Name, place, job so on. Use char data type only for fixed length columns.

EXAMPLES FOR FIXED LENGTH COLUMNS:

○ State Code Char (2)

AP

TS

MH

TN

KA

○ Country Code Char (3)

IND

USA

ENG

○ Gender Char (1)

M

F

Char Data Type

VARCHAR2: -

It allows character data up to 4000 characters in Oracle11g and 32767 characters in Oracle12c. Varchar2 is recommended for variable length character fields.

EXAMPLE: -

Name Varchar2 (20)

S

A

C

H

I

N

H

A

R

I

In the above example up to the name it will consumes the memory and rest unused bytes it releases, that bytes can be used by other names or other data. In char data type it won’t release the unused bytes.

Up to Oracle 6 version it is Varchar this is ANSI data type. From Oracle 7 version it changed to Varchar2 this is ORACLE own data type. Functionality wise both are same.

CHAR, VARCHAR2 allows Alphanumeric (Both Characters and Numbers) data.

NUMBER (P): - It allows numeric data up to 38 digits.

EXAMPLE: -

Empno Number (4)

100 is allowed

1000 is allowed

10000 it is not allowed, as the digits are more than 4

NUMBER (P,S): - It allows numbers with decimals.

P --- Precision --- Total no of digits allowed

S --- Scale --- Number of digits allowed after decimal.

EXAMPLE: -

Sal Number (7, 2) (Before decimal 5digits after decimal 2digits total 7)

5000 is allowed

5000.50 is allowed

50000.50 is allowed

500000.50 is not allowed

5000.507 --- allowed, it will be rounded to one, oracle stores like this (5000.51)

5000.503 --- allowed, it will be rounded as 5000.50

DATE: -

It allows date and time, but time is optional, if not entered Oracle stores 12.00AM. Default date format is DD-MON-YY or DD-MON-YYYY.

EXAMPLE: -

Hiredate  Date

25-Aug-16

25-Aug-2016.

Date occupies 7 bytes             0 – 255 It is 1byte

Input: - DD-MON-YYYY                256 – 32767 It is 2bytes

DB: - DD MM YYYY HH MI SS

            1      1       2         1    1    1

TIMESTAMP: -

It allows date, time and milliseconds

EXAMPLE: -

T          Timestamp

DD-MON-YYYY HH:MI:SS.FFF

25-Aug-2016   11:40:20.1234

LOB’S: - Large Objects, introduced in Oracle 8 version, it is of 3 types

BLOB: - Binary Lob

BFILE: - Binary File Lob

CLOB: - Character Lob

BLOB/BFILE: - Both are used for storing binary data that includes audio, video and images up to 4GB.

If we use BFILE the original picture is stored outside the database but database stores path.

BFILE

If we use BLOB the picture is stored inside the database.

BLOB

CLOB: - It allows character data and character object up to 4GB.