Working with VARRAYs in Oracle – Part II

This article is the second in a three part series that will take
you past the process of creating VARRAYs and will show you how to abstract the
details and complexity from your end users.

In Part I of this series, we saw how to create an abstract
data type and define an array within a table. The mechanisms for inserting data
into this structure and selecting data from this structure was not too
difficult but did require some thought processes and will no doubt look a bit
confusing to the normal person who is familiar with the normal structures of
plain tables within Oracle and simple DML. This article will take you through
the steps on how to abstract the difficulty from using VARRAYs so that
developers or end uses can interact with these structures through familiar
table insert and select statements.

Inserting Data

Inserting one entry into the VARRAY

As seen in Part I of this series, in order to insert into
the GAS_LOG table and provide values for the array type you must supply the gas
log object type (GAS_LOG_TY) to reference and provide values for the GAS_LOG
column. Here is the simple version from Part I on how to insert a value in the
VARRAY. If you look back at the first article, you will see this gets much more
confusing as we want to add multiple values to the VARRAY.

SQL> insert into gas_log values (101010101010101,gas_log_va(gas_log_ty(32,sysdate-1,’Shell’)));
1 row created.

Making Inserts Easier

Since you cannot reference an element of a VARRAY
individually but need to reference the whole VARRAY this makes adding one value
to the VARRAY somewhat difficult. In order to add a value to an element in the
array, you must re-insert all values that are already in the VARRAY at the same
time with the new value. Since you do not want to put developers at the mercy
of remembering this, your next best alternative is to abstract the insert of a
single value to make it look like you are only adding a row in a normal table.
We can do this by designing a procedure to handle this manipulation of the full
VARRAY when we want to insert a new value in the array. Figure 1 gives the code
to handle the abstraction.

Figure 1.
Dynamically Method of Inserting into VARARY

CREATE OR REPLACE PROCEDURE gas_log_insert
( in_vin           IN NUMBER,
  in_gallons       IN NUMBER,
  in_fillup_date   IN DATE,
  in_gas_station   IN VARCHAR2) AS
pr_gas_log_va     gas_log_va := gas_log_va();
BEGIN
EXECUTE IMMEDIATE
‘SELECT gas_log FROM gas_log WHERE vin = :1 FOR UPDATE OF gas_log’
   INTO pr_gas_log_va USING in_vin;
pr_gas_log_va.EXTEND;
pr_gas_log_va(pr_gas_log_va.LAST) := gas_log_ty(in_gallons,in_fillup_date,in_gas_station);
EXECUTE IMMEDIATE
‘UPDATE gas_log SET gas_log = :1 WHERE vin = :2’
  USING pr_gas_log_va, in_vin;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  EXECUTE IMMEDIATE
  ‘INSERT INTO gas_log
   VALUES (:1,gas_log_va(gas_log_ty(:2,:3,:4)))’
    USING in_vin,in_gallons,in_fillup_date,in_gas_station;
END gas_log_insert;
/

Now if we would like to insert a row into our VARRAY all we
need to do is issue the following simplified statement. This is the basic form
of executing a procedure within Oracle and most developers are use to this. While
this is still not a simple INSERT statement, it is much closer. By the end of
this article, we will be doing simple INSERT statements that anyone will be
familiar with. However, this procedure is a critical step in getting us to the
more simplified mechanism and in fact, it can be used by most any developer in
code.

Inserting
a row through the gas_log_insert procedure

SQL>
exec gas_log_insert(101010101010101,22,sysdate,'Sinclare');