Saturday, 4 March 2017

Data Types in Oracle

A data types specifies type of data allowed in column and amount of memory allocated for that column. Every column must have a datatype or data defination that ensure that which type of data you are going to store.

There are various type of data type.

String data type:

CHAR: It allows character data upto 2000 characters recomended for fixed length. let's see an example of character that clearify fixed length concept.

Oracle adda
Char-oracle adda

In this case there are memory wastage. so that it is not recommended for variable length data. you can use this for fixed data.

Fixed length example are 

State_code Country_code Gender
AP         IND M
TS        AUS F
MH        AUS
KL        NZL
e.t.c        e.t.c

VARCHAR2: It allows Character data upto 4000 character in 11G and 32767 in 12C. It is highly recommended for variable lenghth field. let's see an example:

Oracle adda
varchar2- oracle adda

Initially it allocates whole memory that you initiates butafter data entered, it will release all blank speces and declare to others. this is the major advantages of VARCHAR2.

NOTE: Upto version 6 oracle has varchar data type that comes under ANSI where from version 7 oracle introduced Varchar2. Oracle also supports varchar data type but that was outdated.

NCHAR/NVARCHAR2: N denotes National. there is some difference between CHAR/VARCHAR2 and NCHAR/VARCHAR2. In case char/varchar2, it allows ASCII 256 chars(a-z, A-Z, 0-9, SPECIAL SYMBOL). we can also say that it accepts Alphanumeric character. where NCHAR/VARCHAR2 it allows unicode character that is 65536 of (ASCII chars and different language).

NUMBER(P): p stands for precision. It allows numeric data maximum upto 38 digits.

NUMBER(P,S)-: P- total number of digit
     S-(scale) Number of digit allows after decimal(-84 to 127)

There is some example that is easy to under stand this concept.
if Number(7,2) tthen
500000.20  valid
50000.20   valid
50000.205  valid but value stored in oracle is 50000.21

NOTE: Before decimal, number exceeds is invalid but after decimal, number exceed will round the maximum precision is 38 digit.

There are tow type of scale that is positive scale and negative scale.

Input           -VE Scale Stores value in database

number(7,-2)         4567.53 4600
number(7,3)     4567       4600     

DATE Type:

It allows Date and time in database. Actually time is not complsury, if time is not entered then Oracle bydefault stores 12:AM in database.

There is default Date format: DD-MM-YY and DD-MM-YYYY. It will take total 7 bytes space in momery. let's see how it takes 7 bytes space in momery.

1b  1b       2b       1b      1b 1b  here b denotes Byte and total is 7 bytes.

Date must be between 01-jan-4712 BC to 31-dec-9999 AD(Anni Domino). before 9i we can only stores dates but from 9i there is another date type is:
TIMESTAMP:- It allows date time and millisecond like:
20-aug-2014 2:30:23.1234

TIMESTAMP with TIME ZONE:- I allows date, time, milliscond and also TIME ZONE. such example like:
20-aug-2014 2:30:23.1234 PM +5:30

INTERVAL YEAR TO MONTH:- It is used to store Time periods. It is used if time period is in year to month. example are like:
then we need to input like this
INTERVAL '4' year   -> means 4 year 
interval '1-6' year to month    ->one year six month
ingterval'6' month   -> six month

INTERVAL DAY TO SECOND:- It is used to store time period. we will use this if time period is in days, hours, minutes and seconds. let's see an example of this..
then we need to input like this
INTERVAL '2 4:30:20'    -> means two days 4 hours thirty minutes and 20 seconds.DATE TO           SECOND 
INTERVAL '3 5'          -> Means three days and five hours. DAY TO HOUR
Interval '1:30' -> Means one hour thirty minutes. HOUR TO MINUTE

FROM ORACLE 8i some new data types are introduced. these are listed here;
  • LOBs(Large Objects)
  • BFILE(Binary File)
  • BLOB(Binary Large Object)
  • CLOB(Chaarcter large Object)

BFILE/ BLOB:- It is used to binary data that include like Audio, video, pictures, images. Actually ther is some difference between BFILE and BLOB.

BFILE- It stores only file path and actual file stores outside of oracle database. that's why it is not secured.
BLOB-  It stoes files inside the database that's it is secured.

CLOB:- It allows character data upto 4GB and this is same like VARCHAR2 data types.

DATA TYPES topics Ends here. if you have any query, you can comment here and aslo you can email me on