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.