New Datatypes, New Possibilities
Pages: 1, 2, 3

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.

Pages: 1, 2, 3

Next Page