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>
Making Selecting the data Easier


If you look at Part I of this series, you surly would have
noticed the difficulty of selecting data from the VARRAY. If you were to select
straight from the table, the VARRAY information displays as a wrapped set of
information that is very unreadable. If you issued the SELECT statement that
took advantage of the TABLE function, you surly were able to get the data out
in a more readable form but the ability to code this function in normal SQL
statements could cause problems for more junior developers. What we would
really like to do is just issue a normal SQL SELECT statement with which everyone
is familiar. In order to do this we must use a table function to produce a
result set for a simple select. 


Another Abstract Object


Here we create our own object type called GAS_LOG2_TY. Then
we create a table of GAS_LOG2_TY called GAS_LOG_TBL_TY. The table
GAS_LOG_TBL_TY is what we will use to return the rows from the table function
within a simple select statement.




CREATE TYPE GAS_LOG2_TY AS OBJECT
       (VIN                NUMBER,
        GALLONS            NUMBER,
        FILLUP_DATE        DATE,
        GAS_STATION        VARCHAR2(255));
/
CREATE TYPE 
  GAS_LOG_TBL_TY AS TABLE OF GAS_LOG2_TY;
/


The Function


I have created a set of PL/SQL statements in Figure 2. There
is nothing particularly interesting here but please just notice that the SQL
statement we used in Part I of this series is now within the function. 




Figure 2.

Function to pipe results through abstract type




CREATE OR REPLACE FUNCTION GAS_LOG_FN
         RETURN GAS_LOG_TBL_TY PIPELINED IS
         PRAGMA AUTONOMOUS_TRANSACTION; 
TYPE         ref0 IS REF CURSOR;
cur0         ref0;
out_rec      gas_log2_ty 
          := gas_log2_ty(NULL,NULL,NULL,NULL);
BEGIN
OPEN cur0 FOR 
'select a.vin,var.gallons,var.fillup_date,var.gas_station '||
'  from gas_log a, table(gas_log) var ';
LOOP
  FETCH cur0 INTO out_rec.vin, out_rec.gallons, out_rec.fillup_date, out_rec.gas_station;
  EXIT WHEN cur0%NOTFOUND;
  PIPE ROW(out_rec);
END LOOP;
CLOSE cur0;
RETURN;
END GAS_LOG_FN;
/





To simplify the SQL to select through this function we need
to put a view on top of it. Figure 3 shows the view and notice the TABLE
function call to the GAS_LOG_FN function.



Figure 3

View
for Table Function



CREATE OR REPLACE VIEW GAS_LOG_VW AS 
  SELECT a.vin, a.gallons, a.fillup_date, a.gas_station
  FROM TABLE(GAS_LOG_FN) a
/





Now we can simply select from the GAS_LOG_VW as shown here.




SQL> select * from gas_log_vw where vin = 101010101010101;

              VIN    GALLONS FILLUP_DA GAS_STATION
----------------- ---------- --------- ------------
  101010101010101         32 19-FEB-04 Shell
  101010101010101         22 20-FEB-04 Sinclare
  101010101010101         55 20-FEB-04 Texaco



Simplified INSERT as Promised


We now have most of the pieces to perform a simple SQL
INSERT statement that looks like what most of us are used to. The last issue is
that a table function cannot be the target of a DML operation such as INSERT, UPDATE
or DELETE. To get around this we will create an INSTEAD OF INSERT trigger for
the previous GAS_LOG_VW view we created. Figure 4 shows the DDL for this
trigger.



Figure 4.

INSTEAD
OF TRIGGER for Inserts



CREATE OR REPLACE TRIGGER GAS_LOG_TRIGGER
INSTEAD OF INSERT ON GAS_LOG_VW
FOR EACH ROW
BEGIN
gas_log_insert(:new.vin,:new.gallons,:new.fillup_date,:new.gas_station);
END;
/




Now we can issue the following INSERT statement that most of
us are familiar with to add values to the VARRAY within our table.




SQL> INSERT INTO gas_log_vw 
     VALUES (101010101010101,55,sysdate,'Texaco');


This article gives you all the tools and methods of
abstracting the complexity of working with VARRAYs within a table. You can now
perform all types of DML operations in your normal fashion. The smiles you will
have on developers' and end users' faces will far outweigh the extra lines of
code you have just produced. Remember that it is your task in life to take
advantage of the new features that Oracle has to offer but at the same time
make them simple enough that anyone can use them or in this case don't even
know they are. Next time we will look at the performance implications of using
VARRAYs. Stay tuned. 
exec gas_log_insert(101010101010101,22,sysdate,'Sinclare');