CREATE OR REPLACEPROCEDURE test IS
NATIONAL CHAR VARYING(32767);
nationalcharactervaryingvar NATIONAL CHARACTER VARYING(32767);
VARCHAR2(32767); BEGIN NULL; END test;
SQL> desc test
INT_D2S_COL INTERVAL DAY(2) TO SECOND(6)
INT_Y2M_COL INTERVAL YEAR(2) TO MONTH
TSWTZ_COL TIMESTAMP(6) WITH TIME ZONE
TSWLTZ_COL TIMESTAMP(6) WITH
LOCAL TIME ZONE
Since Oracle 7 the DATE datatype is stored in a proprietary format. DATE values
are always stored in 7 bytes, excluding the length byte, within a
datafile. These bytes store the century, year, month, day, hour, minute, and second
details respectively. The following is the definition of Oracle's internal DATE storage structure:
1 Century -- stored in excess-100 notation
2 Year -- " "
3 Month -- stored in 0 base notation
4 Day -- "
5 Hour -- stored in excess-1 notation
6 Minute -- "
7 Second -- "
Note that the century and year components are stored in 'excess 100 format',
which means that 100 must be deducted from the byte's value. If a negative number results, then we've got a BC date at which point we take the absolute
number. Also, to avoid ever having a zero byte, 1 is added to the hour, minute and second bytes. Therefore, 1 must be detected to get the correct value.
For example, take the following date again:
we would expect this date to be stored internally as follows:
119, 180, 12, 17, 01, 01, 01
Let's confirm that hypothesis by dumping the data block in question from an
Oracle 8 database and examining its contents. Let's use the "SCOTT.EMP" table for our example:
1) First let's select a row with DATE information to examine:
SQL> desc emp
Result: We see that the HIREDATE column is the fifth column.
SQL> SELECT to_char(hiredate, 'DD-MON-YYYY HH24:MI:SS') FROM emp WHERE rownum = 1;
Result: 17-DEC-1980 00:00:00
2) Next we need to dump the datablock in question:
What happened? Is the information above incorrect or does the DUMP() function
not handle DATE values? No, you have to look at the "Typ=" values to understand why we are seeing these
results. The datatype returned is 13 and not 12, the external DATE datatype. This occurs because we rely on the
TO_DATE function! External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler
represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL
interfaces for Oracle and is used for date calculations mainly within PL/SQL operations. Note that the same result
can be seen when DUMPing the value SYSDATE.
Using deductive logic, we can derive the following storage format for type 13 data:
Byte 1 - Base 256 year modifier
2 - Base 256 year
3 - Month
4 - Day
5 - Hours
6 - Minutes
7 - Seconds
8 - Unused
For AD dates, the year and base 256 modifier are stored in base 0 notation and
we must add the modifier to the year to obtain the true year. For BC dates, the year and base 256 modifier are stored in excess-255 notation. We must subtract
the modifier from the year to obtain the true year.
For our year 1980, we could read this to be, Byte 1 + Byte 2 * 256. In other words, 188 + 7 * 256 = 1980.
Let's try another DUMP but using a date extracted from the original table:
Now we have a datatype 12 with a length of 7 and the results are as expected.
In terms of limits, Oracle is capable of handling dates from:
01-JAN-4712 BC 00:00:00
Julian Day: 1
31-DEC-9999 AD 23:59:59 AD
Julian Day: 5373484
The Julian Day number is a count of days elapsed since Greenwich mean noon
on 1 January 4712 B.C. in the Julian proleptic calendar. The Julian Date is the Julian Day number followed by
the fraction of the day elapsed since the preceding noon.
All calculations made on DATE values are based on fractional days. In other words, the values of SYSDATE+1 is
tomorrow's DATE at this time. This is reminicent of the Julian Day number behavior described above. By using
the widely accepted convention of the Julian calendar that 1 day is the basic unit of time measurement, DATE
calculation logic is greatly simplified.
Note: Rowid consists of
four parts ... characters 1-6 = data object id, characters 7-9 = file number, characters
10-15 = block, characters 16-18 - row number
Oracle Supplied Data Types
Data Type Definition
Can contain a type description of any persistent SQL type, named or unnamed, including
object types and collection types. Only new transient types can be constructed using the ANYTYPE interfaces.
See link at page bottom
Contains a description of a given type plus a set of data instances of that type. An
ANYDATASET can be persistently stored in the database or can be used as an interface parameter to communicate
self-descriptive sets of data, all of which belong to a certain type.
Data Type Definition
Supports the storage of audio data
Supports the storage and management of any tpe of media
data, including audio, image, and video
Supports the storage of image data
Supports the a compact representation of color, texture, and shape information of image data data
Characterizes an image by its average color
Encapsulates color values
Characterizes an image by the relative frequencies of the color exhibited by samples of the raw image
For up to 4 image features represented by SI_AVERAGE_COLOR, SI_COLORHISTOGRAM, SI_POSITIONAL_COLOR
and SI_TEXTURE where the feature is associated with a feature weight
Given an image divided into rectangles, represents the feature that characterizes an image by
the n by m most significant colors of the rectangles.
Represents digital images with inherent image characteristics such as
height, width, and format
Characterizes an image by
the size of repeating items (coarseness), brightness
variations (contrast), and predominant direction
Supports the storage of video data
Data Type Definition
A geometric description of a spatial object stored in a single row