Database

Data types in Oracle

Data type is an attribute which will specify that what type of data is storing into a column in the table. The Oracle supports the following data types.
      1. Numeric data type.
      2. Character data type.
      3. Long data type.
      4. Date / Time data type.
      5. Raw / Long raw data type.
      6. Large Object / Binary Large Object data type.
1. Numeric datatype:The Numeric datatype is allowing to store only numbers which may consist of integer and float values. This datatype is again classified into two types.
      1. Integer Values
      2. Float Values.
    1. Integer Values:
⋙ It will store only integer format values but wouldn’t accept pointing values.
    Examples: 10, 20, 30, 25, 36, 8, 90, 100, 2000, 2500, etc.
⋙ For storing integer format values into table column, we have an “int” datatype.
⋙ When we use “int” as the datatype, internally database would convert “int” datatype to number data type with max 38 digits.
       int = number(38)
Float Values:
⋙ It will store both integer and as well as pointing values.
⋙ To store float & integer values, we have to use “number” as the datatype.
⋙ In the number datatype would have two arguments / parameters such as Precision and scale.
      Syntax:Number(precision, scale)
Precision:It counts all digits including left and right of a decimal point in a given expression.
Precision:It will count the digits of a right side of decimal point in a given expression.
    Examples: 25.34          →    Precision -4, scale-2.
                       253.12        →    Precision-5, Scale – 2
                       25.312        →    Precision -5, Scale – 3
                       253.3245    →    Precision -7, Scale – 4

2. Character datatype:It will accept only string type of characters and also representation must be in “single quotation”

Character datatype is categorized into two types.

1. Non-Unicode Datatype:These datatypes would support the localized data which means english language. It contains two datatypes.
        1.char(size)
        2. varchar2(size)
1. char(size)It is a fixed length data type and also, it is static in nature. Each character would take 1 Byte memory and the maximum size is 2000 Bytes.
1. varchar2(size)it is a variable length data type and also, it is dynamic in nature. Each character will take 1 Byte memory and the maximum size is 4000 Bytes.

2. Unicode Datatype:These datatypes would support the globalized data which means national language. It contains two datatypes.
        1.nchar(size)
        2. nvarchar2(size)
1. nchar(size)It is a fixed length data type and also, it is static in nature. Each character would take 1 Byte memory and the maximum size is 2000 Bytes.
1. nvarchar2(size)it is a variable length data type and also, it is dynamic in nature. Each character will take 1 Byte memory and the maximum size is 4000 Bytes.

3. Long datatype:It is a dynamic data type and would store characters and alpha numeric characters. Each character will allow to take 1 char = 1 Byte and the maximum size is 2GB

4. Data / Time datatype:These data types are storing date time information of a particular day. The data types are using an employee date of join, customer account opening day or closing day.
The default format of a date in oracle is ‘dd-mon-yy’
Examples: ’10-Nov-98′, 17-oct-17′, ’14-may-90′, ’13-may-16′, etc.
The default format of a time in oracle is ‘HH:MI:SS:MS’
Examples: ’10:31:03:35′, ’12:02:56:45′, ’08:08:57:54′, etc.

5. Raw & Long Raw datatype / Large Object & Binary Large Object data type:These data types are called Binary data types which will be allocated memory dynamically. It will allow to store image files, audio files, video files in Binary format.

About the author

karunakar

I completed the M.TECH, am enthusiastic to finding the gadgets & learning new things.

Add Comment

Click here to post a comment