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
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.
||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.|
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
acted as though it would allow you to work with different time zones, but it was just a
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.
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; /
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
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.
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
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 (
of the currently instantiated object (
SELF). I then express that delta as an
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
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;
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
getNumberval: Return an XML document or fragment as CLOB, string, or number, respectively.
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.
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.
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.
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
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
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
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;
Now let's take a look at Example 6 and the logic that accomplishes all generic retrieval (see Table 2). To do something as flexible as this, I need to take advantage of dynamic SQL and the generic datatype methods. I need to dynamically evaluate each numeric value to see whether it passes the filter, which is passed as a string; this is actually a dynamically-constructed PL/SQL block. If it passes the filter, I deposit that value into the outgoing collection.
|11-15||Construct the basic query to retrieve all of the
|28||Compare this retrieved type against the
|30||We know it's a number, but what is the value? Call the
|35-40||The user passed in a filter, so I need to see whether this numeric value passes the filter. For example, if the user passes in "> 100", then I need to find out whether the value is greater than 100. How do I do that? I'll have to dynamically construct an anonymous PL/SQL block that executes an assignment to a Boolean variable from that expression. Example 7 shows precisely the dynamic block that's constructed and executed for the fragment "> 100".|
|43-47||If the value passes the filter (or the filter was
DECLARE l_bool BOOLEAN; BEGIN l_bool := :invalue > 100; IF l_bool THEN :intval := 1; ELSE :intval := 0; END IF; END;
Sure, I've been having fun coding with PL/SQL for years. With Oracle 9i, though, the possibilities for truly entertaining and exciting programming techniques expand dramatically. Now everyone can have fun with PL/SQL!
Planning to work with XML? Now you can do it with native XML functionality
directly inside of the database. Frustrated with the limitations of
DATE? Port your code to
INTERVALs. Want to impress friends, family, and managers by
performing magic tricks with
AnyDataSet? Put aside lots of time to play around with those object types, because the documentation is not only minimal, but
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.
O'Reilly & Associates recently released (September 2002) Oracle PL/SQL Programming, 3rd Edition.
Sample Chapter 10, Dates and Timestamps, is available free online.
For more information, or to order the book, click here.
Return to the O'Reilly Network.
Copyright © 2009 O'Reilly Media, Inc.