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

Working with Timestamps

Related Reading Oracle PL/SQL Programming By Steven Feuerstein

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

Working with Intervals

Use variables of type INTERVAL to store and manipulate deltas between two different dates or timestamps. In the past, you would've treated this same information with a number, but then you'd have to interpret that number in ways that greatly increased the possibility of error and greatly decreased the readability (and therefore, maintainability) of your code.

Now, with INTERVALs, you can write very understandable code that allows for manipulation of timestamp deltas in natural, intuitive ways.

Recognizing that there are roughly two "scales" of intervals with which humans concern themselves, you can declare and use INTERVALs of type YEAR TO DAY and DAY TO SECOND.

With YEAR TO DAY INTERVALs, you can store and manipulate intervals of years and months. The syntax for this interval is:

INTERVAL YEAR[(precision)] TO MONTH

where the precision can range from 0 to 4, with a default of 2. You don't get to specify a precision for MONTH.

Use DAY TO SECOND INTERVALs to store and manipulate intervals of days, hours, minutes, and seconds. With this interval type, you can set two levels of precision:

INTERVAL DAY[(leading_precision)] 
TO 
SECOND[(fractional_seconds_precision)]

The default values are 2 and 6, respectively. You must use integer literals in these declarations; you may not use a variable or named constant.

Suppose I've created a person object type. I add a member procedure to calculate the age of a person. For such a calculation, I really don't need to get too detailed; number of years and months is fine. So I define this function as follows:

MEMBER FUNCTION age RETURN INTERVAL YEAR TO MONTH
IS
   retval INTERVAL YEAR TO MONTH;

BEGIN
   RETURN (SYSDATE - SELF.dob) 
      YEAR TO MONTH;

END;

Notice that I perform date arithmetic between today's date and the date of birth (dob) of the currently instantiated object (SELF). I then express that delta as an INTERVAL.

Oracle is very flexible about allowing you to specify intervals, as shown in the following set of assignments. In the following block, I assign my duration of 14 years, seven months working with Oracle technology (I started with Oracle Corporation in August 1987 and lasted five years!) to an INTERVAL variable:

DECLARE
   oracle_career 
      INTERVAL YEAR(2) TO MONTH;
BEGIN
   -- Example of INTERVAL literal
   oracle_career := 
      INTERVAL '14-7' YEAR TO MONTH;
    
   -- Implicit conversion from string
   oracle_career := '14-7'; 
   
   -- Assign year and month components
   -- individually
   oracle_career := INTERVAL '14' YEAR; 
   oracle_career := INTERVAL '7' MONTH;
END;

Working with XMLTypes

INTERVALs and TIMESTAMPs are interesting. XMLTypes are part of a dramatic transformation within the Oracle database and technology. As you're probably aware, Oracle moved rapidly with the Oracle 8i release to allow PL/SQL and Java database programmers to parse, manipulate, and store XML documents.

Oracle 9i takes a giant leap toward what Oracle is calling its "XDB," the XML DataBase, by offering a native XML datatype, SYS.XMLTYPE. With this datatype, Oracle now allows us to perform SQL operations on XML content and XML operations on SQL content. You can also apply standard XML functionality, such as XPath, directly against data without the need to convert to CLOBs or other datatypes.

I can now, for example, create a database table with a column of type SYS.XMLTYPE, such as:

CREATE TABLE env_analysis (
   company VARCHAR2(100),
   site_visit_date DATE,
   report SYS.XMLTYPE);

The XMLType datatype is actually an object type, which means that it comes with a set of methods you can use to manipulate object instances of this type. So if I want to insert a row into the env_analysis table, I can write code like that shown in Example 2.

Specifically, I call the CreateXML static method of the XMLType datatype to convert a string into an XML type. Other methods in the XMLType datatype include:

• existsNode: Returns 1 if the given XPath expression returns any result nodes.

• extract: Applies an XPath expression over the XML data to return an XMLType instance containing the resultant fragment.

• isFragment: Returns 1 if the XMLtype contains a fragment, rather than a complete document.

• getCLOBval, getStringval, and getNumberval: Return an XML document or fragment as CLOB, string, or number, respectively.

Example 2. Inserting into an XMLType Column

INSERT INTO env_analysis VALUES (
   'ACME SILVERPLATING',
   TO_DATE (
      '15-02-2001', 'DD-MM-YYYY'),
   SYS.XMLTYPE.CREATEXML(
       '<?xml version="1.0"?>
        <report>
           <site>1105 5th Street</site>
           <substance>PCP</substance>
           <level>1054</level>
        </report>'));

Using these methods, I can perform more complex operations with XMLTypes. In the statement shown in Example 3, I utilize XPath syntax to create a function based on the first 30 characters of the names of substances analyzed in the environmental report.

Example 3. Creating a Function-Based Index Utilizing XMLType Methods

CREATE UNIQUE INDEX i_purchase_order_reference
ON env_analysis ea ( 
   SUBSTR(
      SYS.XMLTYPE.GETSTRINGVAL (         
         SYS.XMLTYPE.EXTRACT(   
            ea.report, '/Report/Substance/text()')),1,30))

Oracle 9i Release 2 (currently entering beta test) will add significant new features for XML document management, including support for access control lists, foldering (allowing for the creation of hierarchies of directories and utilities to search and manage them), and support for FTP access and WEBDav (Web-based Distributed Authoring and Versioning, HTTP extensions for collaborative editing and management of files on remote Web servers).

The bottom line for PL/SQL programmers: if you have to choose (or establish priorities) between learning Java and learning XML, my recommendation is that you come up to speed as quickly as possible on XML. Going forward, it will be an increasingly prominent feature in Oracle-based applications.

Working with "Any" Types

Let's finish up this overview of some of Oracle 9i's new datatypes with a look at the new "Any" types. Does that sound terribly generic? It should sound that way, because it is. With Oracle 9i, the PL/SQL language is finally given some powerful "reflection" capabilities: the ability to interrogate runtime data structures for both data values and data structures. Why would you ever want or need something like that? When you're building highly generic programs that are intended to be run and applied to multiple applications and systems, making few or no assumptions in advance.

Many -- really, most -- developers will never need this capability, but it's still good to be aware of what's possible. In this article, I'll give you a glimpse of the "Any" types. I'll explore this functionality in much more depth in a future article.

First of all, Oracle offers a new built-in package, DBMS_TYPES, that offers named constants for all the different SQL types supported by the database (and they're accessible via the "Any" types). Example 4 shows the current DBMS_TYPES package specification; this package is defined in the Oracle-provided Rdbms/Admin/dbmsany.sql file.

Example 4. The DBMS_TYPES Package Specification

CREATE OR REPLACE PACKAGE DBMS_TYPES 
AS
  TYPECODE_DATE            PLS_INTEGER :=  12;
  TYPECODE_NUMBER          PLS_INTEGER :=   2;
  TYPECODE_RAW             PLS_INTEGER :=  95;
  TYPECODE_CHAR            PLS_INTEGER :=  96;
  TYPECODE_VARCHAR2        PLS_INTEGER :=   9;
  TYPECODE_VARCHAR         PLS_INTEGER :=   1;
  TYPECODE_MLSLABEL        PLS_INTEGER := 105;
  TYPECODE_BLOB            PLS_INTEGER := 113;
  TYPECODE_BFILE           PLS_INTEGER := 114;
  TYPECODE_CLOB            PLS_INTEGER := 112;
  TYPECODE_CFILE           PLS_INTEGER := 115;
  TYPECODE_TIMESTAMP       PLS_INTEGER := 187;
  TYPECODE_TIMESTAMP_TZ    PLS_INTEGER := 188;
  TYPECODE_TIMESTAMP_LTZ   PLS_INTEGER := 232;
  TYPECODE_INTERVAL_YM     PLS_INTEGER := 189;
  TYPECODE_INTERVAL_DS     PLS_INTEGER := 190;

  TYPECODE_REF             PLS_INTEGER := 110;
  TYPECODE_OBJECT          PLS_INTEGER := 108;
  TYPECODE_VARRAY          PLS_INTEGER := 247;  /* COLLECTION TYPE */
  TYPECODE_TABLE           PLS_INTEGER := 248;  /* COLLECTION TYPE */
  TYPECODE_NAMEDCOLLECTION PLS_INTEGER := 122;
  TYPECODE_OPAQUE          PLS_INTEGER := 58;       /* OPAQUE TYPE */

  SUCCESS                  PLS_INTEGER := 0;
  NO_DATA                  PLS_INTEGER := 100;
  
  /* Exceptions */
  invalid_parameters EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_parameters, -22369);

  incorrect_usage EXCEPTION;
  PRAGMA EXCEPTION_INIT(incorrect_usage, -22370);
       
  type_mismatch EXCEPTION;
  PRAGMA EXCEPTION_INIT(type_mismatch, -22626);
END dbms_types;
/

You'll need to make reference to one or more of these constants as you interrogate data structures.

So, let's see what kind of magic you can work with these types. Suppose I want to create a data structure that contains heterogeneous or different kinds of data. One example of such a requirement might be if I'm using Advanced Queuing. Rather than having to constrain each queue message to contain a certain object type, I want it to contain different types.

I can now create a "generic table" that will hold virtually any kind of data (number, string, object type, and so on). Here we go:

First, I'll create an object type of pets:

CREATE TYPE pet_t IS OBJECT (
   tag_no  INTEGER,
   name    VARCHAR2 (60),
   breed   VARCHAR2(100);
/

Now my generic table:

CREATE TABLE wild_side ( 
   id number, 
   data SYS.ANYDATA);

Each row in this table contains an identification number and, well, just about anything, as you can easily see in the following block doing inserts on this table:

DECLARE
   my_bird pet_t := 
      pet_t (5555, 
        'Mercury', 
       'African Grey Parrot'); 
BEGIN
   INSERT INTO wild_side
   VALUES (1, 
     SYS.ANYDATA.CONVERTNUMBER (5));

   INSERT INTO wild_side
   VALUES (2, 
      SYS.ANYDATA.CONVERTOBJECT 
         (my_bird));
      
END;

I've added two rows, one containing a number and the other a pet object instance. I accomplished this by calling two of the convert methods associated with the AnyData object type (also defined in the dbmsany.sql file).

That shows how to put diverse kinds of data into an AnyData column. That's fairly interesting, but even more impressive is the ability to query rows from this data and then figure out what kind of data is sitting in the data column.

You'll find in Example 5 the package-based specification of a function that retrieves from the generic table only those rows that: 1) contain numbers, and 2) contain numbers that satisfy the Boolean expression (in essence, a WHERE clause).

Example 6 shows the body of this function, with line numbers. First, we'll look at how this program can be used. Then we'll step through the most interesting parts of the code. Here's an example of using the function:

SQL> l
  1  DECLARE
  2     mynums   anynums_pkg.numbers_t;
  3  BEGIN
  4     mynums := anynums_pkg.getvals (
  5        'wild_side', 'data');
  6     
  7     mynums := anynums_pkg.getvals (
  8        'wild_side', 'data', '> 100');
  9  END;

On line 2, I declare a local nested table to hold the results of my retrieval. On lines 4- 5, I call the getVals function, passing the table name wild_side and the name of the AnyData column, data. This should return the values in every row in which the AnyData column actually contains a number, skipping everything else. On lines 7-8, I again request numeric values from wild_side.data, but this time I specify that I only want data whose values are greater than 100.

CREATE OR REPLACE PACKAGE anynums_pkg
IS
   TYPE numbers_t IS TABLE OF NUMBER;

   FUNCTION getvals (
      tab_in             IN   VARCHAR2,
      anydata_col_in     IN   VARCHAR2,
      num_satisfies_in   IN   VARCHAR2 := NULL
   )
      RETURN numbers_t;
END anynums_pkg;
/

 1  CREATE OR REPLACE PACKAGE BODY anynums_pkg
 2  IS
 3     FUNCTION getvals (
 4        tab_in             IN   VARCHAR2,
 5        anydata_col_in     IN   VARCHAR2,
 6        num_satisfies_in   IN   VARCHAR2 := NULL
 7     )
 8        RETURN numbers_t
 9     IS
10        retval       numbers_t        := numbers_t ();
11        l_query      VARCHAR2 (1000)
12                            :=    'SELECT '
13                               || anydata_col_in
14                               || ' FROM '
15                               || tab_in;
16        l_type       SYS.ANYTYPE;
17        l_typecode   PLS_INTEGER;
18        l_value      NUMBER;
19        l_dummy      PLS_INTEGER;
20        l_filter     VARCHAR2 (32767);
21        l_include    BOOLEAN;
22     BEGIN
23        FOR rec IN  (SELECT DATA
24                       FROM wild_side)
25        LOOP
26           l_typecode := rec.DATA.gettype (l_type /* OUT */);
27
28           IF l_typecode = dbms_types.typecode_number
29           THEN
30              l_dummy := rec.DATA.getnumber (l_value /* OUT */);
31              l_include := num_satisfies_in IS NULL;
32
33              IF NOT l_include
34              THEN
35                 l_filter :=
36       'DECLARE l_bool BOOLEAN; BEGIN l_bool := :invalue '
37       || num_satisfies_in
38       || '; IF l_bool THEN :intval := 1; ELSE :intval := 0; END IF; END;';
39                 EXECUTE IMMEDIATE l_filter USING  IN l_value,  OUT l_dummy;
40                 l_include := l_dummy = 1;
41              END IF;
42
43              IF l_include
44              THEN
45                 retval.EXTEND;
46                 retval (retval.LAST) := l_value;
47              END IF;
48           END IF;
49        END LOOP;
50
51        RETURN retval;
52     EXCEPTION
53        WHEN OTHERS
54        THEN
55           pl (SQLERRM);
56           pl (l_filter);
57           RETURN NULL;
58     END;
59* END anynums_pkg;

DECLARE
   l_bool   BOOLEAN;
BEGIN
   l_bool := :invalue > 100;

   IF l_bool
   THEN
      :intval := 1;
   ELSE
      :intval := 0;
   END IF;
END;

This article was originally published in the January 2002 issue of Oracle Professional. The material in Feuerstein's articles (and those he cowrote with Bryn Llewellyn) is based on Oracle Corporation white papers originally prepared by Llewellyn for Oracle OpenWorld 2001 in San Francisco and OracleWorld Copenhagen in June 2002, and Oracle PL/SQL Programming, 3rd Edition.

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language, having written ten books on the subject. Steven is a Senior Technology Advisor with Quest Software and has been developing software since 1980.

Related Reading Oracle PL/SQL Programming By Steven Feuerstein

O'Reilly & Associates recently released (September 2002) Oracle PL/SQL Programming, 3rd Edition.

• Sample Chapter 10, Dates and Timestamps, is available free online.

• You can also look at the Table of Contents, the Index, and the full description of the book.

• For more information, or to order the book, click here.

Return to the O'Reilly Network.

Copyright © 2009 O'Reilly Media, Inc.