Developer

Oracle Tip: Build generics with ANYDATA and global temporary tables

Oracle developers often avoid objects because they don't seem to fit cleanly into PL/SQL or SQL domains. To get objects working well, you may have to create a solution that crosses back and forth between SQL and PL/SQL.

This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.

Oracle developers often avoid objects because they don't seem to fit cleanly into PL/SQL or SQL domains. There are many operations on objects that you can't do through pure PL/SQL (e.g., dealing with reference pointers), and there are operations that you can't do in pure SQL (e.g., using OUT columns and BOOLEAN and PLS_INTEGER values). Yet, objects are the only database features that give you a chance to encapsulate code with data and store that data into the database. To get objects working well, you may have to create a solution that crosses back and forth between SQL and PL/SQL.

I encountered this type of problem recently while experimenting with abstract data types or generics. This is the capability of many modern programming languages to create an abstract solution that developers can reuse for a variety of problems. For example, in Java and C#, there are vector classes, which can hold an ordered list of another object and retrieve values by an index number. There is also a list class, which can contain an ordered list of objects that must be navigated from a starting or ending point.

The vector problem might not seem worth solving. There are VARRAY and NESTED TABLES in SQL, and TABLE OF . . . INDEX BY BINARY_INTEGER in PL/SQL, but each implementation has limits, for instance:

  • VARRAY objects get a fixed length at creation time.
  • NESTED TABLES can contain a set of elements, but you cannot easily modify elements after creation.
  • You can't insert PL/SQL TABLES into tables or use them outside of PL/SQL. Most other programming languages have vectors that can grow indefinitely and can be modified and accessed efficiently.

To solve the vector problem, first use a variable of the datatype ANYDATA. (It's similar to a variant or object base class in that it can hold any other datatype, and Oracle already uses it internally for other constructs, such as the database.) Second, store the data in a GLOBAL TEMPORARY TABLE if you only want the data to persist for the session and to be isolated from other users. You also gain the advantage that TEMPORARY tables are almost as efficient as the internal temporary tables that Oracle creates to hold JOIN, SORT, and MERGE data. (They don't generate REDO or ROLLBACK data.) You could also implement the vector with an ordinary table if you want to be able to store vector data permanently. You could store a vector of vectors of vectors in a column in a table.

Another advantage to using TABLES is that you can code the vector with ordinary SELECT/INSERT/UPDATE statements. Since a vector is ordered, add a sequence column and, to allow for multiple vectors in a single session, a GUID column. The vector object only needs to store the GUID and code procedures to manipulate the underlying data table.

Here is the code for a simple vector object that supports as many operations as Java vectors, plus an example using strings (although you can store anything that ANYDATA will store, including other objects and vectors). You could probably simplify the client side a bit more by providing functions and procedures to store specific datatypes into the vector.

drop table vector_data;
drop type vector;

create global temporary table vector_data
(
    guid    raw(16),
    seq     integer,
    data    anydata
);

create type vector as object
(
    m_guid  raw(16),            — unique vector id
    constructor function vector return self as result,
    member function count return integer,
    member function is_empty return boolean,
    member function get_at(idx integer) return anydata,
    member procedure set_at(idx integer,p_data anydata),
    member procedure append(p_data anydata),
    member procedure prepend(p_data anydata),
    member procedure insert_at(idx integer,p_data anydata),
    member procedure remove_at(idx integer),
    member procedure clear
);
/
show errors;

create type body vector as
    —
    — create a new vector
    —
    constructor function vector return self as result is
    begin
        m_guid := sys_guid();
        return;
    end vector;
    —
    — return the number of elements in the vector
    —
    member function count return integer is
        l_count integer;
    begin
        select count(*) into l_count from vector_data
         where m_guid = guid;
        return l_count;
    end count;
    —
    — return true if vector is empty
    —
    member function is_empty return boolean is
    begin
        return self.count = 0;
    end is_empty;
    —
    — return element at position
    —
    member function get_at(idx integer) return anydata is
        l_anydata anydata;
    begin
        select data into l_anydata from vector_data
         where guid = m_guid and seq = idx;
        return l_anydata;
    exception
        when no_data_found then return null;
    end get_at;
    —
    — set an element value at an existing position
    —
    member procedure set_at(idx integer,p_data anydata) is
    begin
        update vector_data
           set data = p_data
         where guid = m_guid and seq = idx;
        if sql%rowcount = 0 then
            raise subscript_beyond_count;
        end if;
    end set_at;
    —
    — insert a value a specified position
    —
    member procedure insert_at(idx integer,p_data anydata) is
    begin
        — push everything above index up
        update vector_data
           set seq = seq + 1
         where guid = m_guid and seq >= idx;
        insert into vector_data values(m_guid,idx,p_data);
    end insert_at;
    —
    — append a value to the end of the vector
    —
    member procedure append(p_data anydata) is
    begin
        insert_at(self.count+1,p_data);
    end append;
    —
    — prepend a value to the start of the vector
    —
    member procedure prepend(p_data anydata) is
    begin
        insert_at(1,p_data);
    end prepend;
    —
    — remove a value at a specified position
    —
    member procedure remove_at(idx integer) is
    begin
        delete from vector_data where guid = m_guid and seq = idx;
        update vector_data set seq = seq - 1
            where guid = m_guid and seq > idx;
    end remove_at;
    —
    — clear out all values
    —
    member procedure clear is
    begin
        delete from vector_data where guid = m_guid;
    end clear;
end;
/
show errors;

set serveroutput on
declare
    l_vector vector := vector();
    l_data  anydata;
    l_varchar2 varchar2(32767);
begin
    l_vector.append(anydata.convertvarchar2('four'));
    l_vector.append(anydata.convertvarchar2('five'));
    l_vector.prepend(anydata.convertvarchar2('three'));
    l_vector.prepend(anydata.convertvarchar2('two'));
    l_vector.insert_at(1,anydata.convertvarchar2('one'));
    l_vector.remove_at(3);
    l_vector.insert_at(3,anydata.convertvarchar2('three'));
    l_vector.append(anydata.convertvarchar2('six'));
    dbms_output.put_line('count = '||l_vector.count);
    for i in 1 .. l_vector.count loop
        l_data := l_vector.get_at(i);
        if l_data.getvarchar2(l_varchar2) = dbms_types.success then
            dbms_output.put_line('value = '||i||','||l_varchar2);
        end if;
    end loop;
    dbms_output.put_line('—————');
    for i in reverse 1 .. l_vector.count loop
        l_data := l_vector.get_at(i);
        if l_data.getvarchar2(l_varchar2) = dbms_types.success then
            dbms_output.put_line('value = '||i||','||l_varchar2);
        end if;
    end loop;
end;
/
show errors;

The list problem has a different set of challenges. In C or C++, you would use pointers; in Java, you would use object references. With Oracle objects, there are REF references, which are equivalent in the sense that they contain a direct reference to an object within a table (using the table id and row id). Because they refer to an object within a table, the objects must be stored in a table in the first place.

You must use a mix of SQL to hold the objects and PL/SQL to aid in navigation to different objects. In this example, I create a LIST_NODE object that contains references to neighboring nodes in the list and creates a GLOBAL TEMPORARY TABLE to hold the nodes and a LIST object to reference the first and last nodes of the list. This time, I don't need a GUID to keep the lists separate; they're already distinct simply by its connections to its neighbors. Since the list works off of references, and the only way to reference or dereference an object is via SQL, most of the code is simple SELECT/INSERT/UPDATE statements of objects in the LIST_DATA table.

Here's an example that builds a list and displays the contents in both directions:

drop type list;
drop table list_data;
drop type list_node;

create type list_node as object
(
    data    anydata,
    next    ref list_node,
    prev    ref list_node
);
/
show errors;

create global temporary table list_data of list_node;

create type list as object
(
    head    ref list_node,
    tail    ref list_node,
    constructor function list return self as result,
    member procedure append(p_data anydata),
    member procedure prepend(p_data anydata),
    — helper functions
    static function get_data(p_ref ref list_node) return anydata,
    static function next(p_ref ref list_node) return ref list_node,
    static function prev(p_ref ref list_node) return ref list_node
);
/
show errors;

create type body list as
    —
    — create a new list
    —
    constructor function list return self as result is
    begin
        head := null;
        tail := null;
        return;
    end list;
    —
    — append data to the end of the list
    —
    member procedure append(p_data anydata) is
        l_ref   ref list_node;
    begin
        insert into list_data l values(list_node(p_data,null,tail))
            returning ref(l) into l_ref;
        if tail is null then
            head := l_ref;
        else
            update list_data p
               set p.next = l_ref
             where ref(p) = tail;
        end if;
        tail := l_ref;
    end append;
    —
    — prepend data to the start of the list
    —
    member procedure prepend(p_data anydata) is
        l_ref   ref list_node;
    begin
        insert into list_data l values(list_node(p_data,head,null))
            returning ref(l) into l_ref;
        if head is null then
            tail := l_ref;
        else
            update list_data p
               set p.prev = l_ref
             where ref(p) = head;
        end if;
        head := l_ref;
    end prepend;
    —
    — get the data value of a node
    —
    static function get_data(p_ref ref list_node) return anydata is
        l_data anydata;
    begin
        select l.data into l_data from list_data l where ref(l) = p_ref;
        return l_data;
    end get_data;
    —
    — navigate to next node
    —
    static function next(p_ref ref list_node) return ref list_node is
        l_ref ref list_node;
    begin
        select l.next into l_ref from list_data l where ref(l) = p_ref;
        return l_ref;
    end next;
    —
    — navigate to next node
    —
    static function prev(p_ref ref list_node) return ref list_node is
        l_ref ref list_node;
    begin
        select l.prev into l_ref from list_data l where ref(l) = p_ref;
        return l_ref;
    end prev;
end;
/
show errors;


set serveroutput on
declare
    l_list list := list();
    l_ref ref list_node;
    l_node list_node;
    l_data  anydata;
    l_varchar2 varchar2(32767);
begin
    l_list.append(anydata.convertvarchar2('one'));
    l_list.append(anydata.convertvarchar2('two'));
    l_list.append(anydata.convertvarchar2('three'));
    l_list.append(anydata.convertvarchar2('four'));
    l_list.append(anydata.convertvarchar2('five'));
    l_list.append(anydata.convertvarchar2('six'));
    l_list.prepend(anydata.convertvarchar2('zero'));
    — forward iteration
    l_ref := l_list.head;
    while l_ref is not null loop
        l_data := list.get_data(l_ref);
        if l_data.getvarchar2(l_varchar2) = dbms_types.success then
            dbms_output.put_line('value = '||l_varchar2);
        end if;
        l_ref := list.next(l_ref);
    end loop;
    dbms_output.put_line('——————');
    — backward iteration
    l_ref := l_list.tail;
    while l_ref is not null loop
        l_data := list.get_data(l_ref);
        if l_data.getvarchar2(l_varchar2) = dbms_types.success then
            dbms_output.put_line('value = '||l_varchar2);
        end if;
        l_ref := list.prev(l_ref);
    end loop;
end;
/
show errors;

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.

Editor's Picks

Free Newsletters, In your Inbox