New Datatypes, New Possibilities

Listen Print

New Datatypes, New Possibilities

by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition
12/09/2002

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

Inherit the Database: Oracle9i's Support for Object Type Inheritance

Substituting and Converting Object Types in a Hierarchy

Native Compilation, CASE, and Dynamic Bulk Binding

Table Functions and Cursor Expressions

Multi-Level Collections in Oracle 9i

HTTP Communication from Within the Oracle Database

Oracle 9i Release 2 Developments for PL/SQL Collections

Using PL/SQL Records in SQL Statements

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

Name	Description
`TIMESTAMP`	A new variation of `DATE` that's useful for storing very precise datetime (date or timestamp) values, down to small fractions of seconds.
`TIMESTAMP WITH TIMEZONE`	A variation of `TIMESTAMP` that provides smart logic about time zones (previous support in Oracle was very weak).
`TIMESTAMP WITH LOCAL TIMEZONE`	A variation of `TIMESTAMP` that automatically uses/enforces the local time zone.
`INTERVAL YEAR TO MONTH`	Used to represent the difference between two datetime values, where the only significant portions are the year and month.
`INTERVAL DAY TO SECOND`	Used to represent the precise difference between two datetime values, down to the second or fraction of a second.
`SYS.XMLTYPE`	Used to store, manipulate, and query XML documents natively in the database.
`SYS.URITYPE`	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.
`SYS.ANYTYPE`	A generic or "Any" datatype that contains the description of any SQL type (scalar, nested table, object type, and so forth).
`SYS.ANYDATA`	An instance of a given type. It contains data, plus a description of the type, and it persists in the database.
`SYS.ANYDATASET`	A description of a given type, plus a set of instances of that type; it persists in the database.

Working with Timestamps

Related Reading

Oracle PL/SQL Programming
By Steven�Feuerstein

Read Online--Safari Search this book on Safari:

We all love the DATE datatype, but let's face it: it wasn't everything we always wanted in a timestamp datatype. Namely, the DATE datatype:

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_TIME function 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 datatype. With 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 second:

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;
/

Pages: 1, 2, 3