Hi friends, Once again here with second part of datatype in oracle. Now lets talk about some of the date datatype.
DATE:
This datatype allows to store date and time. Time is optional here. If we don't provide time then oracle stores 12:00 AM by default. There is two format for date. That is:- DD-MM-YY
- DD-MM-YYY
But oracle stores it in the form of DD-MM-YYYY. Even if we provide data in the form of DD-MM-YY then oracle automatically convert it into DD-MM-YYYY format. Lets see the example:
22-AUG-15 22-AUG-2015
22-AUG-95 22-AUG-2095
22-AUG-1995 22-AUG-1995
Dates in oracle must be between 01-JAN-4712 BC to 31-DEC-9999. Oracle occupies 7 bytes for storing date. Lets see how?
Input : DD-MM-YYY
Oracle stores : DD MM YYY HH MI SS
Bytes : 1 1 2 1 1 1
1+1+2+1+1+1=7 byte. Now here one question is arise that if DD, MM, HH, MI, SS have two digits then how can it stored in one byte. Lets see: one character take one byte and it can stores upto 256. Now, DD maximum value is 31, MM maximum value is 12, HH maximum value is 24, MI maximum value is 60 and SS maximum value is also 60. Then what we seen that all of these is less than the maximum value of character. So it can stored in one byte. But YYYY maximum value is 9999 and it will greater than maximum value of one character that's why it take 2 byte.
From oracle 9i there is some new datatype is introduce for storing date type data. That is:
TIMESTAMP:
It is same as date but it has one extra feature. It will allows date, time and millisecond. Lets see an example:
tm TIMESTAMP
22-AUG-2015 03:06:10.12345
TIMESTAMP WITH TIME ZONE:
It allows date, time, millisecond and time zone also. Lets see an example
tm TIMESTAMP WITH TIME ZONE
22-AUG-2015 11:01:40.1234 +5:30
INTERVAL YEAR TO MONTH:
It is used for storing time periods. This data type is used if time period in year and month. Lets see an example:
duration INTERVAL YEAR TO MONTH
INTERVAL '4' YEAR
INTERVAL '1-6' YEAR TO MONTH
INTERVAL '6' MONTH
INTERVAL DAY TO SECOND:
This is also used for storing time periods. This datatype is used if time period in day, hours, minutes, seconds etc. Lets see an example:
duration INTERVAL DAY TO SECOND
INTERVAL '2 4:30:20' DAY TO SECOND
INTERVAL '3 5' DAY TO HOUR
INTERVAL '1:30' HOUR TO MINUTE
Now lets talk about some other datatype. From oracle 8 there is some new datatype is introduced that's called LOBs (Large Objects).
- BFILE (Binary files)
- BLOB (Binary LOBs)
- CLOB (Character LOBs)
BFILE/BLOB:
It is used to store binary data that include audio, video, picture etc. It will store data up-to 4GB. Here one question is arise that why two datatype for same work. Lets see, both of them are not same. There is some different between both. In "BFILE", data is stored outside of database (in hard disk) and only path of that is stored in database. But in "BLOB" data is stored in database. So if there is any security problem then we must to use "BLOB". But if security doesn't matter then we can use "BFILE". That's why "BFILE" is also called external LOB and "BLOB" is called internal LOB. Both of them can store data upto 4GB.
CLOB:
It is same as "BLOB". but it is used for storing character data up-to 4 GB. As we seen that varchar2 have only 32767 character length, Nvarchar2 have only 65536 character length. Then if we need to store more than this then we can use this datatype.
Here datatype section of oracle is ended. SO that's all for now. For more details keep visiting Oracle Gang.......
No comments:
Post a Comment