New Datatypes, New Possibilities

Listen Print

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

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.

Example 5. Package Specification for "Any" Function

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

Example 6. Package Body for "Any" Function

 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.

Table 2. Breakdown of Example 6

Lines	Description
11-15	Construct the basic query to retrieve all of the `AnyData` columns from the table.
26	Call the `AnyData.gettype` method to query this "opaque" datatype to find out what type it actually is. This is the fun part!
28	Compare this retrieved type against the `DBMS_TYPES` constant. Is it a number? If so, continue evaluating.
30	We know it's a number, but what is the value? Call the `AnyData.getnumber` method to get it.
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 `NULL`), then I extend the nested table and assign the value.

Example 7. Dynamic PL/SQL Block for Filter Evaluation

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

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

Lots of New Possibilities

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 TIMESTAMPs and INTERVALs. Want to impress friends, family, and managers by performing magic tricks with AnyData and AnyDataSet? Put aside lots of time to play around with those object types, because the documentation is not only minimal, but misleading.

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.