Working with VARRAYs in Oracle - Part II

March 12, 2004


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');







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers