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
 
 
Search WWH ::




Custom Search