Database Reference
In-Depth Information
Another approach is to temporarily convert the
LONG
or
LONG RAW
into a
CLOB
or
BLOB
using the
TO_LOB
built-in function and a global temporary table. Your PL/SQL procedure could be as follows:
Insert into global_temp_table ( blob_column )
select to_lob(long_raw_column) from t where...
This would work well in an application that occasionally needs to work with a single
LONG RAW
value. You would
not want to continuously do that, however, due to the amount of work involved. If you find yourself needing to resort
to this technique frequently, you should definitely convert the
LONG RAW
to a
BLOB
once and be done with it.
Dates, Timestamps, and Interval Types
The native Oracle datatypes of
DATE
,
TIMESTAMP
, and
INTERVAL
are closely related. The
DATE
and
TIMESTAMP
types
store fixed date/times with varying degrees of precision. The
INTERVAL
type is used to store an amount of time, such
as “8 hours” or “30 days,” easily. The result of subtracting two timestamps might be an interval, the result of adding an
interval of 8 hours to a
TIMESTAMP
results in a new
TIMESTAMP
that is 8 hours later.
The
DATE
datatype has been part of Oracle for many releases—as far back as my experience with Oracle
goes, which means at least back to version 5 and probably before. The
TIMESTAMP
and
INTERVAL
types are relative
newcomers to the scene by comparison, as they were introduced with Oracle9
i
Release 1. For this simple reason,
you will find the
DATE
datatype to be the most prevalent type for storing date/time information. But many new
applications are using the
TIMESTAMP
type for two reasons: it has support for fractions of seconds (the
DATE
type does
not) and it has support for time zones (something the
DATE
type also does not have).
We'll take a look at each type after discussing
DATE
/
TIMESTAMP
formats and their uses.
Formats
I am not going to attempt to cover all of the
DATE
,
TIMESTAMP
, and
INTERVAL
formats here. This is well covered in the
Oracle Database SQL Language Reference
manual, which is freely available to all. A wealth of formats is available to
you, and a good understanding of what they are is vital. I strongly recommended that you investigate them.
I'd like to discuss what the formats do here, as there are a great many misconceptions surrounding this topic.
The formats are used for two things:
•
To format the data on the way out of the database in a style that pleases you
DATE
,
TIMESTAMP
, or
INTERVAL
And that is all. The common misconception I've observed over the years is that the format used somehow affects
what is stored on disk and how the data is actually saved.
The format has no effect at all on how the data is stored.
The format is only used to convert the single binary format used to store a
DATE
into a string or to convert a string into
the single binary format that is used to store a
DATE
.
The same is true for
TIMESTAMP
s and
INTERVAL
s.
My advice on formats is simply this: use them. Use them when you send a string to the database that represents
a
DATE
,
TIMESTAMP
, or
INTERVAL
. Do
not
rely on default date formats—defaults can and probably will at some point in
the future be changed by someone.
•
To tell the database how to convert an input string into a
■
refer back to Chapter 1 for a really good security reason to never use
TO_CHAR
/
TO_DATE
without an explicit
format. In that chapter, I described a sQL injection attack that was available to an end user simply because the developer
forgot to use an explicit format. additionally, performing date operations without using an explicit date format can and will
lead to incorrect answers. In order to appreciate this, just tell me what date this string represents: '01-02-03'. Whatever
you say it represents, I'll tell you that you are wrong. Never rely on defaults!
Note