Oracle Supporting Data Types With Examples

 *Oracle Supporting Data Types

1)Numeric Datatypes

2)Character/string

3)Long

4)Date

5)Raw & Long Raw

6)Lob (Large Object)

=========================================================================

1)Numeric DT:

============

     i)INT

     ii)Number

i)INT:

 =>To store Integer values Only

=>when we use "int" DT at the time of table designing internally Oracle Server

is converting into "Number" DT with max size 38 Digits.

         int = Number(38)

ii)Number(p,s):

   =>To store Integer and float value

        >Number(p) --------->INT

        >Number(p,s)--------->Float

 

 Precision(p):

============= 

  =>Counting all digits (left + right) in the given Expression 

   ex:: i)98.34

          precision = 4

        ii)9878.34

           precision = 6

 scale(s):

=========

=>Counting right side digit Only

  ex:i)98.34

       precision = 4

       scale = 2

     ii)8870.454

       precision = 7

        scale = 3

2)Character/string DT

=====================

=>Storing String format data only

=>Here String represent with  -----> '<string>'

                    

                       string format data

character only string          |    alphanumeric string

   [A-Z or a-z]                |   [A-Z/a-z,0-9,@,#,$,%,&,*]

=>This DT are again divided to two categories

  

     1)Non-Unicode

     2)Unicode

  

    1)Non-Unicode 

           To store localized data  -- > English

    2)Unicode

           to store globalized data -->chinese,

    

1)Non-Unicode 

     >char(size)

     >varchar2(size)

2)Unicode

     >nchar(size)

     >nvarchar2(size)

          n=====national lang

1)Non-unicode 

  i)char(size):

    =>It is fixed length DT(static)

    =>store non-unicode char's

    =>1 char = 1 byte

    =>max. size is 2000Byte

 

ex:to store mobile,Pin etc.. we can use

  

    Fixed Length

    name char(10);

   name = hello   -------- > 5bytes

    but 5 bytes memory waste

    

    name char(20);

    name = 'hello'

      here 15 bytes will waste

ii)varchar2(size):

  ->It is variable length DT(Dynamic)

  ->Store Non-Unicode char's

  ->1 char = 1 Byte

  ->max.size == 4000Bytes

  

ex: name varchar2(10);

    name = 'hello'

    so variable will assign only 5 bytes so no memory wastage

2)unicode 

  i)nchar(size):

    =>It is fixed length DT(static)

    =>store unicode char's

    =>1 char = 1 byte

    =>max. size is 2000Byte

 

ex:to store mobile,Pin etc.. we can use

  

    Fixed Length

    name nchar(10);

   name = hello   -------- > 5bytes

    but 5 bytes memory waste

    

    name nchar(20);

    name = 'hello'

      here 15 bytes will waste

ii)nvarchar2(size):

  ->It is variable length DT(Dynamic)

  ->Store Non-Unicode char's

  ->1 char = 1 Byte

  ->max.size == 4000Bytes

  

ex: name nvarchar2(10);

    name = 'hello'

    so variable will assign only 5 bytes so no memory wastage

3)Long DT:

=========

    ->To store non-Unicode & Unicode char's

    =>It is Dynamic DT

    =>Max size is 2GB

    =>1 char = 1 Byte

4)Date DT:

=========

i)Date

======

   =>Store Date and time info of particular Day

   =>time is optional if user is not enter time information then oracle will

     take default time '12:00:00' am

   =>Oracle Default Date Format is:

    =============================

    'DD-MON-YY/YYYY  HH:MI:SS'

    '17-MAY-21/2021 20:21:33pm'

      1   1    2     1  1  1

   

   =>Date is Occupied 7 Bytes(fixed Memory)

ii)Timestamp:

============

->store date & time info along with millisecond

-> 'DD-MM-YY/YYYY  HH:MI:SS:MS'

    1   1   2      1  1  1   4

 

->Ocupied 11 bytes memory

5)Raw & Long Raw:

================

=>To store Image/Audio/Video Files in the form of 01001010010101 Binary Format

=       RAW ------> 2000Bytes

        Long Raw ---------> 2GB

6)LOB DT(Large Objects)

======================

i)BLOB(Binary LOB)

===============

=>To store Image/Audio/Video Files in the form of 01001010010101 Binary Format

->4GB

ii)CLOB(CHaracter LOB):

======================

->To store non-unicode char's

=>4GB

iii)nCLOB(national Character Large Object):

==========================================

->To store Unicode char's

->4GB