Datatypes in Oracle

DATATYPES:

·         Datatype specifies type of the data allowed in a column.

These are the following datatypes in Oracle:

1.    String

a.     Char

b.    Nchar

c.     Varchar

d.    Varchar2

e.     Nvarchar2

f.      Long

2.    Date

3.    Number

a.     Number(p)

b.    Number(p,s)

p = precision = total no of digits allowed.

s = scale = no of digits after decimal.

4.    Raw

a.     Raw

b.    Long raw

5.    Lob

a.     BFILE

b.    BLOB

c.     CLOB

6.    Timestamp

7.    Timeperiods

a.     Interval year to month

b.    Interval day to second

8.    Binary

a.     Binary_Float

b.    Binary_Double

Char:  

·         It allows upto 2000 character.

·         It has a fixed length.

·         Mostly used for phone no, pincode, result.

·         Alpha numeric datatype.

Ex:       ename char(20)

20 bytes of memory is allocated for ename. Hence if the name is shorter than 20 bytes, memory is wasted.

Varchar / Varchar2:

·         It allows up to 4000 characters.

·         It has a variable length.

·         Mostly used for variable length columns.

·         Alpha numeric datatype.

Ex:       ename varchar2(20);

           

            Varchar:         ANSI datatype

            Varchar2:       Oracle datatype.

Nchar / Nvarchar2:

·         It accepts UNICODE values.

·         Range is 0-65535 = 65536 characters.

Long:

·         It allows up to 2GB.

Number(p):

·         It allows numeric data up to 38 digits

·         Mostly used for primary key columns.

Number(p,s):

·         It allows decimal values also.

·         P= precision= total no of digits

·         S= scale= total no of digits after decimal.

If P exceeds, remaining digits are ignores.

If S exceeds, the decimal part is rounded.

Date:

·         Allows Date & Time. But time is optional.

·         If time is not provided, oracle inserts 00:00:00 (12 AM )

·         Date format is ‘ DD-MON-YY HH:MI:SS ’, ‘ DD-MON-YYYY HH:MI:SS ’

·         Date Range is from ‘01-JAN-4712 BC’ to  ‘ 31-DEC-9999’

·         Date field occupies 7 bytes of memory

DD       MON   YYYY  HH      MI       SS

1          1          2          1          1          1

Raw:

·         It allows binary data (like pictures) up to 2000 bytes (2kb).

·         It allows only static pictures.

Long Raw:

·         It allows binary data (like pictures) up to 2GB.

·         It allows only static pictures but not gif.

Bfile/ Blob:

·         It allows multimedia like (gif, mp3, mpeg4 etc..) up to 4GB.

Clob:

·         It allows character data up to 4GB.

Timestamp:

·         It allows data , time and milliseconds.

·         To set the size of milliseconds, use

startDate timestamp(4);

·         To store timestamp with zone, use

startDate timestamp with zone;

Time periods:

·         Interval Year to Months.

·         Interval Days to Sec.

They are used to store time periods.

Ex:       If you want to store ‘3’ years duration.

            timePeriod duration interval ‘3’ years;

           

            If you want to store ‘3 days + 4:30:20’ time.

            timePeriod duration interval ‘3 4:30:20’day to second.

Binary_Float:

·         It allows number with decimals.

·         Range is from 3.4*10^-38 to 3.4*10⁺³⁸.

·         It gives good performance than Number(p,s).

Binary_Double:

·         It allows number with decimals.

·         Range is from 1.7*10^-308  to 1.7*10⁺³⁰⁸

·         It gives good performance than Number(p,s).