First let’s understand what DUMP function do in Oracle.
The DUMP function shows the datatype, length in bytes and the actual value of each byte for column.
Now let’s do some hands-on:
SQL> create table dummy ( col1 DATE );
Table created.
SQL> INSERT INTO dummy
2 VALUES ( TO_DATE( ‘25/12/1980 12:30:05′,’DD/MM/YYYY HH24:MI:SS’ ) );
1 row created.
SQL> INSERT INTO dummy
2 VALUES ( TO_DATE( ‘25/12/2006 20:25:10′,’DD/MM/YYYY HH24:MI:SS’ ) );
1 row created.
SQL> select TO_CHAR(col1,’DD/MM/YYYY HH24:MI:SS’) col1, DUMP(col1) dumpcol1 FROM dummy
COL1
——————-
DUMPCOL1
——————————————————————————–
25/12/1980 12:30:05
Typ=12 Len=7: 119,180,12,25,13,31,6
25/12/2006 20:25:10
Typ=12 Len=7: 120,106,12,25,21,26,11
Now lets try to understand how Oracle is storing the DATE with above results.
Oracle’s stores DATE in total of 7 bytes. Each byte in it stores values for an element of the DATE as follows:
1st Byte: Stores the century value but before storing it add 100 to it.
2nd Byte: Stores the year and 100 is added to it before storing.
3rd Byte: Stores the Month.
4rth Byte: Stores the Day of the month.
5th Byte: Stores the hours but add 1 before storing it.
6th Byte: Stores the minutes but add 1 before storing it.
7th Byte: Stores the seconds but add 1 before storing it.
4 responses so far ↓
1 OracleBrains.Com » Blog Archive » Using Dump Function // Dec 13, 2006 at 3:52 pm
[...] « Understanding Internal DATE Storage [...]
2 OracleBrains.Com » Blog Archive » Internal Datatypes in Oracle // Dec 14, 2006 at 12:25 am
[...] Internal Datatypes in Oracle One more important term which really stuck me while writting “Understanding Internal DATE Storage” post, was “Internal Datatype”. Although the name itself sugggest that it is a internal coding of oracle to store different data type, but I was curious of what other more datatype are there…. [...]
3 OracleBrains Oracle Community Webspace - Blog » Using Dump Function // Dec 15, 2006 at 2:28 pm
[...] I was little bit curious about DUMP function after writting Understanding Internal DATE Storage. So I did little bit reserach and find out following things about it. [...]
4 OracleBrains Oracle Community Webspace - Blog » Internal Datatypes in Oracle // Dec 15, 2006 at 2:30 pm
[...] One more important term which really stuck me while writting “Understanding Internal DATE Storage” post, was “Internal Datatype”. Although the name itself sugggest that it is a internal coding of oracle to store different data type, but I was curious of what other more datatype are there…. [...]
Leave a Comment