New Datatypes, New Possibilitiesby Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition
Editor's note: In Part 3 in this series on new features in Oracle 9i, Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition, takes a look at some of Oracle's new datatypes -- including XMLTypes -- and shows how you can make them work for you.
Also In This Series
Oracle 9i has introduced a whole bunch of new datatypes that greatly expand the possibilities for powerful, intuitive programming in the PL/SQL language. This article introduces many of these datatypes and offers some examples of usage. Table 1 offers a quick review of many of the Oracle 9i datatypes.
Table 1. New Oracle 9i Datatypes
||A new variation of
||A variation of
||A variation of
||Used to represent the difference between two datetime values, where the only significant portions are the year and month.|
||Used to represent the precise difference between two datetime values, down to the second or fraction of a second.|
||Used to store, manipulate, and query XML documents natively in the database.|
||The root of an object type hierarchy that can be used to store URIs (Universal Resource Identifiers) to external Web pages and files, as well as to refer to data within the database.|
||A generic or "Any" datatype that contains the description of any SQL type (scalar, nested table, object type, and so forth).|
||An instance of a given type. It contains data, plus a description of the type, and it persists in the database.|
||A description of a given type, plus a set of instances of that type; it persists in the database.|
Working with Timestamps
We all love the
DATE datatype, but let's face it: it wasn't everything we always wanted in
a timestamp datatype. Namely, the
Supported a timestamp only down to the nearest second. Perhaps when the Oracle database was first designed and released (in the early 1980s), that was good enough. But now we're on "Internet time," and fractions of seconds are the cat's meow.
Offered almost no support for time zone manipulation. The
NEW_TIMEfunction acted as though it would allow you to work with different time zones, but it was just a stopgap.
Oracle has corrected these deficiencies in Oracle 9i by introducing the
TIMESTAMP, you can specify a precision (up to nine digits) for fractions
of seconds. And you can take advantage of smart, built-in time zone recognition,
manipulation, and arithmetic. Let's look at some examples.
Here's a declaration of a
TIMESTAMP with a precision down to a thousandth of a
DECLARE test_endpoint TIMESTAMP(3); BEGIN test_endpoint := '1999-06-22 ' || '07:48:53.275';
I assign a value to that checkout timestamp through an implicit conversion. This is very
similar to the type of code one might write to assign a value to a
DATE variable, except
that I can now also provide a fractional value for the second (275/1000).
Of course, for the most part, we won't be assigning fractional components of seconds. Instead, such information will be taken from system timestamp information or provided from externally-generated data (from, say, a manufacturing assembly line).
Oracle provides a host of new built-in functions to obtain and convert timestamps, as I demonstrate in the following script:
DECLARE -- Grab the current timestamp, -- restricting precision to -- four digits right_now TIMESTAMP (4) := CURRENT_TIMESTAMP; -- Grab the current timestamp, -- but preserve time zone info. over_there TIMESTAMP (0) WITH TIME ZONE:= CURRENT_TIMESTAMP; -- Use LOCAL TIME ZONE with -- the timestamp right_here TIMESTAMP (2) WITH LOCAL TIME ZONE:= CURRENT_TIMESTAMP; BEGIN -- Display the values DBMS_OUTPUT.put_line ( SYSTIMESTAMP); DBMS_OUTPUT.put_line ( CURRENT_TIMESTAMP); DBMS_OUTPUT.put_line ( right_now); DBMS_OUTPUT.put_line ( over_there); DBMS_OUTPUT.put_line ( right_here); END;
And here's the output displayed:
SYSTIMESTAMP 05-FEB-02 12.57.44.000000000 PM -08:00 CURRENT_TIMESTAMP 05-FEB-02 12.57.44.000000107 PM -08:00 TIMESTAMP (4) 05-FEB-02 12.57.44.0000 PM TIMESTAMP (0) WITH TIME ZONE 05-FEB-02 12.57.44 PM -08:00 TIMESTAMP (2) WITH TIME ZONE 05-FEB-02 12.59.59.00 PM
Working with time zones can get very complicated, and Oracle documentation is still a bit on the skimpy side. You'll need to make sure that your database has set a time zone, which isn't done by default. Here's the kind of statement you'd execute (and then restart the database):
ALTER DATABASE SET time_zone = 'US/Central'
You can also set a time zone for a session as well, such as:
ALTER SESSION SET time_zone = 'US/Central'
You can also set the default time zone format used for conversion and display as follows:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'DD-Mon-YYYY HH24:MI:SSXFF TZR TZD';
You can examine the full (and greatly expanded) set of Oracle-recognized time zones with the following query:
SELECT DISTINCT tzname FROM v$timezone_names;
Example 1 offers a procedure that you can use to set the time zone in your session and then display various elements of the current time zone information.
Example 1. Set and Show Time Zone Information
CREATE OR REPLACE PROCEDURE tz_set_and_show (tz_in IN VARCHAR2 := null) IS BEGIN IF tz_in IS NOT NULL THEN EXECUTE IMMEDIATE 'alter session set time_zone = ''' || tz_in || ''''; END IF; DBMS_OUTPUT.put_line ( 'SESSIONTIMEZONE = ' || SESSIONTIMEZONE); DBMS_OUTPUT.put_line ( 'CURRENT_TIMESTAMP = ' || CURRENT_TIMESTAMP); DBMS_OUTPUT.put_line ( 'LOCALTIMESTAMP = ' || LOCALTIMESTAMP); DBMS_OUTPUT.put_line ( 'SYS_EXTRACT_UTC (LOCALTIMESTAMP) = ' || sqlexpr ('SYS_EXTRACT_UTC (LOCALTIMESTAMP)') ); END; /